Database Reference
In-Depth Information
ments are that the two nested tables must be of the same type, and thus
returning the same nested table type as well. All options default to ALL but
can return only DISTINCT values as well.
MULTISET EXCEPT returns exceptions in the first nested table and
not in the second, returning a nested table containing elements in the
first and not the second nested table.
<nested table> MULTISET EXCEPT [ DISTINCT |
ALL
]
<nested table>
For example, the following procedure will output elements in
nested table P1 but not in nested table P2, namely the string “one”:
DECLARE
TYPE PCOLL IS TABLE OF VARCHAR2(32);
P1 PCOLL := PCOLL('one','two','three');
P2 PCOLL := PCOLL('two','three','four');
P3 PCOLL;
BEGIN
P3 := P1 MULTISET EXCEPT P2;
FOR i IN P3.FIRST..P3.LAST LOOP
DBMS_OUTPUT.PUT_LINE(P3(i));
END LOOP;
END;
/
MULTISET INTERSECT returns the intersection of two nested
tables or, in other words, elements common to both.
<nested table> MULTISET INTERSECT [ DISTINCT |
ALL
]
<nested table>
MULTISET UNION returns all elements in both.
<nested table> MULTISET UNION [ DISTINCT |
ALL
]
<nested table>
