PL/SQL REF Cursor

What is a REF CURSOR?

1. A Ref Cursor is a PL/SQL data type or cursor variable.
2. A Ref Cursor is a pointer to a result set on the database. 
3. A Ref Cursor can be used to associate multiple Queries at run time dynamically. 
4. A Ref Cursor can be passed as a variable to a procedure or a function

What are the categories of REF CURSOR?
REF CURSOR is of 3 types:

1. Strong Ref Cursor - Ref Cursors which has a return type is classified as Strong Ref Cursor. Example :-

-- Strongly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN emp%ROWTYPE;
  c_cursor  t_ref_cursor;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;  

  CLOSE c_cursor;
END;
/

2. Weak Ref Cursor -  When a Ref-Cursor is defined without a return type, it is called as a weakly typed dynamic Ref-Cursor. 
Example :

-- Weakly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR;
  c_cursor  t_ref_cursor;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Weakly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;  

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

3. System Ref Cursor - SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle. SYS_REFCURSOR is available from Oracle 9i onwards as part of standard package and weak reference created for programmer easiness. 
Example:

-- REF CURSOR using SYS_RECURSOR.
DECLARE
  c_cursor  SYS_REFCURSOR;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('REF CURSOR using SYS_RECURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical