MULTISET UNION : MULTISET UNION « Set « Oracle PL/SQL Tutorial






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>








3.6.MULTISET UNION
3.6.1.MULTISET UNION
3.6.2.MULTISET UNION DISTINCT
3.6.3.MULTISET EXCEPT