Using PL/SQL to collect data from multiple tables and returning this as a cursor (resultset)

Hi

I have a large amount of data in multiple tables from which a very small subset of data needs to be retrieved. Due to the relatively complex logic required to select the data it is impossible to execute the query as a single sql statement. Instead, a main query was written which returned multiple rows. For each of these rows returned I execute a number of 'smaller' queries to get all the info I need, building up an object representation of the results returned.

Because of the resource intensive nature of this setup I was hoping to move much of the SQL processing logic (and admittedly a small amount of query specific business logic) from the client (in this case is a J2EE application server) to the database using a stored procedures and PL/SQL. This would eliminate the roundtrips between the database and client for all the subqueries.

What I hoped to do was the following:

• Create a 'temporary-table' or similar structure into which the records would be built up
• Return a cursor for the temporary table that I just created
  • Use this as a resultset over which I could iterate and retrieve the (flattened) data

For example.
Assume that I have a table with personal data (person_id, name, surname) and a table with membership data (person_id, membership_no). I would like a single row back per person that summarises the personal data and membership data.

That single row would have the following structure:

        person_id, name, surname, membership_no

So first I would select the name and surname into this temporary structure and then for each person - get the memebership_no

(This example is contrived as it would be a lot simpler to join person on membership in this case. In the real case however, the logic required to get the membership_no would not be able to be executed in a single sql statement.)

I have read through a lot of the Oracle documentation and cannot find something that matches my needs. The closest that I can get to is pl/sql object of type RECORD and TABLE:

TYPE personRecordType IS RECORD (

	person_id		VARCHAR2(20),
	surname		VARCHAR2(20)
	);

TYPE personTableType IS TABLE OF personRecordType INDEX BY BINARY_INTEGER; But any attempt to bulk load the person data into the TABLE object fails (does not even compile). And I can't event begin to think how I am going to return a cursor over the TABLE object.

Perhaps I am barking up the wrong tree - am I using the correct oracle object here? Any ideas would be much appreciated.

Thanks

Justin Received on Wed Oct 23 2002 - 08:41:42 CDT