Mega Code Archive
Row type Reference cursor
<![CDATA[
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> drop table pizza;
Table dropped.
SQL>
]]>