Database Interaction with PL/SQL: Explict Cursors in Depth - Working with more than one cursor

(Page 2 of 6 )

We can work with more than one cursor at a time by using nested loops. Let us consider the following example:

declare

  cursor c_emp is

        select ename,deptno from emp;

  cursor c_dept is

        select deptno,dname from dept;

  r_emp c_emp%rowtype;

  r_dept      c_dept%rowtype;

begin

  open c_dept;

  loop

        fetch c_dept into r_dept;

        exit when c_dept%notfound;

dbms_output.put_line('-----------------');

        dbms_output.put_line(r_dept.dname);

        dbms_output.put_line('-----------------');

        open c_emp;

        loop

              fetch c_emp into r_emp;

              exit when c_emp%notfound;

              if r_emp.deptno = r_dept.deptno then

                    dbms_output.put_line(r_emp.ename);

              end if;

        end loop;

        close c_emp;

  end loop;

  close c_dept;

end;

 

Before discussing such a lengthy program, let us see the output of above program:

-----------------
ACCOUNTING
-----------------
KING
CLARK
MILLER
-----------------
RESEARCH
-----------------
JONES
FORD

And so on. It displays all employees separated and categorized under department names. The next section explains the above program in a great deal of detail.

Next: How the program works >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL: Explict Cursors in Depth

· Hello guys, this is my in-depth article on working with explicit cursors in PL/SQL....    · Thank you very much for your in depth look at cursors. It helps me see what else can...   >>> Post your comment now!