Multilevel collections

In Oracle9i you can now nest collections within collections—also referred to as support for multilevel collections. Let's take a look at an example and then discuss how you can use this feature in your applications.

Suppose that I want to build a system to maintain information about my pets. Besides their standard information, such as breed, name, and so on, I would like to keep track of their visits to the veterinarian. So I 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 are not associated with a pet (i.e., a foreign key to a pet table or object). You will soon see why I don't need to do that. Now I create a nested table of vet visits (we are supposed to go at least once a year):

CREATE TYPE vet_visits_t IS TABLE OF vet_visit_t

With these data structures defined, I now declare my object type to maintain information about my 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, I have declared this object type to be NOT FINAL so that I can extend this generic pet object type, taking advantage of Oracle9i's support for object type inheritance. (I might, for example, define a dog subtype of the generic pet supertype. See Chapter 21 for more details.)

So I have now declared an object type that contains as an attribute a nested table. I don't need a separate database table to keep track of these veterinarian visits; they are a part of my object.

Now let's take advantage of the new multilevel collections features of Oracle9i. In the following example, lines 1-10 define a multilevel collection; once the collection is populated, lines 11-14 access the collection.

/* File on web: 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;

The output from running this script is:

MercuryCheck cholesterol12

The following table explains what's going on in the code:

Line(s) Description
2-3 I declare a local associative array TYPE, in which each row contains a single pet object. I then declare a collection to keep track of my "bunch of pets."
5-10 I assign an object of type pet_t to the first row in this associative array. 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, I must provide a tag number, a name, and a list of vet visits, which is a nested table. To provide a nested table of type vet_visits_t, I must call the associated constructor (of the same name). I can either provide a null or empty list, or initialize the nested table with some values. I 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 I must use the object constructor and pass in a value for each attribute (date and reason for visit).
I display the value of the name attribute of the pet object in row 1 of the my_pets associative array.
I 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 array. That's a mouthful, and it is a "line-full" of code.
13-14 I demonstrate how you can use the collection methods (in this case, COUNT and LAST) on both outer and nested collections.

In this example we have the good fortune to be working with collections that, at each level, actually have names: the my_pets associative array and the petcare nested table. This is not always the case, as is illustrated in the next example.