Return user-defined type : Function Return « Function Procedure Packages « Oracle PL/SQL Tutorial

SQL>
SQL> create or replace type myRecordType as object
  2  ( seq int,
  3    a int,
  4    b varchar2(10),
  5    c date
  6  )
  7  /

Type created.

SQL>
SQL>
SQL> create or replace type myTableType
  2  as table of myRecordType
  3  /

Type created.

SQL>
SQL>
SQL>
SQL> create or replace function my_function return myTableType
  2  is
  3      l_data myTableType;
  4  begin
  5      l_data := myTableType();
  6
  7      for i in 1..5
  8      loop
  9          l_data.extend;
 10          l_data(i) := myRecordType( i, i, 'row ' || i, sysdate+i );
 11      end loop;
 12      return l_data;
 13  end;
 14  /

Function created.

SQL> drop type myTableType;

Type dropped.

SQL> drop type myRecordType;

Type dropped.

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