Oracle PL/SQL/PL SQL/IN

IN/NOT IN example(table collection)

   
SQL> CREATE OR REPLACE PROCEDURE in_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    emp3 := charTable("A", "B", "C");
 11
 12    result := emp3 IN (emp1);
 13    IF result THEN
 14      DBMS_OUTPUT.PUT_LINE("emp3 in emp1");
 15    END IF;
 16
 17
 18  END in_example;
 19  /
Procedure created.
SQL>

IN operator checks whether a variable value is in a set of comma-delimited values.

   
SQL>
SQL>  BEGIN
  2    IF 1 IN (1,2,3) THEN
  3     dbms_output.put_line("In the set.");
  4    END IF;
  5   END;
  6   /
In the set.
PL/SQL procedure successfully completed.

IN operator in PL SQL

   
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> DECLARE
  2     lv_test_bln BOOLEAN;
  3  BEGIN
  4     lv_test_bln := UPPER("AAA") IN ("TRE","B","N");
  5     IF lv_test_bln THEN
  6        DBMS_OUTPUT.PUT_LINE("Result: TRUE");
  7     ELSE
  8        DBMS_OUTPUT.PUT_LINE("Result: FALSE");
  9     END IF;
 10  END;
 11  /
Result: FALSE
PL/SQL procedure successfully completed.

NOT IN example (table collection)

    
SQL> CREATE OR REPLACE PROCEDURE in_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    emp3 := charTable("A", "B", "C");
 11
 12
 13
 14    result := emp3 NOT IN (emp2);
 15    IF result THEN
 16      DBMS_OUTPUT.PUT_LINE("emp3 not in emp2");
 17    END IF;
 18  END in_example;
 19  /
Procedure created.
SQL>

Use IN operator in procedure

  

SQL>
SQL>
SQL>
SQL> -The IN operator used to test for long weekends.
SP2-0734: unknown command beginning "-The IN op..." - rest of line ignored.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     test_date     DATE;
  3     day_of_week   VARCHAR2(3);
  4     years_ahead   INTEGER;
  5  BEGIN
  6     --Assign a date value to test_date.
  7
  8     test_date := TO_DATE("4-Jul-1997","dd-mon-yyyy");
  9
 10     FOR years_ahead IN 1..10 LOOP
 11       day_of_week := TO_CHAR(test_date,"Dy");
 12
 13      IF day_of_week IN ("Mon","Fri","Sat","Sun") THEN
 14          DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| "     A long weekend!");
 15       ELSE
 16           DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
 17       END IF;
 18       --Advance one year (12 months)
 19       test_date := ADD_MONTHS(test_date,12);
 20    END LOOP;
 21  END;
 22  /
04-Jul-1997     A long weekend!
04-Jul-1998     A long weekend!
04-Jul-1999     A long weekend!
04-Jul-2000 Not a long weekend.
04-Jul-2001 Not a long weekend.
04-Jul-2002 Not a long weekend.
04-Jul-2003     A long weekend!
04-Jul-2004     A long weekend!
04-Jul-2005     A long weekend!
04-Jul-2006 Not a long weekend.
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>