Oracle SQL : REF Cursors

Oracle Cursor types

What is the difference between Cursor and REF Cursor??


REF cursors are different than your typical, standard cursors.
With standard cursors, you know the cursor's query ahead of time.
With REF cursors, you do not have to know the query ahead of time. REF cursors let you create a cursor to be used in your PL/SQL block, for instance, and use that cursor, without knowing ahead of time what your particular query might be.
This is often used when the cursor's query will be given to a stored procedure or function at run time.
For instance, you might want a function to compute the average of a column on a particular table.
But you don't know ahead of time what the table or column will be. With regular cursors, you would need to know. But with REF cursors, you can pass the table and column to the function, and have the REF cursor build the cursor "on the fly".




----- Passing Ref Cursor...

CREATE TABLE xxxemp (
empid   NUMBER(5),
empname VARCHAR2(30));

INSERT INTO xxemp (empid, empname) VALUES (1, 'ABC-1');
INSERT INTO xxemp (empid, empname) VALUES (2, 'ABC-2');
INSERT INTO xxemp(empid, empname) VALUES (3, 'ABC-3');
COMMIT;

CREATE OR REPLACE PROCEDURE pass_ref_cursor (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_cursor;
/

set serveroutput on

DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM xxemp';

  pass_ref_cursor(rec_array);
  CLOSE rec_array;
END;
/



------weekly Typed REF Cursor----


CREATE OR REPLACE PROCEDURE child (
  p_NumRecs IN PLS_INTEGER,
  p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
   OPEN p_return_cur FOR
   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;

END child;
/
Parent Procedure CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS

p_retcur   SYS_REFCURSOR;
at_rec     all_tables%ROWTYPE;

BEGIN
   child(pNumRecs, p_retcur);

   FOR i IN 1 .. pNumRecs
   LOOP
      FETCH p_retcur
      INTO at_rec;

      DBMS_OUTPUT.PUT_LINE(at_rec.table_name ||
      ' - ' || at_rec.tablespace_name ||
      ' - ' || TO_CHAR(at_rec.initial_extent) ||
      ' - ' || TO_CHAR(at_rec.next_extent));
   END LOOP;

END parent;

To Run The Demo SQL> SET SERVEROUTPUT ON

SQL> exec parent(1)
SQL> exec parent(17)





----- Strongly typed REF Cursor----------

CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;

PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);

END strongly_typed;
/
Parent Procedure 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 all_tables;

END child;
--==============================================
PROCEDURE parent (p_NumRecs PLS_INTEGER) IS

p_retcur return_cur;
at_rec   all_tables%ROWTYPE;

BEGIN
   child(p_retcur);

   FOR i IN 1 .. p_NumRecs
   LOOP
      FETCH p_retcur
      INTO at_rec;

      DBMS_OUTPUT.PUT_LINE(at_rec.table_name ||
      ' - ' || at_rec.tablespace_name ||
      ' - ' || TO_CHAR(at_rec.initial_extent) ||
      ' - ' || TO_CHAR(at_rec.next_extent));
   END LOOP;

END parent;

END strongly_typed;
/
To Run The Demo SQL> SET SERVEROUTPUT ON

SQL> exec strongly_typed.parent(1)
SQL> exec strongly_typed.parent(8)