With the release of the Oracle9 i database, you can create a collection type in the database whose elements are also a collection type; this is known as a multilevel collection type . The following list shows the valid multilevel collection types:
A nested table containing a nested table type
A nested table containing a varray type
A varray containing a varray type
A varray containing a nested table type
A varray or nested table of an object type that has an attribute that is a varray or nested table type
Note | I ve provided a SQL*Plus script named collection_schema_9i.sql in the SQL directory that creates a user named collection_user2 with a password of collection_password , along with the types and the table described in this section. You can run this script if you are using an Oracle9 i database or higher. You ll notice I create a completely different user named collection_user2 that has its own types and tables separate from collection_user you saw earlier in this chapter. |
To consider an example of a multilevel collection type, let s say you wanted to store a set of phone numbers that are associated with each address of a customer. The following example creates a varray type of three VARCHAR2 strings named varray_phone_typ to represent phone numbers:
CREATE TYPE varray_phone_typ AS VARRAY(3) OF VARCHAR2(14); /
Next , the following example creates an object type named address_typ that contains an attribute named phone_numbers; this attribute is defined using varray_phone_typ :
CREATE TYPE address_typ AS OBJECT (street VARCHAR2(15), city VARCHAR2(15), state CHAR(2), zip VARCHAR2(5), phone_numbers varray_phone_typ); /
The next example creates a nested table type of address_typ objects:
CREATE TYPE nested_table_address_typ AS TABLE OF address_typ; /
The following example creates a table named customers_with_nested_table that contains a column named addresses of nested_table_address_typ :
CREATE TABLE customers_with_nested_table (id INTEGER PRIMARY KEY, first_name VARCHAR2(10), last_name VARCHAR2(10), addresses nested_table_address_typ) NESTED TABLE addresses STORE AS nested_addresses;
Finally, the next example inserts a row into customers_with_nested_table; notice the use of the constructors for the three types in the INSERT statement:
INSERT INTO customers_with_nested_table VALUES (1, 'Steve', 'Brown', nested_table_address_typ(address_typ('2 State Street', 'Beantown', 'MA', '12345', varray_phone_typ('(800)-555-1211', '(800)-555-1212', '(800)-555-1213')), address_typ('4 Hill Street', 'Lost Town', 'CA', '54321', varray_phone_typ('(800)-555-1211', '(800)-555-1212'))));
You can see that the first address has three phone numbers, while the second address only has two. Multilevel collection types are a very powerful extension to the Oracle9 i database, and you might want to consider using them in any database designs you contribute to.