Oracle PL/SQL Tutorial/Set/MULTISET UNION
MULTISET EXCEPT
<source lang="sql">
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.</source>
MULTISET UNION
<source lang="sql">
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></source>
MULTISET UNION DISTINCT
<source lang="sql">
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></source>