Reference Cursor in Oracle

After learning the concept of cursors and their types let me explain the term "Reference Cursor".

REFERENCE CURSOR/ REF CURSOR

Basically, REF Cursor is a data type, just like VARCHAR is a variable and holds the value of a string, similarly the REF cursor type holds a cursor. The cursor can be opened on the server and then passed to the "referenced to the same cursor" among all the programs that need the cursor access.

Syntax of REF Cursor

The following is the syntax of a REF Cursor type:

TYPE ref_type_name IS REF CURSOR

     [RETURN {cursor_name%ROWTYPE

             |ref_cursor_name%ROWTYPE

             |record_name%TYPE

             |record_type_name

             |table_name%ROWTYPE}

        ];

Note: The Return clause is optional, and generally used since it causes the cursor variable to be strongly typed.
 
Opening a REF cursor
 

PEN cursor_variable_name

FOR select_statement;
 
Example
 

DECLARE

  // defining a new datatype new_cursor

  TYPE new_cursor is REF CURSOR;

 

  // declared variable C_Cust of type new_cursor

   C_Cust new_cursor;           

   Cu Cust.Cname%type;

 

  // Statement begin

  BEGIN

 

  // cursor opened using select statement      

  OPEN C_Cust FOR SELECT Cname FROM Cust;   

  LOOP

 

  // retrieving the information from each row

  FETCH C_Cust INTO Cu;           

  EXIT WHEN C_Cust%notfound;

   dbms_output.put_line(Cu);

   END LOOP;

 

  // Cursor closed

  CLOSE C_Cust;                     

  END;
 
Reference cursors are one of the following two types:

  1. Strong REF Cursors
  2. Week REF Cursor

1. Strong REF Cursors

Also known as a static structure type, it presents the query results to a view or a table and typically announces itself to a package or a user-defined type of a cursor. Strong Cursors are defined
as in the following.

TYPE Strong_Cursor IS REFCURSOR

RETURN table_name%ROWTYPE;
 
Example
 

DECLARE

 TYPE new_refcursor is REF CURSOR

 RETURN cust%rowtype;

 c_cust new_refcursor;

 v_cu cust%rowtype;

 BEGIN

 OPEN cu FOR SELECT * FROM cust;

 LOOP

    FETCH cu INTO v_cu;

    EXIT WHEN cu%NOTFOUND;

    PRINT(v_cu.cust_id);

    PRINT(v_cu.Cust_name);

 END LOOP;

 CLOSE cu;

 

 print('-------------------------------------------------------------------------'):

  OPEN cu FOR SELECT * FROM cust;

  LOOP

     FETCH cu INTO v_cu;

     EXIT WHEN cu%NOTFOUND;

     PRINT(v_cu.cust_id);

     PRINT(v_cu.Cust_name);

 END LOOP;

 CLOSE cu;

 END;
 
2. Week REF Cursors

Also called as a dynamic structure type it presents a query result to a view or table and implements the SYS_REFCURSOR type. Week Cursors are defined as:
 

TYPE Weak_Cursor IS REFCURSOR;
 Example

DECLARE

 TYPE new_cursor is REF CURSOR

 ab new_refcursor;

 v_cu cust%rowtype;

 v_su supp%rowtype;

 

 BEGIN

 OPEN ab FOR SELECT * FROM cust;

 LOOP

    FETCH ab INTO v_cu;

    EXIT WHEN ab%NOTFOUND;

    PRINT(v_cu.cust_id);

    PRINT(v_cu.Cust_name);

 END LOOP;

 CLOSE ab;

 

 print('-------------------------------------------------------------------------'):

  OPEN ab FOR SELECT * FROM supp;

  LOOP

     FETCH cu INTO v_cu;

     EXIT WHEN cu%NOTFOUND;

     PRINT(v_su.supp_id);

     PRINT(v_sc.supp_name);

 END LOOP;

 CLOSE ab;

 END;