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. Ref Cursors are opened with an OPEN FOR statement
There are two types of refcursor
Strong REF Cursor and Weak REF Cursor
Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time. REF cursors are dynamic
Ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client. This allows you to pass that numeric cursor handle from PL/SQL code to an external client process.
Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)
Ref cursor can be passed from subroutine to subroutine - a cursor cannot be.
Strong REF Cursor : A REF CURSOR that specifies a specific return type
Weak REF Cursor : A REF CURSOR that does not specify the return type such as
SYS_REFCURSOR.
• A REF CURSOR involves an additional database round-trip.
•A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.
•A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.
1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.
2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.
3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.
4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.
5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.
6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.
Use of ref cursors should be limited to only when you have a requirement of returning result sets to
clients and when there is NO other efficient/effective means of achieving the goal.Generally we use ref
cursor when we need to return result set. Else it is advisable to use static cursor because they give
better performance.
Different ways to declare REF CURSORS
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- strong
TYPE genericcurtyp IS REF CURSOR; -- weak
cursor1 empcurtyp;
cursor2 genericcurtyp;
my_cursor SYS_REFCURSOR; -- didn't need to declare a new type (From 9i onwards for weak refcursor use this )
TYPE EmpRecTyp IS RECORD ( employee_id NUMBER,
last_name VARCHAR2(25), salary NUMBER(8,2));
TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
emp_cv EmpCurTyp; -- declare cursor variable
Opening a REF CURSOR (Cursor Variable)
OPEN cursorname FOR vquery [(optional) USING bindvariable_in_vquery ]
-- reamining as in Cursor loop,fetch ,exit ,end loop
CLOSE cursorname
