Database Interaction with PL/SQL: Explict Cursors in Depth - Cursors with parameters – a complicated example
(Page 5 of 6 )
The following is a new way of writing the program discussed in the previous section “Working with more than one cursor.”
declare
cursor c_dept is
select deptno,dname from dept;
cursor c_emp(v_deptno emp.deptno%type) is
select ename,deptno from emp
where deptno = v_deptno;
begin
for r_dept in c_dept
loop
dbms_output.put_line(r_dept.dname);
dbms_output.put_line('-----------------');
for r_emp in c_emp(r_dept.deptno)
loop
dbms_output.put_line(r_emp.ename);
end loop;
end loop;
end;
Here we have a new declaration of cursor ‘c_emp’ as follows:
cursor c_emp(v_deptno emp.deptno%type) is
select ename,deptno from emp
where deptno = v_deptno;
The above statement states that the SELECT statement needs to be executed based on the value sent through ‘v_deptno’ which is of type ‘emp.deptno%type’. For proceeding to another statement, we have:
for r_emp in c_emp(r_dept.deptno)
This statement opens the cursor ‘c_emp’ based on the value sent (in this case the current value of ‘r_dept.deptno’). If you need to know the traditional way of working with the same concept (without using cursor FOR loop), the following would be the answer:
declare
cursor c_emp(v_deptno dept.deptno%type) is
select ename,deptno from emp
where deptno=v_deptno;
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(r_dept.deptno);
loop
fetch c_emp into r_emp;
exit when c_emp%notfound;
dbms_output.put_line(r_emp.ename);
end loop;
close c_emp;
end loop;
close c_dept;
end;
And I hope you can understand the above program without any explanation.
Next: Can we use JOINS in cursors? >>
More Oracle Articles
More By Jagadish Chatarji
/ 23 
