|
SQL>
SQL> CREATE OR REPLACE PACKAGE pkg_table_func
2 IS
3 TYPE address_rec IS RECORD
4 (LINE1 VARCHAR2(20),
5 LINE2 VARCHAR2(20),
6 CITY VARCHAR2(20),
7 STATE_CODE VARCHAR2(2),
8 ZIP VARCHAR2(13),
9 COUNTRY_CODE VARCHAR2(4));
10
11 TYPE temp_adds IS TABLE OF address_rec;
12 END;
13 /
Package created.
SQL>
SQL> CREATE OR REPLACE FUNCTION myProc
2 RETURN pkg_table_func.temp_adds
3 PIPELINED
4 IS
5 addressValue pkg_table_func.address_rec;
6 BEGIN
7 FOR i IN 1..3 LOOP
8 IF (i=1) THEN
9 addressValue.line1 :='20 Spring St.';
10 addressValue.line2 :=null;
11 addressValue.city :='New York';
12 addressValue.state_code :='NY';
13 addressValue.zip :='10020';
14 addressValue.country_code :='USA';
15 ELSIF (i=2) THEN
16 addressValue.line1 :='Suite 206';
17 addressValue.line2 :='P Blvd';
18 addressValue.city :='B';
19 addressValue.state_code :='IL';
20 addressValue.zip :='60000';
21 addressValue.country_code :='USA';
22 ELSIF (i=3) THEN
23 addressValue.line1 :='1 Dr.';
24 addressValue.line2 :=null;
25 addressValue.city :='Vancouver';
26 addressValue.state_code :='NJ';
27 addressValue.zip :='22222';
28 addressValue.country_code :='USA';
29 END IF;
30 PIPE ROW(addressValue);
31 END LOOP;
32 RETURN;
33 END;
34 /
Function created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION f_table_plsql2_pipelined(p_ref_cursor SYS_REFCURSOR)
2 RETURN pkg_table_func.temp_adds PIPELINED
3 IS
4 addressValue1 pkg_table_func.address_rec;
5 addressValue2 pkg_table_func.address_rec;
6 BEGIN
7 LOOP
8 FETCH p_ref_cursor INTO addressValue1;
9 EXIT WHEN p_ref_cursor%NOTFOUND;
10 IF (addressValue1.city='New York') THEN
11 addressValue2.line1 :='P.O.Box 2215';
12 addressValue2.line2 :=null;
13 addressValue2.city :='New York';
14 addressValue2.state_code :='NY';
15 addressValue2.zip :='10020-2215';
16 addressValue2.country_code :='USA';
17 ELSIF (addressValue1.city='Bloomington') THEN
18 addressValue2.line1 :='P.O.Box 6615';
19 addressValue2.line2 :=null;
20 addressValue2.city :='Bloomington';
21 addressValue2.state_code :='IL';
22 addressValue2.zip :='60610-6615';
23 addressValue2.country_code :='USA';
24 ELSIF (addressValue1.city='Vancouver') THEN
25 addressValue2.line1 :='P.O.Box 0001';
26 addressValue2.line2 :=null;
27 addressValue2.city :='Vancouver';
28 addressValue2.state_code :='NJ';
29 addressValue2.zip :='08540';
30 addressValue2.country_code :='USA';
31 END IF;
32 PIPE ROW(addressValue2);
33 END LOOP;
34 close p_ref_cursor;
35 RETURN;
36 END;
37 /
Function created.
SQL>
SQL> SELECT * FROM TABLE(f_table_plsql2_pipelined(
2 CURSOR(SELECT * FROM TABLE(myProc()))));
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP COUN
------------- ----
P.O.Box 2215 null New York NY
10020-2215 USA
P.O.Box 2215 null New York NY
10020-2215 USA
P.O.Box 0001 null Vancouver NJ
08540 USA
3 rows selected.
SQL>
|