Ref Cursors Oracle
Example :-
Declare
TYPE honni IS REF CURSOR RETURN emp%ROWTYPE;
refcur1 honni;
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 honni IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;
Here honni 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 honni IS REF CURSOR;
…..
End;
Here honni 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
honni SYS_REFCURSOR;
…..
End;
Advantages
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )
Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values
In general Ref Cursors are only be used when static cursor cannot do the work
Example :-
Declare
TYPE honni IS REF CURSOR RETURN emp%ROWTYPE;
refcur1 honni;
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 honni IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;
Here honni 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 honni IS REF CURSOR;
…..
End;
Here honni 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
honni SYS_REFCURSOR;
…..
End;
Advantages
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )
Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values
In general Ref Cursors are only be used when static cursor cannot do the work