Using the MULTISET operators v16

Suggest edits

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;
Output
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;
Output
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;
Output
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;
Output
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;
Output
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;
Output
Red
Green
Blue
Blue
Yellow
← Prev

Using the TABLE function

Next →

Using the FORALL statement