Re: PL/SQL function returning a table?

Rupesh Kapoor (rupesh_at_whowhere.com) wrote:
: Hi,
 

: Is there a way to write a PL/SQL function that returns multiple rows (a
: PL/SQL table of records)?
 

: SELECT pl_sql_func() FROM table_name;
 

: returns only one row at a time.

: Thanks
: Rupesh

Rupesh,

There are restrictions on the usage of functions within SQL statements, one of which is that is can return only simple data types (number, varchar2, date, etc). Hence your example will return <n> rows of the same thing, where <n> is the number of rows in your table_name. Since your function takes no arguments, it will return the same result every time, unless it uses sysdate or some randomisation function.

However (big however), if you are calling your PL/SQL function within PL/SQL, it can return complex datatypes (records, cursor refs, PL/SQL tables, etc). For example:

declare

	type myTable is table of emp.ename%type
		index by binary_integer;
	function staff_list(i_mgr	emp.empno%type) return myTable;

	my_staff	myTable;
	me			emp.empno%type;
...
	my_staff:=staff_list(me);
	for i in my_staff loop
	...

Apologies if the syntax is duff, but you get the idea?

Have a look at the Application Developer's Guide for more details of calling functions within SQL.

MPP

--
Martin Purbrook           +44 1473 605208
martin_at_kbss.bt.co.uk (FAX)+44 1473 622451