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;

Normal Cursor Vs. Ref Cursor:

Normal Cursor	REF Cursor
It is static in definition.	It may be dynamically opened or opened based on logic.
It can’t be returned to a client.	It can be returned to a client.
A normal cursor can be global.	A ref cursor cannot. (you cannot define them OUTSIDE of a procedure / function)
A normal cursor can’t be passed from subroutine to subroutine.	A ref cursor can be passed from subroutine to subroutine.

The performance of normal cursors is much more efficient than ref cursors and hence you should use ref cursor only when you absolutely have to.

Reference:

http://asktom.oracle.com

http://www.devshed.com