Embedded SQL

Embedded SQL

Write a PL/SQL block print the employee details such as employee name, salary and deptno when employee number passes as in value.

declare
   a number;
   b varchar2(10);
   c number;
   d number;
begin
   select ename,sal,deptno into b,c,d from emp where empno=&a;
   dbms_output.put_line(b||' ' ||c||' '||d);
end;

In the above example the programmer need to remember data types of the variables and its sizes and also need to take more than one variable to retrieve more than one column data. To avoid all the above said problems we need to use following attributes.

1. %type

This attribute is used to avoid data types of variables and also its sizes.

E.g

declare
   a emp.empno%type;
   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(b||' '||c||' '||d);
end;

2. %Row Type

This attribute is used to avoid to mention data type and its sizes and also no need to take more than one variable.

E.g

declare
   a emp%rowtype;
begin
   select * into a from emp where empno=&a;
   dbms_output.put_line('empno' || a.empno);
   dbms_output.put_line('ename' || a.ename);
   dbms_output.put_line('sal' || a.sal);
end;