tests/ctags/refcurs.sql

   1 rem -----------------------------------------------------------------------
   2 rem URL:        http://www.orafaq.com/scripts/plsql/refcurs.txt
   3 rem Filename:   refcurs.sql
   4 rem Purpose:    Pass result sets (REF CURSOR) between procedures and
   5 rem             functions
   6 rem Date:       15-Jun-2001
   7 rem Author:     Frank Naude (frank@ibi.co.za)
   8 rem -----------------------------------------------------------------------
   9
  10 set serveroutput on
  11
  12 -- Define TYPES package separately to be available to all programming
  13 -- environments...
  14 CREATE OR REPLACE PACKAGE types AS
  15    TYPE cursortyp is REF CURSOR;   -- use weak form
  16 END;
  17 /
  18
  19 -- Create test package to demonstrate passing result sets...
  20 CREATE OR REPLACE PACKAGE test_ref_cursor AS
  21    PROCEDURE main;
  22    FUNCTION  get_cursor_ref(typ NUMBER) RETURN types.cursortyp;
  23    PROCEDURE process_cursor(cur types.cursortyp);
  24 END;
  25 /
  26 show errors
  27
  28
  29 CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS
  30
  31   -- Main program entry point
  32   PROCEDURE main IS
  33   BEGIN
  34     process_cursor( get_cursor_ref(1) );
  35     process_cursor( get_cursor_ref(2) );
  36   END;
  37
  38   -- Get and return a CURSOR REF/ Result Set
  39   FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS
  40     cur  types.cursortyp;
  41   BEGIN
  42     if typ = 1 THEN
  43       OPEN cur FOR SELECT * FROM emp  WHERE ROWNUM < 5;
  44     ELSE
  45       OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;
  46     END IF;
  47     RETURN cur;
  48   END;
  49
  50   -- Process rows for an EMP or DEPT cursor
  51   PROCEDURE process_cursor(cur types.cursortyp) IS
  52     empRec  emp%ROWTYPE;
  53     deptRec dept%ROWTYPE;
  54   BEGIN
  55     LOOP
  56       FETCH cur INTO empRec;    -- Maybe it was an EMP cursor, try to fetch...
  57       EXIT WHEN cur%NOTFOUND;
  58       dbms_output.put_line('EMP ROW: '||empRec.ename);
  59     END LOOP;
  60   EXCEPTION
  61     WHEN ROWTYPE_MISMATCH THEN  -- OK, so it was't EMP, let's try DEPT.
  62        LOOP
  63          FETCH cur INTO deptRec;
  64          EXIT WHEN cur%NOTFOUND;
  65          dbms_output.put_line('DEPT ROW: '||deptRec.dname);
  66        END LOOP;
  67   END;
  68
  69 END;
  70 /
  71 show errors
  72
  73
  74 EXEC test_ref_cursor.main;
  75