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:
- Strong REF Cursors
- 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;