Oracle PL/SQL Tutorial/Collections/SET

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

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.

IS A SET, table collection

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.

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

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.

MULTISET EXCEPT table collection

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>

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

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>

MULTISET INTERSECT table collection

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>

MULTISET UNION DISTINCT table collection

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>

MULTISET UNION operator performs a UNION ALL operation on two collections

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>

MULTISET UNION table collection

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.

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.

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>

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

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.

SET(table collection)

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.