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>