12.9 Multilevel Collections

If you are using Oracle9i release
2 or later, you will be able to nest a collection inside of another
collection. Earlier in the chapter, we used a table called
cust_order_c that contained a collection of line
items. To illustrate multilevel collections, the
cust_order_c table will be converted to a type
definition and added to the customer table, so
that each customer record contains a collection of orders, and each
order contains a collection of line items:

While the INSERT statement above creates a single row, the statement
has actually created a customer entry, two orders, and three
line-items per order.

12.9.1 Querying Multilevel Collections

When querying
multilevel collections, you can use the
TABLE function to unnest your collections to make the data appear
relational. For instance, you can look at all of the line items
associated with Cooper Industries:

Since the unnested data is treated like a normal relational table,
you are free to use the full array of available functionality in your
queries. For example, you can use GROUP BY and aggregate functions,
as demonstrated by the following:

12.9.2 DML Operations on Multilevel Collections

To perform DML operations on a multilevel collection, you
need to isolate the collection to be modified via the TABLE function.
For example, the next statement adds 1 to the
quantity field of each line item for order number
9867 under Cooper Industries: