Oracle PL/SQL Tutorial/Collections/SET

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

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.

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