в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:27Z

1 версия

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

== IS A SET operator checks whether a variable is a VARRAY or NESTED TABLE collection variable==



<source lang="sql">
SQL>
SQL> DECLARE
2 TYPE list IS TABLE OF INTEGER;
3 a LIST := list();
4 BEGIN
5 IF a IS A SET THEN
6 dbms_output.put_line(""a" is a set.");
7 END IF;
8 END;
9 /
"a" is a set.
PL/SQL procedure successfully completed.</source>

== IS A SET, table collection==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE is_a_set_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 result BOOLEAN;
5 BEGIN
6 emp1 := charTable("A", "B", "C", "D");
7 result := emp1 IS A SET;
8 IF result THEN
9 DBMS_OUTPUT.PUT_LINE("Elements are all unique");
10 ELSE
11 DBMS_OUTPUT.PUT_LINE("Elements contain duplicates");
12 END IF;
13 END is_a_set_example;
14 /
Procedure created.</source>

== MULTISET EXCEPT operator finds the elements remaining from the first set after removing any matching elements from the second set==



<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000);
3 BEGIN
4 FOR i IN set_in.FIRST..set_in.LAST LOOP
5 returnValue := set_in(i)||" ";
6 END LOOP;
7 RETURN returnValue;
8 END format_list;
9 /
Function created.
SQL>
SQL> DECLARE
2 a LIST := list(1,2,3,4);
3 b LIST := list(4,5,6,7);
4 BEGIN
5 dbms_output.put_line(format_list(a MULTISET EXCEPT b));
6 END;
7 /
3
PL/SQL procedure successfully completed.</source>

== MULTISET EXCEPT table collection==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 count_var INTEGER;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("D", "E", "F");
10
11
12 emp3 := emp1 MULTISET EXCEPT emp2;
13 DBMS_OUTPUT.PUT_LINE("EXCEPT: ");
14 FOR count_var IN 1..emp3.COUNT LOOP
15 DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16 END LOOP;
17
18 END multiset_example;
19 /
Procedure created.
SQL>
SQL></source>

== MULTISET INTERSECT operator finds the intersection or matching values between two sets==



<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000);
3 BEGIN
4 FOR i IN set_in.FIRST..set_in.LAST LOOP
5 returnValue := set_in(i)||" ";
6 END LOOP;
7 RETURN returnValue;
8 END format_list;
9 /
Function created.
SQL>
SQL> DECLARE
2 a LIST := list(1,2,3,4);
3 b LIST := list(4,5,6,7);
4 BEGIN
5 dbms_output.put_line(format_list(a MULTISET INTERSECT b));
6 END;
7 /
4
PL/SQL procedure successfully completed.
SQL></source>

== MULTISET INTERSECT table collection==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 count_var INTEGER;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("D", "E", "F");
10
11
12 emp3 := emp1 MULTISET INTERSECT emp2;
13 DBMS_OUTPUT.PUT("INTERSECT: ");
14 FOR count_var IN 1..emp3.COUNT LOOP
15 DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16 END LOOP;
17 DBMS_OUTPUT.PUT_LINE(" ");
18
19 END multiset_example;
20 /
Procedure created.
SQL></source>

== MULTISET UNION DISTINCT table collection==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 count_var INTEGER;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("D", "E", "F");
10
11
12 emp3 := emp1 MULTISET UNION DISTINCT emp2;
13 DBMS_OUTPUT.PUT("UNION DISTINCT: ");
14 FOR count_var IN 1..emp3.COUNT LOOP
15 DBMS_OUTPUT.PUT(emp3(count_var) || " ");
16 END LOOP;
17 DBMS_OUTPUT.PUT_LINE(" ");
18
19 END multiset_example;
20 /
Procedure created.
SQL></source>

== MULTISET UNION operator performs a UNION ALL operation on two collections==



<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000);
3 BEGIN
4 FOR i IN set_in.FIRST..set_in.LAST LOOP
5 returnValue := set_in(i)||" ";
6 END LOOP;
7 RETURN returnValue;
8 END format_list;
9 /
Function created.
SQL>
SQL> DECLARE
2 a LIST := list(1,2,3,4);
3 b LIST := list(4,5,6,7);
4 BEGIN
5 dbms_output.put_line(format_list(a MULTISET UNION b));
6 END;
7 /
7
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== MULTISET UNION table collection==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE multiset_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 emp3 charTable;
6 count_var INTEGER;
7 BEGIN
8 emp1 := charTable("A", "B", "C");
9 emp2 := charTable("D", "E", "F");
10
11 emp3 := emp1 MULTISET UNION emp2;
12 DBMS_OUTPUT.PUT("UNION: ");
13 FOR count_var IN 1..emp3.COUNT LOOP
14 DBMS_OUTPUT.PUT(emp3(count_var) || " ");
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE(" ");
17
18
19 END multiset_example;
20 /
Procedure created.</source>

== SET Operator==

<p>The SET operator first converts a nested table into a set, removes duplicate elements from the set, and returns the set as a nested table.</p>



<source lang="sql">
SQL>
SQL> CREATE OR REPLACE PROCEDURE set_example AS
2 TYPE nestedTableType IS TABLE OF VARCHAR2(10);
3 myTable1 nestedTableType;
4 myTable2 nestedTableType;
5 count_var INTEGER;
6 BEGIN
7 myTable1 := nestedTableType("F", "G", "S", "G");
8 myTable2 := SET(myTable1);
9 DBMS_OUTPUT.PUT("myTable2: ");
10 FOR count_var IN 1..myTable2.COUNT LOOP
11 DBMS_OUTPUT.PUT(myTable2(count_var) || " ");
12 END LOOP;
13 DBMS_OUTPUT.PUT_LINE(" ");
14 END set_example;
15 /
Procedure created.
SQL> CALL set_example();
myTable2: F G S
Call completed.
SQL>
SQL></source>

== SET operator removes any duplicates from the set and returns a new set with unique values==



<source lang="sql">
SQL> CREATE OR REPLACE TYPE list IS TABLE OF NUMBER;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION format_list(set_in LIST) RETURN VARCHAR2 IS
2 returnValue VARCHAR2(2000);
3 BEGIN
4 FOR i IN set_in.FIRST..set_in.LAST LOOP
5 returnValue := set_in(i)||" ";
6 END LOOP;
7 RETURN returnValue;
8 END format_list;
9 /
Function created.
SQL>
SQL> DECLARE
2 a LIST := list(1,2,3,3,4,4,5,6,6,7);
3 BEGIN
4 dbms_output.put_line(format_list(SET(a)));
5 END;
6 /
7
PL/SQL procedure successfully completed.</source>

== SET(table collection)==



<source lang="sql">
SQL> CREATE OR REPLACE PROCEDURE set_example AS
2 TYPE charTable IS TABLE OF VARCHAR2(10);
3 emp1 charTable;
4 emp2 charTable;
5 count_var INTEGER;
6 BEGIN
7 emp1 := charTable("A", "B", "C", "S");
8 emp2 := SET(emp1);
9 DBMS_OUTPUT.PUT("emp2: ");
10 FOR count_var IN 1..emp2.COUNT LOOP
11 DBMS_OUTPUT.PUT(emp2(count_var) || " ");
12 END LOOP;
13 DBMS_OUTPUT.PUT_LINE(" ");
14 END set_example;
15 /
Procedure created.</source>

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

Oracle PL/SQL Tutorial/Collections/SET - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия