Using the MULTISET UNION operator v15
The MULTISET UNION
operator combines two collections to form a third collection. The signature is:
<coll_1> MULTISET UNION [ 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 behavior is the default.
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.
Combining collections
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.
Removing duplicate entries
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
- On this page
- Combining collections
- Removing duplicate entries