Friday, April 5, 2013

SQL Embedded in PL/SQL programs


Embedded SQL :
===========
we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.

Double click on the image to view the full image with clarity

Example 1: Write a PL/SQL block input employee number and display the employee details like name, salary and deptno.

SQL>declare
a number;
b varchar2(15);
c number;
d number;
begin
select ename,sal,deptno into b,c,d from emp where empno=&a;
dbms_output.put_line(‘The employee name is’||b);
dbms_output.put_line(‘The employee salary is’||c);
dbms_output.put_line(‘The employee department no is’||d);
end;
/
Enter value for a: 7369

Old 7: select ename,sal,deptno into b,c,d from emp where eid=&a;
New 7: select ename,sal,deptno into b,c,d from emp where eid=7369;

The employee name is SMITH
The employee salary is 800
The employee department no is 20

PL/SQL Procedure Successfully completed.

Note : If you opened SQL * Plus command prompt newly then you need to type set serveroutput on at the command prompt and execute the PL/SQL program to see the output.

SQL> set serveroutput on

Attributes :
=======
As part of the declaration for each PL/SQL variable, you declare its datatype. Usually, this datatype is one of the types shared between PL/SQL and SQL, such as NUMBER or VARCHAR2. For easier code maintenance that interacts with the database, you can also use the special qualifiers %TYPE and %ROWTYPE to declare variables that hold table columns or table rows.
  • %type
  • %rowtype
%type :
=====
The %TYPE attribute provides the datatype of a variable or table column. This is particularly useful when declaring variables that will hold values of a table column. For example, suppose you want to declare variables as the same datatype as the employee_id and last_name columns in employees table. To declare variables named empid and emplname that have the same datatype as the table columns, use dot notation and the %TYPE attribute.

SQL>declare
a emp.empno%type; -- <table_name>.<column_name>%type (Here no need to mention datatype)
b emp.ename%type;
c emp.sal%type;
d emp.deptno%type;
begin
select ename,sal,deptno into b,c,d from emp where empno=&a;
dbms_output.put_line(‘The employee name is’||b);
dbms_output.put_line(‘The employee salary is’||c);
dbms_output.put_line(‘The employee deptno is’||d);
end;


Note : 1)In the above example %type attribute is used to avoid to mention datatypes and its sizes.

          2)
'--' is used to write comments in PL/SQL.

%rowtype :
========
you can use the %ROWTYPE attribute to declare a variable that represents a row in a table. A PL/SQL record is the datatype that stores the same information as a row in a table.Which is used to avoid to mention datatypes,sizes and no need to take more than one variable within the program.

SQL>declare
a emp%rowtype; -- <table_name>%rowtype (represents total row of the table)
begin
select * into a from emp where empno=&empno;
dbms_output.put_line(‘The employee name is’||a.ename);
dbms_output.put_line(‘The employee salary is’||a.sal);
dbms_output.put_line(‘The employee deptno is’||a.deptno);
end;




No comments:

Post a Comment

back to top