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>