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))
|
No comments:
Post a Comment