12.6 Collection Functions

Oracle Database 10g supplies a number of functions that are useful when working with nested table collections. For example, if you are interested in the number of elements in a given collection, you can use the CARDINALITY function:

SELECT co.order_nbr, co.cust_nbr, co.order_dt,

  CARDINALITY(co.order_items) number_of_items

FROM cust_order_c co;



 ORDER_NBR   CUST_NBR ORDER_DT  NUMBER_OF_ITEMS

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

      1000       9568 21-MAR-01               3

Compare this with the following query, which obtains the same results without the benefit of the CARDINALITY function:

SELECT co.order_nbr, co.cust_nbr, co.order_dt,

  COUNT(*) number_of_items

FROM cust_order_c co, TABLE(co.order_items) oi

GROUP BY co.order_nbr, co.cust_nbr, co.order_dt;



 ORDER_NBR   CUST_NBR ORDER_DT  NUMBER_OF_ITEMS

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

      1000       9568 21-MAR-01               3

If you would like to perform set operations on multiple collections, there are functions that perform the equivalent of UNION, UNION ALL, MINUS, and INTERSECT. To illustrate these functions, we will add another row to the cust_order_c table and then perform set operations against the two rows in the table:

INSERT INTO cust_order_c (order_nbr, cust_nbr, sales_emp_id,

  order_dt, sale_price, order_items)

VALUES (1001, 9679, 275, TO_DATE('15-DEC-2003','DD-MON-YYYY'), 8645, 

  line_item_tbl(

    line_item_obj('A675-015', 25),

    line_item_obj('TX-475-A2', 7)));

Here's a look at our two sets of line items:

ORDER_ITEMS(PART_NBR, QUANTITY)

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

LINE_ITEM_TBL(LINE_ITEM_OBJ('A675-015', 25), 

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

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



LINE_ITEM_TBL(LINE_ITEM_OBJ('A675-015', 25), 

              LINE_ITEM_OBJ('TX-475-A2', 7))

As you can see, the two sets of line items share one common element (quantity 25 of part number A675-015). The next query demonstrates how the MULTISET UNION DISTINCT function can be used to build a new instance of line_item_tbl with the distinct set of line items:

SELECT co_1.order_items 

  MULTISET UNION DISTINCT

  co_2.order_items distinct_items

FROM cust_order_c co_1, cust_order_c co_2

WHERE co_1.order_nbr = 1000 and co_2.order_nbr = 1001;



DISTINCT_ITEMS(PART_NBR, QUANTITY)

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

LINE_ITEM_TBL(LINE_ITEM_OBJ('A675-015', 25), 

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

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

              LINE_ITEM_OBJ('TX-475-A2', 7))

If you want the non-distinct union of line items from the two sets, simply replace the keyword DISTINCT with ALL:

SELECT co_1.order_items 

  MULTISET UNION ALL

  co_2.order_items all_items

FROM cust_order_c co_1, cust_order_c co_2

WHERE co_1.order_nbr = 1000 and co_2.order_nbr = 1001;



ALL_ITEMS(PART_NBR, QUANTITY)

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

LINE_ITEM_TBL(LINE_ITEM_OBJ('A675-015', 25), 

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

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

              LINE_ITEM_OBJ('A675-015', 25), 

              LINE_ITEM_OBJ('TX-475-A2', 7))

As you would expect, the common line item now appears twice in the all_items collection.

If you want the functionality of the MINUS set operator, you can use the MULTISET EXCEPT function. The keyword EXCEPT is used rather than MINUS, to conform to the ANSI/ISO SQL standard:

SELECT co_1.order_items 

  MULTISET EXCEPT

  co_2.order_items diff_items

FROM cust_order_c co_1, cust_order_c co_2

WHERE co_1.order_nbr = 1000 and co_2.order_nbr = 1001;



DIFF_ITEMS(PART_NBR, QUANTITY)

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

LINE_ITEM_TBL(LINE_ITEM_OBJ('GX5-2786-A2', 1), 

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

Finally, if you desire to generate the intersection between the two sets, you can use the MULTISET INTERSECT function:

SELECT co_1.order_items 

  MULTISET INTERSECT

  co_2.order_items common_items

FROM cust_order_c co_1, cust_order_c co_2

WHERE co_1.order_nbr = 1000 and co_2.order_nbr = 1001;



COMMON_ITEMS(PART_NBR,



 QUANTITY)

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

LINE_ITEM_TBL(LINE_ITEM_OBJ('A675-015', 25))