REF Cursors

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