Ref Cursors

Just wondering from sometimes that why the hell we have one more cursor type called as ref cursor when we already have normal cursors to solve our purpose of storing the SQL result set in to the memory for accessing them later.
before starting the difference between ref cursor and a normal cursor,let me first make it clear what a Ref cursor is ,what are their types and some of their usage.


What is Ref Cursor ?

Ref Cursor is basically a PL/SQL Datatype. This is used to store 
result of a query. It is a "pointer" data types that allows you to quickly reference any cursor result with data values kept in RAM.

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.)

declare

  -- defines a new data type called "rf_cursor," which is of the type REF CURSOR   type rf_cursor is REF CURSOR; 

  -- We declare a cursor variable named "c_emp" based on the type "rf_cursor"    

  c_emp rf_cursor;

  en emp.ename%type; begin

  -- Every cursor variable must be opened with an associated SELECT statement as follows:   open c_emp for select ename from emp;   loop       fetch c_emp into en;       exit when c_emp%notfound;       dbms_output.put_line(en);   end loop;

  --closed the cursor    close c_emp; end;

Types of Ref Cursors

Ref cursor type can be strong(restrictive) or weak (non restrictive).

A strong REF CURSOR type definition specifies a return type, but a weak definition does not.

DECLARE 

  TYPE strong_cursor IS REF CURSOR RETURN emp%ROWTYPE -- strong

  TYPE weak_cursor IS REF CURSOR; --weak 

NOTE : Declaring a cursor variable creats a pointer, not an item.

Cursor variables can not be saved in the database.

Cursors - Vs - Ref Cursors
1. A "normal" plsql cursor is static in defintion. Ref cursors may be dynamically opened or opened based on logic.

Declare
  type rc is ref cursor;
 
  cursor c is select * from dual;

  l_cursor rc;
begin
  if ( to_char(sysdate,'dd') = 30 ) then
    open l_cursor for 'select * from emp';
  elsif ( to_char(sysdate,'dd') = 29 ) then
    open l_cursor for select * from dept;
  else
    open l_cursor for select * from dual;
  end if;
  open c;
end;
/
Given that block of code -- you see perhaps the most "salient" difference -- no  matter how many times you run that block -- cursor C will always be select *  from dual. The ref cursor can be anything.

2. Another difference is a ref cursor can be returned to a client. a plsql "cursor  cursor" cannot be returned to a client. 

Also, a ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

Some examples :

CREATE OR REPLACE PACKAGE CURSPKG AS 
    TYPE T_CURSOR IS REF CURSOR; 
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER, 
                               IO_CURSOR IN OUT T_CURSOR); 
    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR, 
                                DEPTCURSOR OUT T_CURSOR);
END CURSPKG;
/ 
CREATE OR REPLACE PACKAGE BODY CURSPKG AS
    PROCEDURE OPEN_ONE_CURSOR (N_EMPNO IN NUMBER,
                               IO_CURSOR IN OUT T_CURSOR)
    IS 
        V_CURSOR T_CURSOR; 
    BEGIN 
        IF N_EMPNO <> 0 
        THEN
             OPEN V_CURSOR FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO 
                  AND EMP.EMPNO = N_EMPNO;

        ELSE 
             OPEN V_CURSOR FOR 
             SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME 
                  FROM EMP, DEPT 
                  WHERE EMP.DEPTNO = DEPT.DEPTNO;

        END IF;
        IO_CURSOR := V_CURSOR; 
    END OPEN_ONE_CURSOR; 

    PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
                                DEPTCURSOR OUT T_CURSOR)
    IS 
        V_CURSOR1 T_CURSOR; 
        V_CURSOR2 T_CURSOR; 
    BEGIN 
        OPEN V_CURSOR1 FOR SELECT * FROM EMP;
        OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
        EMPCURSOR  := V_CURSOR1; 
        DEPTCURSOR := V_CURSOR2; 
    END OPEN_TWO_CURSORS; 
END CURSPKG;
/
Another Example of Ref Cursor getting called in the package :
CREATE PACKAGE my_cursor AS

   TYPE MyCursor IS REF CURSOR;
   ...
END my_cursor;


CREATE PROCEDURE GetCarter ( proc_cv IN OUT my_cursor.MyCursor,
                             emp_name VARCHAR2(50) )
   ...

Then the client-side application code would start like

DECLARE
    local_cv        my_cursor.MyCursor;
    carter_record   carter%ROWTYPE
BEGIN
   GetCarter(local_cv,:employee)    -- employee is a host variable
   FETCH local_cv INTO carter_record;
   ...
END
/