IN and NOT IN Operators : IN « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. IN

You can use the IN and NOT IN operators to check if the contents of one nested table appear or don't appear in the contents of another nested table.

SQL>
SQL> CREATE OR REPLACE PROCEDURE in_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', 'B', 'C');
  9    myTable2 := nestedTableType('D', 'E', 'F');
 10    myTable3 := nestedTableType('A', 'B', 'C');
 11
 12    result := myTable3 IN (myTable1);
 13    IF result THEN
 14      DBMS_OUTPUT.PUT_LINE('myTable3 in myTable1');
 15    END IF;
 16
 17    result := myTable3 NOT IN (myTable2);
 18    IF result THEN
 19      DBMS_OUTPUT.PUT_LINE('myTable3 not in myTable2');
 20    END IF;
 21  END in_example;
 22  /

Procedure created.

SQL> CALL in_example();
myTable3 in myTable1
myTable3 not in myTable2

Call completed.

SQL>