ORCL - REF CURSOR

A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.).

Example: %ROWTYPE with REF CURSOR:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor; er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename || ' - ' || er.sal);
end loop;
close c_emp;
The bold statements basically show the use of REF CURSOR.

Example of using REF as parameter in the sub-programs of a PL/SQL block:

Sub-programs can also be called sub-routines. These are nothing but the divisions of the main program. These divisions are named and are executed when they are called by name from the main program. They will not get executed unless they are called.

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
);
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name || ' - ' || er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;

There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.

The strong ref_cursor and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace package REFCURSOR_PKG as
TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
TYPE STRONG REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;

Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.

/** From Oracle 9 */
create or replace procedure test( p_deptno IN number
, p_cursor OUT SYS_REFCURSOR)
is .........

Cheers.