Multi-Level Collections in Oracle 9iby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn
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.
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.
Also In This Series
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
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:
pet_tis 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
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;
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
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
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
Once the collection has been populated, we can access its data. We do this in
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:
14, we demonstrate how you can use the collection methods (in
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.