REF CURSOR

REF CURSOR
A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a


Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an


OPEN FOR statement. In most other ways they behave similar to normal cursors.


Ref Cursors Oracle


The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like


pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries at


run time.


Example :-


Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1 empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;


REF CURSOR can be categorized into three


1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.


Example :-


Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;


Here empcurtyp is a Strong Ref Cursor


2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.


Example :-


Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;


Here empcurtyp is a Weak Ref Cursor


3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.


Declare
empcurtyp SYS_REFCURSOR;
…..
End;