Ref Cursor in PL/SQL

      Ref cursor or Cursor Variable is a pointer to a result set in database. Ref cursor is the pl/sql datatype whose value is the memory address of a query work area in database.

Characteristics of a Ref Cursor:
1) Ref cursor refers to the Memory address on the database.
2) A ref cursor involves an additional database round trip. The data is not to be retrieved until the user attempts to read it.
3) Ref Cursor is not updatable. You cannot update the database by using Ref cursor.
4) A Ref Cursor is not backward scrollable. The data represented by the Ref Cursor is accessed in forward-only serial manner.
5) A Ref-Cursor is PL/SQL datatype. You create and return a ref cursor in PL/SQL Block.

Difference between Normal cursor and Ref Cursor:
1) Cursor doesn't have a return type but A Reference Cursor have a return type
2) REF cursor can be assigned dynamically while Normal cursor once defined cann't be changed.
3) Ref cursor can be associated with many no. of sql statements where cursor can be associated only with one sql statement.
4) Using Ref Cursor we can pass cursor data as parameter to the procedure or function but in normal cursor it is not.

Types of Ref Cursor:

There are 2 types of Ref Cursor

1) Strongly Typed Ref Cursor:
Ref Cursor with Return type is called strongly typed Ref Cursor.

2) Weakly Typed Ref Cursor:
Ref Cursor without return type is called weakly Typed Ref Cursor.

General Syntax:
TYPE cursor_name IS REF CURSOR [RETURN return_type];
Example for Strongly typed Ref Cursor:

Table creation Script
CREATE TABLE ref_cursor_test (id NUMBER,description VARCHAR2(100),price NUMBER) INSERT INTO ref_cursor_test VALUES(1,'abc',10); INSERT INTO ref_cursor_test VALUES(2,'def',15); INSERT INTO ref_cursor_test VALUES(3,'xyz',50);

PL/SQL Block for Strongly Typed ref cursor:
 DECLARE TYPE test_cur IS RECORD ( l_id ref_cursor_test.id%TYPE, l_desc ref_cursor_test.description%TYPE , l_price ref_cursor_test.price%TYPE ); test_cur_t test_cur; TYPE strong_ref_cur IS REF CURSOR RETURN ref_cursor_test%ROWTYPE; strng_ref strong_ref_cur; BEGIN OPEN strng_ref FOR SELECT id,description, price FROM ref_cursor_test; LOOP FETCH strng_ref INTO test_cur_t; EXIT WHEN strng_ref%NOTFOUND; DBMS_OUTPUT.put_line('Desc='||test_cur_t.l_desc||' '||'Price='||test_cur_t.l_price); END LOOP; CLOSE strng_ref; END;

OUTPUT:

Example for Weakly Typed Ref Cursor:

Refer the same table created above.
DECLARE TYPE weak_ref_cursor is REF CURSOR; ref_cursor_t weak_ref_cursor; v_desc ref_cursor_test.description%TYPE; BEGIN OPEN ref_cursor_t FOR SELECT description FROM ref_cursor_test; LOOP FETCH ref_cursor_t INTO v_desc; EXIT WHEN ref_cursor_t%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Description= '||v_desc); END LOOP; CLOSE ref_cursor_t; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Occured..'||SQLERRM); END;

Output:

Difference between Strongly Typed and Weakly typed Ref Cursor:
1) A strongly typed cursor always returns a known Type . But weakly typed ref Cursor has return type that depends on the SQL query executed.
2) In Strongly typed Ref Cursor the Compiler find the problems in a PL/SQL Block by comparing the types returned. But In weakly typed Ref cursors compiler cannot find the problem at compile time.
3) In strongly typed Dynamic query is not possible but in weakly typed Dynamic Query is possible.

SYS_REFCURSOR:
SYS_REFCURSOR is same as the weekly typed ref cursor and that can be used to pass cursors from and to a stored procedure.
If you are using SYS_REFCURSOR then no need to use the TYPE for declaration.

General Syntax :
Ref_cursor_name SYS_REFCURSOR;

Create a procedure and use the same table created above.
CREATE OR REPLACE PROCEDURE SYSREF_CUR_PRC (param IN SYS_REFCURSOR) AS l_num NUMBER; BEGIN LOOP FETCH param INTO l_num; EXIT WHEN param%NOTFOUND; DBMS_OUTPUT.PUT_LINE(l_num); END LOOP; END;

Call the procedure by passing ref cursor
DECLARE test_sys SYS_REFCURSOR; BEGIN OPEN test_sys FOR SELECT id FROM ref_cursor_test; SYSREF_CUR_PRC(test_sys); CLOSE test_sys; END;

Output: