Row type Reference cursor : REFCURSOR « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. REFCURSOR

SQL>
SQL>
SQL> CREATE TABLE pizza (
  2  code NUMBER(38) CONSTRAINT pk_pizza PRIMARY KEY NOT NULL
  3  ,flavor VARCHAR2(30) NOT NULL
  4  );

Table created.

SQL>
SQL> CREATE OR REPLACE PACKAGE pizza_pkg
  2     AUTHID CURRENT_USER
  3  IS
  4     c_chocolate CONSTANT PLS_INTEGER := 16;
  5     c_strawberry CONSTANT PLS_INTEGER := 29;
  6
  7     TYPE codes_nt IS TABLE OF INTEGER;
  8
  9     my_pizza codes_nt;
 10
 11     TYPE fav_info_rct IS REF CURSOR RETURN pizza%ROWTYPE;
 12
 13     PROCEDURE show_pizza (list_in IN codes_nt);
 14
 15     FUNCTION most_popular RETURN fav_info_rct;
 16
 17  END pizza_pkg;
 18  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pizza_pkg
  2  IS
  3     g_most_popular   PLS_INTEGER;
  4
  5     PROCEDURE show_pizza (list_in IN codes_nt)
  6     IS
  7     BEGIN
  8        FOR indx IN list_in.FIRST .. list_in.LAST
  9        LOOP
 10           DBMS_OUTPUT.put_line (list_in (indx));
 11        END LOOP;
 12     END show_pizza;
 13
 14     FUNCTION most_popular RETURN fav_info_rct
 15     IS
 16        retval fav_info_rct;
 17        null_cv fav_info_rct;
 18     BEGIN
 19        OPEN retval FOR
 20        SELECT * FROM pizza WHERE code = g_most_popular;
 21
 22        RETURN retval;
 23     EXCEPTION
 24        WHEN NO_DATA_FOUND
 25        THEN
 26           RETURN null_cv;
 27     END most_popular;
 28
 29     PROCEDURE analyze_pizza (year_in IN INTEGER)
 30     IS
 31     BEGIN
 32
 33        NULL;
 34     END analyze_pizza;
 35
 36  BEGIN
 37     g_most_popular := c_chocolate;
 38
 39     analyze_pizza (EXTRACT (YEAR FROM SYSDATE));
 40  END pizza_pkg;
 41  /

SP2-0810: Package Body created with compilation warnings

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table pizza;

Table dropped.

SQL>
SQL>