Oracle PL/SQL Tutorial/Collections/SUBMULTISET

SUBMULTISET: is it a sub set

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>

SUBMULTISET Operator

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

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>

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

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.

SUBMULTISET table collection

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>