Cursors with parameters – a complicated example - Oracle

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.