в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:10Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Set/MULTISET UNION - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия