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;