Pipelined Table Functions : table function « Object Oriented « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Object Oriented
3. table function

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13));
  7  /

Type created.

SQL>
SQL> CREATE or replace TYPE temp_adds IS TABLE OF address;
  2  /

Type created.

SQL>
SQL> CREATE OR REPLACE FUNCTION myProc
  2  RETURN temp_adds PIPELINED
  3  IS
  4    addressValue address;
  5  BEGIN
  6    FOR i IN 1..5 LOOP
  7      IF (i=1) THEN
  8        addressValue := address('St.',null,'York','NY','22222');
  9      ELSIF (i=2) THEN
 10        addressValue := address('Suite','Blvd','B','IL','33333');
 11      ELSIF (i=3) THEN
 12        addressValue := address('1 Dr.',null,'P','NJ','33333');
 13      ELSIF (i=4) THEN
 14        addressValue := address('#9','Avenue','Dallas','TX','11111');
 15      ELSIF (i=5) THEN
 16        addressValue := address('1 Ct.',null,'F','MA','44444');
 17      END IF;
 18      PIPE ROW(addressValue);
 19    END LOOP;
 20    RETURN;
 21  END;
 22  /

Function created.

SQL> show errors
No errors.
SQL>
SQL> SELECT * FROM TABLE(myProc);

LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
St.                  null                 York                 NY
22222

Suite                Blvd                 B                    IL
33333

1 Dr.                null                 P                    NJ
33333

#9                   Avenue               Dallas               TX
11111

1 Ct.                null                 F                    MA
44444


5 rows selected.

SQL>
SQL> drop type address force;

Type dropped.

SQL>
SQL> drop type temp_adds force;

Type dropped.