| 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))
|
