Oracle PL/SQL Tutorial/Set/MULTISET UNION

MULTISET EXCEPT

SQL> DECLARE
  2      TYPE nested_type IS TABLE OF NUMBER;
  3      nt1 nested_type := nested_type(1,2,3);
  4      nt2 nested_type := nested_type(3,2,1);
  5      nt3 nested_type := nested_type(2,3,1,3);
  6      nt4 nested_type := nested_type(1,2,4);
  7      answer nested_type;
  8      PROCEDURE show_answer (str IN VARCHAR2)
  9      IS
 10         l_row   PLS_INTEGER;
 11      BEGIN
 12         DBMS_OUTPUT.put_line (str);
 13         l_row := answer.FIRST;
 14
 15         WHILE (l_row IS NOT NULL)
 16         LOOP
 17            DBMS_OUTPUT.put_line (l_row || "=" || answer (l_row));
 18            l_row := answer.NEXT (l_row);
 19         END LOOP;
 20
 21         DBMS_OUTPUT.put_line ("");
 22      END show_answer;
 23  BEGIN
 24      answer := nt1 MULTISET UNION nt4;
 25      show_answer("nt1 MULTISET UNION nt4");
 26      answer := nt1 MULTISET UNION nt3;
 27      show_answer("nt1 MULTISET UNION nt3");
 28      answer := nt1 MULTISET UNION DISTINCT nt3;
 29      show_answer("nt1 MULTISET UNION DISTINCT nt3");
 30      answer := nt2 MULTISET INTERSECT nt3;
 31      show_answer("nt2 MULTISET INTERSECT nt3");
 32      answer := nt2 MULTISET INTERSECT DISTINCT nt3;
 33      show_answer("nt2 MULTISET INTERSECT DISTINCT nt3");
 34      answer := SET(nt3);
 35      show_answer("SET(nt3)");
 36      answer := nt3 MULTISET EXCEPT nt2;
 37      show_answer("nt3 MULTISET EXCEPT nt2");
 38      answer := nt3 MULTISET EXCEPT DISTINCT nt2;
 39      show_answer("nt3 MULTISET EXCEPT DISTINCT nt2");
 40  END;
 41  /
PL/SQL procedure successfully completed.

MULTISET UNION

SQL>
SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
  2  /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employees_pkg
  2  IS
  3     vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
  4     newyork_employees   strings_nt := strings_nt ("H", "S", "A");
  5     boston_employees    strings_nt := strings_nt ("S", "D");
  6
  7     PROCEDURE show_employees (title_in IN VARCHAR2,employees_in IN strings_nt);
  8  END;
  9  /
Package created.
SQL> SHO ERR
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
  2  IS
  3     PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt)
  4     IS
  5     BEGIN
  6        DBMS_OUTPUT.put_line (title_in);
  7
  8        FOR indx IN employees_in.FIRST .. employees_in.LAST
  9        LOOP
 10           DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx));
 11        END LOOP;
 12
 13     END show_employees;
 14  END;
 15  /
Package body created.
SQL> SHOw error
No errors.
SQL>
SQL>
SQL> DECLARE
  2     our_employees strings_nt := strings_nt();
  3  BEGIN
  4      our_employees := employees_pkg.vancouver_employees
  5                     MULTISET UNION employees_pkg.newyork_employees;
  6
  7      employees_pkg.show_employees ("MINE then VEVA", our_employees);
  8
  9      our_employees := employees_pkg.newyork_employees
 10                     MULTISET UNION employees_pkg.vancouver_employees;
 11
 12      employees_pkg.show_employees ("VEVA then MINE", our_employees);
 13
 14      our_employees := employees_pkg.vancouver_employees
 15                     MULTISET UNION DISTINCT employees_pkg.newyork_employees;
 16
 17      employees_pkg.show_employees ("MINE then VEVA with DISTINCT", our_employees);
 18
 19      our_employees := employees_pkg.vancouver_employees
 20                     MULTISET INTERSECT employees_pkg.newyork_employees;
 21
 22      employees_pkg.show_employees ("IN COMMON", our_employees);
 23
 24      our_employees := employees_pkg.newyork_employees
 25                     MULTISET EXCEPT employees_pkg.vancouver_employees;
 26
 27      employees_pkg.show_employees (q"[ONLY VEVA"S]", our_employees);
 28  END;
 29  /
PL/SQL procedure successfully completed.
SQL>
SQL>

MULTISET UNION DISTINCT

SQL>
SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
  2  /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employees_pkg
  2  IS
  3     vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
  4     newyork_employees   strings_nt := strings_nt ("H", "S", "A");
  5     boston_employees    strings_nt := strings_nt ("S", "D");
  6
  7     PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt);
  8  END;
  9  /
Package created.
SQL> SHO ERR
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
  2  IS
  3     PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt)
  4     IS
  5     BEGIN
  6        DBMS_OUTPUT.put_line (title_in);
  7
  8        FOR indx IN employees_in.FIRST .. employees_in.LAST
  9        LOOP
 10           DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx));
 11        END LOOP;
 12
 13     END show_employees;
 14  END;
 15  /
Package body created.
SQL>
SQL>
SQL> SHOw error
No errors.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     our_favorites strings_nt := strings_nt ();
  3  BEGIN
  4     our_favorites := employees_pkg.newyork_employees
  5         MULTISET UNION DISTINCT
  6        employees_pkg.vancouver_employees;
  7
  8     employees_pkg.show_employees("DISTINCT", our_favorites);
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>