Using the MULTISET operators v17
The MULTISET
operators combine two collections to form a third collection.
Syntax
<coll_1> MULTISET [ UNION | INTERSECT | EXCEPT ] [ ALL | DISTINCT | UNIQUE ] <coll_2>
Where coll_1
and coll_2
specify the names of the collections to combine.
Include the ALL
keyword to specify to represent duplicate elements (elements that are present in both coll_1
and coll_2
) in the result, once for each time they're present in the original collections. This is the default behavior.
Include the DISTINCT
or UNIQUE
keyword to include duplicate elements in the result only once. There is no difference between the DISTINCT
and UNIQUE
keywords.
There are three forms of MULTISET operators:
- MULTISET UNION
- MULTISET INTERSECT
- MULTISET EXCEPT
MULTISET UNION
MULTISET UNION
takes as arguments two nested tables and returns a nested table whose values from both the input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.
This example uses the MULTISET UNION
operator to combine collection_1
and collection_2
into a third collection, collection_3
:
DECLARE TYPE int_arr_typ IS TABLE OF NUMBER(2); collection_1 int_arr_typ; collection_2 int_arr_typ; collection_3 int_arr_typ; v_results VARCHAR2(50); BEGIN collection_1 := int_arr_typ(10,20,30); collection_2 := int_arr_typ(30,40); collection_3 := collection_1 MULTISET UNION ALL collection_2; DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT); FOR i IN collection_3.FIRST .. collection_3.LAST LOOP IF collection_3(i) IS NULL THEN v_results := v_results || 'NULL '; ELSE v_results := v_results || collection_3(i) || ' '; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Results: ' || v_results); END; COUNT: 5 Results: 10 20 30 30 40
The resulting collection includes one entry for each element in collection_1
and collection_2
. If you use the DISTINCT
keyword, the results are as follows:
DECLARE TYPE int_arr_typ IS TABLE OF NUMBER(2); collection_1 int_arr_typ; collection_2 int_arr_typ; collection_3 int_arr_typ; v_results VARCHAR2(50); BEGIN collection_1 := int_arr_typ(10,20,30); collection_2 := int_arr_typ(30,40); collection_3 := collection_1 MULTISET UNION DISTINCT collection_2; DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT); FOR i IN collection_3.FIRST .. collection_3.LAST LOOP IF collection_3(i) IS NULL THEN v_results := v_results || 'NULL '; ELSE v_results := v_results || collection_3(i) || ' '; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Results: ' || v_results); END; COUNT: 4 Results: 10 20 30 40
The resulting collection includes only those members with distinct values.
In this example, the MULTISET UNION DISTINCT
operator removes duplicate entries that are stored in the same collection:
DECLARE TYPE int_arr_typ IS TABLE OF NUMBER(2); collection_1 int_arr_typ; collection_2 int_arr_typ; collection_3 int_arr_typ; v_results VARCHAR2(50); BEGIN collection_1 := int_arr_typ(10,20,30,30); collection_2 := int_arr_typ(40,50); collection_3 := collection_1 MULTISET UNION DISTINCT collection_2; DBMS_OUTPUT.PUT_LINE('COUNT: ' || collection_3.COUNT); FOR i IN collection_3.FIRST .. collection_3.LAST LOOP IF collection_3(i) IS NULL THEN v_results := v_results || 'NULL '; ELSE v_results := v_results || collection_3(i) || ' '; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('Results: ' || v_results); END; COUNT: 5 Results: 10 20 30 40 50
MULTISET INTERSECT
MULTISET INTERSECT
takes as arguments two nested tables and returns a nested table whose values are common in the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.
This example uses the MULTISET INTERSECT
operator to combine color_name
and fruit_name
into a third collection, common_name
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Orange', 'Peach', 'Yellow', 'Peach'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach', 'Peach'); common_name := color_name MULTISET INTERSECT UNIQUE fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Orange Peach
This example shows the use of MULTISET INTERSECT DISTINCT
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Orange', 'Peach', 'Yellow', 'Peach'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach', 'Peach'); common_name := color_name MULTISET INTERSECT DISTINCT fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Orange Peach
This example shows the use of MULTISET INTERSECT ALL
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Orange', 'Peach', 'Yellow', 'Peach'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach', 'Peach'); common_name := color_name MULTISET INTERSECT ALL fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Orange Peach Peach
MULTISET EXCEPT
MULTISET EXCEPT
takes two nested tables as arguments and returns a nested table whose elements are in the first nested table but not in the second nested table. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.
This example shows the use of MULTISET EXCEPT UNIQUE
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Blue', 'Orange', 'Peach', 'Yellow'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach'); common_name := color_name MULTISET EXCEPT UNIQUE fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Blue Green Red Yellow
This example shows the use of MULTISET EXCEPT DISTINCT
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Blue', 'Orange', 'Peach', 'Yellow'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach'); common_name := color_name MULTISET EXCEPT DISTINCT fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Blue Green Red Yellow
This example shows the use of MULTISET EXCEPT ALL
:
DECLARE TYPE name_typ IS TABLE OF VARCHAR(50); color_name name_typ; fruit_name name_typ; common_name name_typ; BEGIN color_name := name_typ('Red', 'Green', 'Blue', 'Blue', 'Orange', 'Peach', 'Yellow'); fruit_name := name_typ('Mango', 'Orange', 'Grapes', 'Banana', 'Peach'); common_name := color_name MULTISET EXCEPT ALL fruit_name; FOR i IN common_name.FIRST .. common_name.LAST LOOP DBMS_OUTPUT.PUT_LINE(common_name(i)); END LOOP; END;
Red Green Blue Blue Yellow
- On this page
- Syntax
- MULTISET UNION