Multilevel Collection Types


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.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net