Mega Code Archive
MULTISET Operator
<![CDATA[
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>
]]>