Passing Parameter to CURSOR in Oracle

I like to contribute on OTN, basically to help others and improve myself. In the past few weeks I have answered 4-5 questions related to "Passing Parameters to Cursor". So I thought that it is a nice time to write a post on "Parameterized Cursors in Oracle".

Yes, Oracle does support Parameters with Cursors, like it does in case of function or procedure. Benefit of "Parameterized Cursors" if of-course reusability and maintainability.

Let's write some code which opens a Cursor with Parameter multiple times. In the following example I have used Cursor with Parameter in 4 different ways
1) Parameterized cursor with fetch
2) Parameterized cursor with cursor loop
3) Parameterized cursor with cursor for loop
4) Parameterized cursor with bulk collect

set serveroutput on

SQL> declare
  2     cursor c1 (p_deptno number) is
  3     select dname, nvl(sum(sal),0) total_sal
  4     from emp e, dept d
  5     where d.deptno = e.deptno(+)
  6     and d.deptno = p_deptno
  7     group by dname;
  8
  9     l_dname dept.dname%type;
 10     l_sal number;
 11
 12     type t_tab is table of c1%rowtype index by binary_integer;
 13     l_tab t_tab;
 14  begin
 15
 16     -- using parameterized cursor with fetch
 17     open c1(10);
 18     FETCH c1 INTO l_dname, l_sal;
 19     close c1;
 20     dbms_output.put_line('Department: ' || l_dname || ' Total: ' || l_sal);
 21
 22     -- using parameterized cursor with cursor loop
 23     open c1(20);
 24     loop
 25             fetch c1 into l_dname, l_sal;
 26             exit when c1%notfound;
 27             dbms_output.put_line('Department: ' || l_dname || ' Total: ' || l_sal);
 28     end loop;
 29     close c1;
 30
 31     -- using parameterized cursor with cursor for loop
 32     for c in c1(30)
 33     loop
 34             dbms_output.put_line('Department: ' || c.dname || ' Total: ' || c.total_sal);
 35     end loop;
 36
 37     -- using parameterized cursor with bulk collect
 38     open c1(40);
 39     fetch c1 bulk collect into l_tab;
 40     dbms_output.put_line('Department: ' || l_tab(1).dname || ' Total: ' || l_tab(1).total_sal);
 41     close c1;
 42  end;
 43  /
Department: ACCOUNTING Total: 8750
Department: RESEARCH Total: 10875
Department: SALES Total: 9400
Department: OPERATIONS Total: 0

PL/SQL procedure successfully completed.

As we can see in above example "parameterized cursor" can be used as we use simple cursor and they provide us a great flexibility. I hope that you have enjoyed reading this article. Feedback would be well appreciated.

Related Links:
- Get Results from REFCURSOR with unknown number of columns in PLSQL
- Difference Between Cursor And Ref Cursor
- Ref Cursor: Strongly Typed VS Weakly Typed
- View results of refcursor out parameter
- Bulk Collect with Limit Clause and %NOTFOUND