Multi-Level Collections in Oracle 9i by Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn 02/11/2003

Editor's note: In part 5 in their continuing series on new Oracle 9i features, Steven Feuerstein and Bryn Llewellyn introduced cursor expressions and showed how those expressions can be utilized with table functions. In this article, our Oracle gurus show you how to work with multi-level collections, using numerous examples that progress from the simple to the complex.

Collections

New to Oracle 9i, you can now nest collections within collections, also referred to as support for "multi-level collections." A collection is a data structure (actually, three different, but similar data structures: index-by tables, nested tables, and varying arrays) that acts like a list or array. Collections are, in fact, the closest you can get to traditional arrays in the PL/SQL language, though there are a number of differences. Developers use collections to manage lists of information in their programs—or even within columns in a database table.

Use of collections can both simplify the code we need to write and optimize performance of that code. A collection can, for example, be used as the target of a bulk-bind query to improve the performance of data transfer between the database and the PL/SQL processing. Collections can serve as a "local cache," avoiding repetitive queries against the database.

Prior to Oracle 9i, collections could only be used to represent a single dimension of information (a list of names or salaries). With Oracle 9i and support for multi-level collections, PL/SQL developers can now model multi-dimensional phenomena, which greatly enlarges the set of real-world problems that we can address.

This article will demonstrate how to use multi-level collections, first with a simple example and then a more complex application of this feature.

An Introduction to Multi-Level Collections

Suppose we want to build a system to maintain information about pets. Besides their standard information, such as species, name, and so on, we'd like to keep track of their visits to the veterinarian. So we create a vet-visit object type:

create type vet_visit_t is object (
   visit_date  date,
   reason      varchar2 (100)
   );
/

Notice that objects instantiated from this type aren't associated with a pet (that is, a foreign key to a pet table or object). You'll soon see why we don't need to do that. Now we create a nested table of vet visits (we are, after all, supposed to visit the vet at least once a year):

create type vet_visits_t is table of vet_visit_t
/

With these data structures defined, we'll now declare our object type to maintain information about the pets:

create type pet_t is object (
   tag_no   integer,
   name     varchar2 (60),
   petcare vet_visits_t, 
   member function set_tag_no (
     new_tag_no in integer) return pet_t)
   not final;
/

This object type has three attributes and one member method. Any object instantiated from this type will have associated with it a tag number, name, and a list of visits to the vet. You can also modify the tag number for that pet by calling the set_tag_no program. Finally, we've declared this object type to be NOT FINAL so that we can extend this generic pet object type, taking advantage of Oracle9i's support for object type inheritance.

So we've now declared an object type that contains an attribute and a nested table. We don't need a separate database table to keep track of these veterinarian visits; they're a part of our object.

Now let's take advantage of the new multi-level collections features of Oracle 9i. In lines 2-3 of the anonymous block shown in Example 1, we declare a local associative table TYPE, in which each row contains a single pet object. We then declare a collection to keep track of this "bunch of pets."

Example 1. Defining and accessing a multi-level collection.

/* file multilevel_collections.sql */
 1  declare
 2     type bunch_of_pets_t is table of pet_t index by binary_integer;
 3     my_pets   bunch_of_pets_t;
 4  begin
 5     my_pets (1) :=
 6           pet_t (100, 'Mercury',
 7              vet_visits_t (
 8                 vet_visit_t ('01-Jan-2001', 'Clip wings'),
 9                 vet_visit_t ('01-Apr-2002', 'Check cholesterol'))
10           );
11     dbms_output.put_line (my_pets (1).name);
12     dbms_output.put_line (my_pets (1).petcare (2).reason);
13     dbms_output.put_line (my_pets.count);
14     dbms_output.put_line (my_pets(1).petcare.last);
15  end;

Related Reading Oracle PL/SQL Programming By Steven Feuerstein Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

Lines 5-10 assign an object of type pet_t to the first row in this associative table. As you can see, the syntax required when working with nested, complex objects of this sort can be quite intimidating. So let's "parse" the various steps required.

To instantiate an object of type pet_t, we must provide a tag number, name, and list of vet visits, which is a nested table. To provide a nested table of type vet_visits_t, we must call the associated constructor (of the same name). We can either provide a null or empty list, or initialize the nested table with some values. We do this in lines 8 and 9. Each row in the vet_visits_t collection is an object of type vet_visit_t, so again we must use the object constructor and pass in a value for each attribute (date and reason for visit).

Once the collection has been populated, we can access its data. We do this in lines 11-14. In line 11, we display the value of the name attribute of the pet object in row 1 of the my_pets associative table. In line 12, we display the value of the reason attribute of the vet visit object in row 2 of the nested table, which in turn resides in the first row of the my_pets associative table. Mmm. That's a mouthful, and it's a "line-full" of code:

dbms_output.put_line (my_pets(1).petcare(2).reason);

On lines 13 and 14, we demonstrate how you can use the collection methods (in this case, COUNT and LAST) on both outer and nested collections.

The output from running this script is:

Mercury
Check cholesterol
1
2

Let's now take a look at a more complex example of applying multi-level collections.

Pages: 1, 2, 3, 4

Next Page