Re: how to make function to return multiple fields??

jeffchirco_at_gmail.com wrote:
: Is there a way for a function to return multiple columns and multiple
: rows

: For simplicity, say I have a table EMPLOYEE like below;

: dbid dbfname dblname
: 1 Jeff Chirco
: 2 Dave Quackenbush
: 3 Warren Fitzgerald
: 4 Josh Freeze
: 5 Joe Escalante

: And I want my function to return all the contents of this table. How
: would I do this? Is it possible?
: My real function accepts a bunch of paremeters and does a lot of others
: things to get the results I want.

PL/SQL functions can return various datatypes. Datatypes can be defined for all sorts of things, including "collections" "objects", and "varrays".

This could be useful if you want to return one chunk of data that contains all of a set of values, which may or may not be what you want for your problem.

I have never asked a pl/sql function to return one of the types I mention above, but I don't recall reading any restrictions on the return datatype, from which I assume you could return one of the above mentioned types, any one of which could easily hold a list of items after you look them up and save them in memory.

The function might likely have to be part of a PACKAGE so that you could declare the datatype where the function knows about it, but presumably you would do that simply as part of what ever package of procedures you are working on that needs to call the function in the first place.

(this is not even pseudo code)

        CREATE PACKAGE BODY my_routines

        declare datatype complex_list as some kind of array ;

	function return_a_list ( ID IN number)
		returns complex_list
	is
	    my_list  complex_list;
	    i        number;
	begin
	    i := 0;
	    LOOP through the table
		FETCH some data based on ID
		end of data yet? or exit loop
		i := i+1;
		my_list(i).lname := the extracted data . lname;
		my_list(i).fname := the extract data . fname;
	etc etc etc

	    RETURN my_list;
	END;

	procedure TRYIT
	is
	    the_list complex_list;
	    an_lname   varchar2(200);	    
	    af_fname   varchar2(200);	    
	begin

	    the_list := return_a_list(53); // 53=meaningless example number

	    for i = 1 to the_list.count loop
		an_lname := the_list(i).lname;
		af_fname := the_list(i).fname;

	etc etc etc

As I said, that's hardly even pseudo code, but I hope it illustrates what I mean.

--

This space not for rent.