Oracle PL/SQL Tutorial/PL SQL Operators/IN

IN

The IN operator checks to see if a value is contained in a specified list of values.

A true result is returned if the value is contained in the list;

otherwise, the expression evaluates to false.

The Syntax for IN

the_value [NOT] IN (value1, value2, value3,...)

1. the_value is the value you are testing, and
2. value1, value2, value3,... represents a list of comma-delimited values.

Expression Result 3 IN (0,1,2,3,) true "Sun" IN ("Mon","Tue") false "Sun" IN ("Sat","Sun") true 3 NOT IN (0,1,2,3) false

Use if with "IN"

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    test_date     DATE;
  3    day_of_week   VARCHAR2(3);
  4    years_ahead   INTEGER;
  5  BEGIN
  6    test_date := TO_DATE("1-Jan-1997","dd-mon-yyyy");
  7
  8    FOR years_ahead IN 1..10 LOOP
  9      day_of_week := TO_CHAR(test_date,"Dy");
 10
 11      IF day_of_week IN ("Sat","Sun") THEN
 12        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| "     A long weekend!");
 13      ELSE
 14        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
 15      END IF;
 16      test_date := ADD_MONTHS(test_date,12);
 17    END LOOP;
 18  END;
 19  /
01-Jan-1997 Not a long weekend.
01-Jan-1998 Not a long weekend.
01-Jan-1999 Not a long weekend.
01-Jan-2000     A long weekend!
01-Jan-2001 Not a long weekend.
01-Jan-2002 Not a long weekend.
01-Jan-2003 Not a long weekend.
01-Jan-2004 Not a long weekend.
01-Jan-2005     A long weekend!
01-Jan-2006     A long weekend!
PL/SQL procedure successfully completed.
SQL>
SQL>

Use IN operator in PL/SQL

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    test_date     DATE;
  3    day_of_week   VARCHAR2(3);
  4    years_ahead   INTEGER;
  5  BEGIN
  6    test_date := TO_DATE("1-Jan-1997","dd-mon-yyyy");
  7
  8    FOR years_ahead IN 1..10 LOOP
  9      day_of_week := TO_CHAR(test_date,"Dy");
 10
 11      IF day_of_week IN ("Sat","Sun") THEN
 12        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| "     A long weekend!");
 13      ELSE
 14        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
 15      END IF;
 16      test_date := ADD_MONTHS(test_date,12);
 17    END LOOP;
 18  END;
 19  /
01-Jan-1997 Not a long weekend.
01-Jan-1998 Not a long weekend.
01-Jan-1999 Not a long weekend.
01-Jan-2000     A long weekend!
01-Jan-2001 Not a long weekend.
01-Jan-2002 Not a long weekend.
01-Jan-2003 Not a long weekend.
01-Jan-2004 Not a long weekend.
01-Jan-2005     A long weekend!
01-Jan-2006     A long weekend!
PL/SQL procedure successfully completed.
SQL>
SQL>