Oracle PL/SQL Tutorial/Collections/SUBMULTISET

SUBMULTISET: is it a sub set

   <source lang="sql">

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(1,2,3,3,4,5);
 4    c LIST := list(1,2,3,3,4,4);
 5  BEGIN
 6    IF a SUBMULTISET c THEN
 7      dbms_output.put_line("[a] is a subset of [c]");
 8    END IF;
 9    IF NOT b SUBMULTISET c THEN
10      dbms_output.put_line("[b] is not a subset of [c]");
11    END IF;
12  END;
13  /

[a] is a subset of [c] [b] is not a subset of [c] PL/SQL procedure successfully completed. SQL></source>

SUBMULTISET Operator

SUBMULTISET operator checks whether the contents of one nested table are a subset of another nested table.

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE submultiset_example AS

 2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
 3    myTable1 nestedTableType;
 4    myTable2 nestedTableType;
 5    myTable3 nestedTableType;
 6    result BOOLEAN;
 7  BEGIN
 8    myTable1 := nestedTableType("A", "G", "S");
 9    myTable2 := nestedTableType("B", "F", "S");
10
11    result :=
12      myTable1 SUBMULTISET OF myTable2;
13    IF result THEN
14      DBMS_OUTPUT.PUT_LINE("myTable1 subset of myTable2");
15    END IF;
16  END submultiset_example;
17  /

Procedure created. SQL> CALL submultiset_example(); Call completed. SQL> SQL></source>

SUBMULTISET operator checks whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype

   <source lang="sql">

SQL> SQL> DECLARE

 2     TYPE list IS TABLE OF INTEGER;
 3     a LIST := list(1,2,3);
 4     b LIST := list(1,2,3,4);
 5   BEGIN
 6     IF a SUBMULTISET b THEN
 7       dbms_output.put_line("Subset.");
 8     END IF;
 9   END;
10   /

Subset. PL/SQL procedure successfully completed.</source>

SUBMULTISET table collection

   <source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE submultiset_example AS

 2    TYPE charTable IS TABLE OF VARCHAR2(10);
 3    emp1 charTable;
 4    emp2 charTable;
 5    emp3 charTable;
 6    result BOOLEAN;
 7  BEGIN
 8    emp1 := charTable("A", "B", "C");
 9    emp2 := charTable("C", "D", "E");
10
11    result := emp1 SUBMULTISET OF emp2;
12    IF result THEN
13      DBMS_OUTPUT.PUT_LINE("emp1 subset of emp2");
14    END IF;
15  END submultiset_example;
16  /

Procedure created. SQL> SQL></source>