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.

   <source lang="sql">

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></source>