Recent Posts

Sunday, 17 July 2016

Embeded 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;

Next Tutorial  Exception Handling in PL/SQL

Previous Tutorial  Loop Statements in PL/SQL

No comments:

Post a Comment