PL SQL REF CURSOR

• A REF CURSOR is basically a data type.  A variable created based on such a data type is generally called a cursor variable.
• A cursor variable can be associated with different queries at run-time.
• The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.

Ref Cursors are categorized as of two types:

• Strongly Typed :The REF CURSOR type is restricted to an individual return type using the RETURN clause, reducing the chance of run-time errors, but restricting the flexibility.
• Weakly Typed: The RETURN clause is omitted allowing the type to reference any return type.  This gives greater flexibility, but it increases the likelihood of runtime errors because column mismatches are not picked up at compile time.

Example Strongly typed REF CURSOR:

DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  c_cursor  t_ref_cursor;

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

  OPEN c_cursor FOR
    SELECT *  FROM   cursor_variable_test;

  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;
/

Example Weakly typed REF CURSOR:

DECLARE
  TYPE t_ref_cursor IS REF CURSOR;
  c_cursor  t_ref_cursor;

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

  OPEN c_cursor FOR
    SELECT *   FROM   cursor_variable_test;

  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;
/

Example REF CURSOR with different queries:

DECLARE

TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp empcurtyp;

BEGIN

-- First find 10 arbitrary employees.

 OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11; process_emp_cv(emp); // it is already created.

CLOSE emp;

-- find employees matching a condition.

OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%'; process_emp_cv(emp);

CLOSE emp;

END;

/ 

Also Check: PL SQL Cursors

Happy Coding :)