Working with more than one cursor - Oracle

This is part 11 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I introduced the concept of the explicit cursor and looked at several approaches for using the explicit cursors. In this article, we shall delve more deeply into explicit cursors.

TABLE OF CONTENTS:

By: Jagadish Chatarji

Poor Best

Rating:

/ 25

August 02, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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