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

"Justin" <dumpywalsh_at_yahoo.com> wrote in message news:c3a67ae5.0210230541.2312da4d_at_posting.google.com...
> 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.

Justin,

the code above only describes the personTableType, it doesn't declare the PL/SQL table. You need an extra declaration e.g.

personTable personTableType;

To populate the PL/SQL table you can add either a whole record at a time e.g

personTable(i) := personRecord;

Where i is a binary integer value which identfies the specific record within the table and personRecord is a record declared or equivalent to personRecordType. Alternatively you can populate the fields individually e.g.

personTable(i).person_id := person_id_value;

personTable(i).surname:= surname_value;

To output the table data you can do it a record at a time e.g.

personRecord := personTable(i) ;

Or a field at a time e.g.

person_id_value := personTable(i).person_id ; surname_value := personTable(i).surname;

Hope this helps.

Paul Dixon Received on Wed Oct 23 2002 - 09:55:35 CDT