REF Cursors

What are Ref Cursors?

Ref cursor is a Data type. A variable created using this data type is usually called as 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.)

It is of two types:

Strong: With a Return Type
Weak: Without a Return Type

Advantages of Ref Cursors:

* Uses the same memory area for all the active sets created by different queries.
* Can be used to pass result sets between sub programs.
* Ability to change the query based on a certain criterion.

Difference between Static and Ref Cursors:

Static Cursors cannot be passed to sub programs whereas ref cursors can be passed between sub programs.
Static Cursors as the name suggests are Static and decided at the design time itself whereas Ref Cursors are changed during the execution time as per certain criterion.

Important Note: Ensure that any open cursor is closed before attempting to open the next cursor.

Examples of Ref Cursors:

A simple Ref Cursor:

declare type ref_cursor is REF CURSOR;
var_emp ref_cursor;
var emp.ename%type;
begin
open var_emp for select ename from emp;
loop
fetch var_emp into var;
exit when var_emp%notfound;
dbms_output.put_line(var);
end loop;
close var_emp;
end;

%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;
end;

RECORDS with Ref Cursor:

declare

type r_cursor is REF CURSOR;

c_emp r_cursor;

type rec_emp is record ( name varchar2(20), sal number(6) );

er rec_emp;

begin

open c_emp for select ename,sal from emp;

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

end;

Multiple queries using Ref Cursor:

declare

type r_cursor is REF CURSOR;

c_emp r_cursor;

type rec_emp is record

( name varchar2(20),

sal number(6) );

er rec_emp;

begin

open c_emp for select ename,sal from emp where deptno = 10;

dbms_output.put_line('Department: 10');

dbms_output.put_line('--------------');

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

open c_emp for select ename,sal from emp where deptno = 20;

dbms_output.put_line('Department: 20');

dbms_output.put_line('--------------');

loop

fetch c_emp into er;

exit when c_emp%notfound;

dbms_output.put_line(er.name ' - ' er.sal);

end loop;

close c_emp;

end;

Ref Cursor used as Parameters between Sub Programs:

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;

Thus, these are some of the uses of Ref Cursors. Feel free to post any comments on this article.

Source Courtesy: www.devshed.com