Oracle - Using Ref Cursors

Ref Cursors are variables which can point to any cursor dynamically, or can be passed as parameter in some procedure, function or java program, etc.

The PROC below takes a REF CURSOR as a parameter and iterates to print the data contained in the cursor.

create or replace procedure emp_proc_ref_cur (c_emp SYS_REFCURSOR ) is
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
er rec_emp;
begin
open c_emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
/

The PROC below creates a REF CURSOR and OPENS it on EMP table, and passes that REF CURSOR as parameter to the PROC above.

create or replace procedure emp_rec_pro is
type emp_ref is REF CURSOR;
emp_cur emp_ref;
BEGIN
open emp_cur for select ename name, sal from emp where sal>250;
emp_proc_ref_cur (emp_cur);
end;
/