Working with REF Cursors – With Examples

A ref cursor is a variable, defined as a cursor type, which points to, or references a cursor result. A cursor variable which is based on REF CURSOR data type 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.).

Examples:

REF CURSOR WITH %TYPE:

DECLARE

TYPE r_cursor IS REF CURSOR;

c_employee r_cursor;

en employee.ename%TYPE;

BEGIN

OPEN c_employee FOR select ename from employee;

LOOP

FETCH c_employee INTO en;

EXIT WHEN c_employee%NOTFOUND;

dbms_output.put_line(en);

END LOOP;

CLOSE c_employee;

END;

 

 REF CURSOR WITH %ROWTYPE:

 

DECLARE

TYPE r_cursor IS REF CURSOR;

c_employee r_cursor;

er employee%ROWTYPE;

BEGIN

OPEN c_employee FOR select * from employee;

LOOP

FETCH c_employee INTO er;

exit when c_employee%NOTFOUND;

dbms_output.put_line(er.ename || ' - ' || er.position);

END LOOP;

CLOSE c_employee;

END;

 

 REF CURSOR WITH RECORD TYPE:

 

DECLARE

TYPE r_cursor IS REF CURSOR;

c_employee r_cursor;

TYPE rec_emp IS RECORD

(

name VARCHAR2(40),

position VARCHAR2(40)

);

er rec_emp;

BEGIN

OPEN c_employee FOR select ename,position from employee;

LOOP

FETCH c_employee INTO er;

exit when c_employee%NOTFOUND;

dbms_output.put_line(er.name || ' - ' || er.position);

END LOOP;

CLOSE c_employee;

END;

 

REF CURSOR WITH MULTIPLE QUERIES:

 

 

DECLARE

TYPE r_cursor IS REF CURSOR;

c_employee r_cursor;

TYPE rec_emp IS RECORD

(

name VARCHAR2(40),

position VARCHAR2(40)

);

er rec_emp;

BEGIN

OPEN c_employee FOR select ename,position from employee where deptname = ‘IT’;

dbms_output.put_line('Department: IT');

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

LOOP

FETCH c_employee INTO er;

exit when c_employee%NOTFOUND;

dbms_output.put_line(er.name || ' - ' || er.position);

END LOOP;

CLOSE c_employee;

OPEN c_employee FOR select ename,position from employee where deptname = ‘Finance’;

dbms_output.put_line('Department: Finance');

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

loop

fetch c_employee into er;

exit when c_employee%NOTFOUND;

dbms_output.put_line(er.name || ' - ' || er.position);

END LOOP;

CLOSE c_employee;

END;