Oracle PL/SQL/PL SQL/MULTISET

MULTISET EXCEPT operator finds the elements remaining from the first set after removing any matching elements from the second set

   <source lang="sql">

SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS

 2    returnValue VARCHAR2(2000);
 3  BEGIN
 4      FOR i IN set_in.FIRST..set_in.LAST LOOP
 5          returnValue := set_in(i)||" ";
 6      END LOOP;
 7      RETURN returnValue;
 8  END format_list;
 9  /

Function created. SQL> SQL> DECLARE

 2    a LIST := list(1,2,3,4);
 3    b LIST := list(4,5,6,7);
 4  BEGIN
 5    dbms_output.put_line(format_list(a MULTISET EXCEPT b));
 6  END;
 7  /

3 PL/SQL procedure successfully completed.

 </source>
   
  

MULTISET EXCEPT table collection

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE multiset_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    count_var INTEGER;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("D", "E", "F");
10
11
12    emp3 := emp1 MULTISET EXCEPT emp2;
13    DBMS_OUTPUT.PUT_LINE("EXCEPT: ");
14    FOR count_var IN 1..emp3.COUNT LOOP
15      DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16    END LOOP;
17
18  END multiset_example;
19  /

Procedure created. SQL> SQL>

 </source>
   
  

MULTISET INTERSECT operator finds the intersection or matching values between two sets

   <source lang="sql">

SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS

 2    returnValue VARCHAR2(2000);
 3  BEGIN
 4      FOR i IN set_in.FIRST..set_in.LAST LOOP
 5            returnValue := set_in(i)||" ";
 6      END LOOP;
 7      RETURN returnValue;
 8  END format_list;
 9  /

Function created. SQL> SQL> DECLARE

 2    a LIST := list(1,2,3,4);
 3    b LIST := list(4,5,6,7);
 4  BEGIN
 5    dbms_output.put_line(format_list(a MULTISET INTERSECT b));
 6  END;
 7  /

4 PL/SQL procedure successfully completed. SQL>

 </source>
   
  

MULTISET INTERSECT table collection

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE multiset_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    count_var INTEGER;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("D", "E", "F");
10
11
12    emp3 := emp1 MULTISET INTERSECT emp2;
13    DBMS_OUTPUT.PUT("INTERSECT: ");
14    FOR count_var IN 1..emp3.COUNT LOOP
15      DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16    END LOOP;
17    DBMS_OUTPUT.PUT_LINE(" ");
18
19  END multiset_example;
20  /

Procedure created. SQL>

 </source>
   
  

MULTISET UNION DISTINCT table collection

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE multiset_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    count_var INTEGER;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("D", "E", "F");
10
11
12    emp3 := emp1 MULTISET UNION DISTINCT emp2;
13    DBMS_OUTPUT.PUT("UNION DISTINCT: ");
14    FOR count_var IN 1..emp3.COUNT LOOP
15      DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16    END LOOP;
17    DBMS_OUTPUT.PUT_LINE(" ");
18
19  END multiset_example;
20  /

Procedure created. SQL>

 </source>
   
  

MULTISET UNION operator performs a UNION ALL operation on two collections

   <source lang="sql">

SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;

 2  /

Type created. SQL> SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS

 2    returnValue VARCHAR2(2000);
 3  BEGIN
 4      FOR i IN set_in.FIRST..set_in.LAST LOOP
 5        returnValue := set_in(i)||" ";
 6      END LOOP;
 7      RETURN returnValue;
 8  END format_list;
 9  /

Function created. SQL> SQL> DECLARE

 2    a LIST := list(1,2,3,4);
 3    b LIST := list(4,5,6,7);
 4  BEGIN
 5    dbms_output.put_line(format_list(a MULTISET UNION b));
 6  END;
 7  /

7 PL/SQL procedure successfully completed. SQL> SQL>

 </source>
   
  

MULTISET UNION table collection

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE multiset_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    count_var INTEGER;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("D", "E", "F");
10
11    emp3 := emp1 MULTISET UNION emp2;
12    DBMS_OUTPUT.PUT("UNION: ");
13    FOR count_var IN 1..emp3.COUNT LOOP
14      DBMS_OUTPUT.PUT(emp3(count_var) || " ");
15    END LOOP;
16    DBMS_OUTPUT.PUT_LINE(" ");
17
18
19  END multiset_example;
20  /

Procedure created.

 </source>