MULTISET Operator : MULTISET « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. MULTISET

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>