12.7 Comparing Collections

If you are using nested tables, you can compare the structure and content of one collection to another. If the nested tables being compared contain a built-in data type such as NUMBER or VARCHAR2, the Oracle server will do the comparison for you; if the nested tables contain an object type, however, you will need to write a MAP method to tell the server how to compare multiple instance of your object type. The following code fragment demonstrates a simple mapping function for the line_item_obj type:

CREATE TYPE BODY line_item_obj AS 

  MAP MEMBER FUNCTION to_string RETURN VARCHAR2 IS

  BEGIN

    RETURN part_nbr || ':' || to_char(quantity, '00009');

  END to_string;

END;

The to_string mapping function simply returns the part number concatenated to the quantity with a colon between. Oracle will call this function (there can only be one mapping function defined for each of your object types) whenever two instances of the type are being compared.

Now that the mapping function is in place, you can begin comparing different collections of line_item_obj. For example, if you want to find a customer order containing a certain set of line items, you can use the equality operator:

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

FROM cust_order_c co

WHERE co.order_items =

  line_item_tbl(

    line_item_obj('A675-015', 25),

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

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



ORDER_NBR    CUST_NBR ORDER_DT

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

      1000       9568 21-MAR-01

This query finds all customer orders containing the three part number/quantity pairs listed above.

Although it is useful to use the equality operator to look for an exact match, you are more likely to need only a partial match, especially if you are working with large collections. It might seem intuitive to use the IN operator to look for partial matches, as in:

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

FROM cust_order_c co

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

  IN co.order_items



no rows selected

When working with nested tables, however, you will need to use the SUBMULTISET operator instead of using IN. The next example uses SUBMULTISET to find all customer orders that include one quantity of part number GX5-2786-A2:

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

FROM cust_order_c co

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

  SUBMULTISET OF co.order_items;



ORDER_NBR   CUST_NBR ORDER_DT

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

      1000       9568 21-MAR-01

Since the collection in the preceding example contains a single object, you can instead use the MEMBER OF operator to check to see if the co.order_items collection contains a given instance of line_item_obj:

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

FROM cust_order_c co

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

  MEMBER OF co.order_items;



ORDER_NBR   CUST_NBR ORDER_DT

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

      1000       9568 21-MAR-01

Because MEMBER OF checks individual elements of a collection rather than the collection as a whole, you only need to instantiate a line_item_obj instead of a collection of line_item_obj types as you did when using SUBMULTISET.

You will need Oracle Database 10g to use the SUBMULTISET and MEMBER OF operators.