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
<source lang="sql">
the_value [NOT] IN (value1, value2, value3,...)</source>
- the_value is the value you are testing, and
- 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"
<source lang="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></source>
Use IN operator in PL/SQL
<source lang="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></source>
