Oracle PL/SQL Tutorial/Set/MULTISET UNION

Материал из SQL эксперт
Перейти к: навигация, поиск

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>