A pipelined Table Function that returns a PL/SQL type : Function Return « Function Procedure Packages « Oracle PL/SQL Tutorial

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 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 :='Bloomington';
 19        addressValue.state_code :='IL';
 20        addressValue.zip :='11111';
 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> SELECT * FROM TABLE(myProc);

LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP           COUN
------------- ----
20 St.               null                 New York             NY
10020         USA

Suite 206            P Blvd               Bloomington          IL
11111         USA

1  Dr.               null                 Vancouver            NJ
22222         USA


3 rows selected.

SQL>

27.3.Function Return
	27.3.1.	Return Types
	27.3.2.	Returning values with functions
	27.3.3.	Return number from a function
	27.3.4.	Return value from a function
	27.3.5.	Multiple RETURN Statements
	27.3.6.	Returning a list based on parameters
	27.3.7.	Return date value from a function
	27.3.8.	A pipelined Table Function that returns a PL/SQL type
	27.3.9.	Return column type
	27.3.10.	Statements after Return will not be executed
	27.3.11.	create a function to return a employee record. accept employee numner return all fields.
	27.3.12.	Return a table collection
	27.3.13.	Demonstrate returning a record.
	27.3.14.	Return cursor from function
	27.3.15.	Return user-defined type