Collections of records

You define a collection of records by specifying a record type (through either %ROWTYPE or a programmer-defined record type) in the TABLE OF clause of the collection definition. This technique applies only to collection TYPEs that are declared inside a PL/SQL program. Nested table and VARRAY TYPEs defined in the database cannot reference %ROWTYPE record structures.

Here is an example of a collection of records based on a custom record TYPE:

CREATE OR REPLACE PACKAGE compensation_pkgIS TYPE reward_rt IS RECORD ( nm VARCHAR2(2000), sal NUMBER, comm NUMBER ); TYPE reward_tt IS TABLE OF reward_rt INDEX BY BINARY_INTEGER END compensation_pkg;

With these types defined in my package specification, I can now declare collections in other programs like this:

DECLARE holiday_bonuses compensation_pkg.reward_tt ;

Collections of records come in especially handy when you want to create in-memory (PGA) collections that have the same structure (and, at least in part, data) as database tables. Why would I want to do this? Suppose that I am running a batch process on Sunday at 3 AM against tables that are only modified during the week. I need to do some intensive analysis that involves multiple passes against the tables' data. I could simply query the data repetitively from the database, but that is a relatively slow, intensive process.

Alternately, I can copy the data from the table or tables into a collection and then move much more rapidly (and randomly) through my result set. I am, in essence, emulating bidirectional cursors in my PL/SQL code. Let's first look at a very simple example of moving data from a table to a collection. I will then provide a comprehensive prototype of emulation of a bidirectional cursor.

It actually takes very little code to transfer data from a table—or any query, in fact—to a collection.

DECLARE CURSOR hairstyles_cur IS SELECT * FROM hairstyles; TYPE local_hairstyles_tab IS TABLE OF hairstyles_cur%ROWTYPE INDEX BY BINARY_INTEGER; local_hairstyles local_hairstyles_tab;BEGIN FOR hairstyles_rec IN hairstyles_cur LOOP local_hairstyles (hairstyles_rec.code) := hairstyles_rec; END LOOP;END;

In this case, I am using the hairstyles code, which is an integer value, as the row number for the hairstyle information. This is an example of filling an associative array nonsequentially. Why would I do this? Because now if I have the hairstyle code (which could come from another program or be entered by the user through a screen interface), I can immediately retrieve the hairstyles description without doing another lookup from the database.

So that's the basic idea. Now let's take a look at emulating a bidirectional cursor. In this case, I want to read my data just once from the database and then be able to move back and forth through my dataset without any more roundtrips to the SGA. There are two basic approaches I can take:

· Embed all of the collection code in my main program

· Create a separate package to encapsulate access to the data in the collection

I generally choose the second approach for most situations. In other words, I find it useful to create separate, well-defined, and highly reusable APIs to complex data structures and logic. Here is the package specification for my bidirectional cursor emulator:

/* File on web: bidir.pkg */CREATE OR REPLACE PACKAGE bidirIS FUNCTION rowforid (id_in IN employee.employee_id%TYPE) RETURN employee%ROWTYPE; FUNCTION firstrow RETURN PLS_INTEGER; FUNCTION lastrow RETURN PLS_INTEGER; FUNCTION rowCount RETURN PLS_INTEGER; FUNCTION end_of_data RETURN BOOLEAN; PROCEDURE setrow (nth IN PLS_INTEGER); FUNCTION currrow RETURN employee%ROWTYPE; PROCEDURE nextrow; PROCEDURE prevrow;END;

So how do you use this API? Here is an example of a program using this API to read through the result set for the employee table, first forward and then backward:

DECLARE l_employee employee%ROWTYPE;BEGIN LOOP EXIT WHEN bidir.end_of_data; l_employee := bidir.currrow; DBMS_OUTPUT.put_line (l_employee.last_name); bidir.nextrow; END LOOP; bidir.setrow (bidir.lastrow); LOOP EXIT WHEN bidir.end_of_data; l_employee := bidir.currrow; DBMS_OUTPUT.put_line (l_employee.last_name); bidir.prevrow; END LOOP;END;

An astute reader will now be asking: when is the collection loaded up with the data? Or even better: where is the collection? There is no evidence of a collection anywhere in the code I have presented.

Let's take the second question first. The reason you don't see the collection is that I have hidden it behind my package specification. A user of the package never touches the collection and doesn't have to know anything about it. That is the whole point of the API. You just call one or another of the programs that will do all the work of traversing the collection (data set) for you.

Now, when and how is the collection loaded? This may seem a bit magical until you read about packages in Chapter 17. If you look in the package body, you will find that it has an initialization section as follows:

BEGIN -- Package initialization FOR rec IN (SELECT * FROM employee) LOOP employees (rec.employee_id) := rec; END LOOP; g_currrow := firstrow;END;
Note that g_currrow is defined in the package body and therefore was not listed in the specification above.

 

 

This means that the very first time I reference any element in the package specification, this code is run automatically, transferring the contents of the employee table to my employees collection. When does that happen in my sample program shown earlier? Inside my loop, when I call the bidir.end_of_data function to see if I am done looking through my data set!

I encourage you to examine the package implementation. The code is very basic and easy to understand; the benefits of this approach can be dramatic.