Ref Cursor

Q: What are Ref Cursor and its type?

A REF CURSOR is a cursor variable that actually contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR statement using the cursor variable. Currently pl/sql supports both strongly and weakly typed REF Cursor’s.

Pl/sql supports the declaration of a cursor variable using both the SYS_REFCURSOR built-in data type as well as creating a type of REF CURSOR and then declaring a variable of that type. SYS_REFCURSOR is a REF CURSOR type that allows any result set to be associated with it. This is known as a weakly-typed REF CURSOR.

For the strong ref cursor the returning columns with datatype and length need to be known at compile time. For the weak ref cursor the structure does not need to be known at compile time.

Declaring a SYS_REFCURSOR Cursor Variable:

          Name SYS_REFCURSOR;

Declaring a User Defined REF CURSOR Type Variable:

          Create a referenced cursor TYPE

                    TYPE emp_cur_type IS REF CURSOR RETURN EMP%ROWTYPE;

          Declare the actual cursor variable based on that TYPE

                    my_rec emp_cur_type;

Q: Example of each.

                    Strong ref cursor

                    Weak ref cursor

                    Passing Ref Cursors

Strong ref cursor:

          CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN emp%ROWTYPE;

PROCEDURE child (p_return_rec OUT return_cur);

PROCEDURE parent (p_NumRecs PLS_INTEGER); 

END strongly_typed;

/

CREATE OR REPLACE PACKAGE BODY strongly_typed IS

PROCEDURE child (p_return_rec OUT return_cur) IS

BEGIN

                    OPEN p_return_rec FOR   SELECT * FROM EMP;

END child; 

PROCEDURE parent (p_NumRecs PLS_INTEGER) IS

p_retcur return_cur;

                    at_rec   emp%ROWTYPE;

BEGIN

                    Child (p_retcur);

                    FOR i IN 1.. p_NumRecs

                    LOOP

                    FETCH p_retcur INTO at_rec;

                    dbms_output.put_line (at_rec.ename);

                    END LOOP;

END parent;

Weak ref cursor:

          CREATE OR REPLACE PROCEDURE test_proc(p_return_cur OUT SYS_REFCURSOR)

IS

BEGIN

                    OPEN p_return_cur FOR 'SELECT * FROM EMP;

END child;

/

DECLARE

cur sys_refcursor;   

all_rec    emp%ROWTYPE;

BEGIN

                    test_proc (cur);

                    FOR i  IN 1..10 LOOP

                              FETCH cur  INTO all_rec;

                              Dbms_Output.put_line (all_rec.ename);

                    END LOOP;

END; 

/

Passing Ref Cursors:

CREATE OR REPLACE PROCEDURE pass_ref_cur (p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN

          FETCH p_cursor BULK COLLECT INTO rec_array;

          FOR i IN rec_array.FIRST .. rec_array.LAST

          LOOP

                    dbms_output.put_line (rec_array (i));

          END LOOP;

END pass_ref_cur;

/ 

DECLARE

          rec_array SYS_REFCURSOR;

BEGIN

          OPEN rec_array FOR 'SELECT ename FROM emp';

          pass_ref_cur (rec_array);

          CLOSE rec_array;

END;

/