Oracle PL/SQL Tutorial/Collections/MULTISET

MULTISET Operator

MULTISET operator gets a nested table whose elements are set to certain elements of two nested tables that are input to MULTISET. There are three MULTISET operators:

MULTISET UNION Returns a nested table whose elements are set to the elements of the two input nested tables.

MULTISET INTERSECT Returns a nested table whose elements are set to the elements that are common to the two input nested tables.

MULTISET EXCEPT Returns a nested table whose elements are in the first input nested table but not in the second.

You may also use one of the following options with MULTISET:

ALL Indicates that all applicable elements in the input nested tables are set in the returned nested table. ALL is the default.

DISTINCT Indicates that only the distinct non-duplicate elements in the input nested tables are set in the returned nested table.

SQL>
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
  2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
  3    myTable1 nestedTableType;
  4    myTable2 nestedTableType;
  5    myTable3 nestedTableType;
  6    count_var INTEGER;
  7  BEGIN
  8    myTable1 := nestedTableType("F", "G", "S");
  9    myTable2 := nestedTableType("G", "S", "R");
 10
 11    myTable3 := myTable1 MULTISET UNION myTable2;
 12    DBMS_OUTPUT.PUT("UNION: ");
 13    FOR count_var IN 1..myTable3.COUNT LOOP
 14      DBMS_OUTPUT.PUT(myTable3(count_var) || " ");
 15    END LOOP;
 16    DBMS_OUTPUT.PUT_LINE(" ");
 17
 18    myTable3 := myTable1 MULTISET UNION DISTINCT myTable2;
 19    DBMS_OUTPUT.PUT("UNION DISTINCT: ");
 20    FOR count_var IN 1..myTable3.COUNT LOOP
 21      DBMS_OUTPUT.PUT(myTable3(count_var) || " ");
 22    END LOOP;
 23    DBMS_OUTPUT.PUT_LINE(" ");
 24
 25    myTable3 := myTable1 MULTISET INTERSECT myTable2;
 26    DBMS_OUTPUT.PUT("INTERSECT: ");
 27    FOR count_var IN 1..myTable3.COUNT LOOP
 28      DBMS_OUTPUT.PUT(myTable3(count_var) || " ");
 29    END LOOP;
 30    DBMS_OUTPUT.PUT_LINE(" ");
 31
 32    myTable3 := myTable1 MULTISET EXCEPT myTable2;
 33    DBMS_OUTPUT.PUT_LINE("EXCEPT: ");
 34    FOR count_var IN 1..myTable3.COUNT LOOP
 35      DBMS_OUTPUT.PUT(myTable3(count_var) || " ");
 36    END LOOP;
 37  END multiset_example;
 38  /
Procedure created.
SQL> CALL multiset_example();
UNION: F G S G S R
UNION DISTINCT: F G S R
INTERSECT: G S
EXCEPT:
Call completed.
SQL>