Monday 4 June 2012

Using CURSOR

To get the list of employees who works under sales department using cursor

declare
cursor c is select E.empno,E.ename,E.sal,D.dname
from emp E,dept D where d.dname='SALES';
vno emp.empno%type;
vname emp.ename%type;
vsal emp.sal%type;
vdname dept.dname%type;
begin
IF c%ISOPEN THEN
CLOSE c;
end if;
open c;
loop
FETCH c into vno,vname,vsal,vdname;
EXIT when c%NOTFOUND;
dbms_output.put_line('THE EMPS DETAILS:'||vno||' '||vname||' '||vsal||' '||vdname);
end loop;
end;