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:

CREATE TYPE line_item_obj AS OBJECT (

  part_nbr VARCHAR2(20),

  quantity NUMBER(8,2));



CREATE TYPE line_item_tbl AS TABLE OF line_item_obj;



CREATE TYPE cust_order_obj AS OBJECT (

  order_nbr NUMBER(8),

  sales_emp_id NUMBER(6),

  order_dt DATE,

  sale_price NUMBER(9,2),

  order_items line_item_tbl);



CREATE TYPE cust_order_tbl AS TABLE OF cust_order_obj;



CREATE TABLE customer_c (

  cust_nbr NUMBER(5) NOT NULL,

  name VARCHAR2(30) NOT NULL,

  region_id NUMBER(5),

  inactive_dt DATE,

  inactive_ind CHAR(1),

  orders cust_order_tbl)

NESTED TABLE orders STORE AS orders_c

  (NESTED TABLE order_items STORE AS order_items_c);

Now that the schema is in place, let's add some data:

INSERT INTO customer_c (cust_nbr, name, region_id, 

  inactive_dt, inactive_ind, orders)

VALUES (1, 'Cooper Industries', 5, NULL, 'N', 

  cust_order_tbl(

    cust_order_obj(9568, 275, 

      TO_DATE('21-MAR-2001','DD-MON-YYYY'), 15753, 

      line_item_tbl(

        line_item_obj('A675-015', 25),

        line_item_obj('GX5-2786-A2', 1),

        line_item_obj('X378-9JT-2', 3))),

    cust_order_obj(9867, 275, 

      TO_DATE('08-DEC-2003','DD-MON-YYYY'), 22575, 

      line_item_tbl(

        line_item_obj('A675-015', 43),

        line_item_obj('GX5-2786-A2', 1),

        line_item_obj('X378-9JT-2', 6)))));

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:

SELECT li.*

FROM customer_c c, TABLE(c.orders) o, TABLE(o.order_items) li

WHERE c.name = 'Cooper Industries';



PART_NBR               QUANTITY

-------------------- ----------

A675-015                     25

GX5-2786-A2                   1

X378-9JT-2                    3

A675-015                     43

GX5-2786-A2                   1

X378-9JT-2                    6

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:

SELECT o.order_nbr, SUM(li.quantity)

FROM customer_c c, TABLE(c.orders) o, TABLE(o.order_items) li

WHERE c.name = 'Cooper Industries'

GROUP BY o.order_nbr;



ORDER_NBR SUM(LI.QUANTITY)

---------- ----------------

      9568               29

      9867               50

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:

UPDATE TABLE(

   SELECT o.order_items 

   FROM TABLE(

      SELECT c.orders 

      FROM customer_c c

      WHERE c.name = 'Cooper Industries') o

   WHERE o.order_nbr = 9867) li

SET li.quantity = li.quantity + 1;

The target of this update is the table returned by the outermost TABLE function.