The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. The IS NOT NULL operator does the opposite.

Comparisons involving NULL values always yield NULL.

To test whether a value is NULL, use IF value IS NULL, as in these examples:

• Example 3-14, "Variable Initialized to NULL by Default"

• Example 3-34, "Procedure Prints BOOLEAN Variable"

• Example 3-55, "Searched CASE Expression with WHEN ... IS NULL"

This table summarizes the relational operators.

The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.

Case is significant.

The pattern can include the two wildcard characters underscore (_) and percent sign (%).

Underscore matches exactly one character.

Percent sign (%) matches zero or more characters.

To search for the percent sign or underscore, define an escape character and put it before the percent sign or underscore.

DECLARE
  PROCEDURE compare (
    value   VARCHAR2,
    pattern VARCHAR2
  ) IS
  BEGIN
    IF value LIKE pattern THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  compare('Johnson', 'J%s_n');
  compare('Johnson', 'J%S_N');
END;
/
 

TRUE
FALSE

DECLARE
  PROCEDURE half_off (sale_sign VARCHAR2) IS
  BEGIN
    IF sale_sign LIKE '50\% off!' ESCAPE '\' THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
    END IF;
  END;
BEGIN
  half_off('Going out of business!');
  half_off('50% off!');
END;
/
 

FALSE
TRUE

The BETWEEN operator tests whether a value lies in a specified range.

The value of the expression x BETWEEN a AND b is defined to be the same as the value of the expression (x>=a) AND (x<=b) . The expression x will only be evaluated once.

BEGIN
  print_boolean ('2 BETWEEN 1 AND 3', 2 BETWEEN 1 AND 3);
  print_boolean ('2 BETWEEN 2 AND 3', 2 BETWEEN 2 AND 3);
  print_boolean ('2 BETWEEN 1 AND 2', 2 BETWEEN 1 AND 2);
  print_boolean ('2 BETWEEN 3 AND 4', 2 BETWEEN 3 AND 4);
END;
/
 

2 BETWEEN 1 AND 3 = TRUE
2 BETWEEN 2 AND 3 = TRUE
2 BETWEEN 1 AND 2 = TRUE
2 BETWEEN 3 AND 4 = FALSE

The IN operator tests set membership.

x IN (set) returns TRUE only if x equals a member of set.

DECLARE
  letter VARCHAR2(1) := 'm';
BEGIN
  print_boolean (
    'letter IN (''a'', ''b'', ''c'')',
    letter IN ('a', 'b', 'c')
  );
  print_boolean (
    'letter IN (''z'', ''m'', ''y'', ''p'')',
    letter IN ('z', 'm', 'y', 'p')
  );
END;
/
 

letter IN ('a', 'b', 'c') = FALSE
letter IN ('z', 'm', 'y', 'p') = TRUE
 

DECLARE
  a INTEGER; -- Initialized to NULL by default
  b INTEGER := 10;
  c INTEGER := 100;
BEGIN
  print_boolean ('100 IN (a, b, c)', 100 IN (a, b, c));
  print_boolean ('100 NOT IN (a, b, c)', 100 NOT IN (a, b, c));
  
  print_boolean ('100 IN (a, b)', 100 IN (a, b));
  print_boolean ('100 NOT IN (a, b)', 100 NOT IN (a, b));
 
  print_boolean ('a IN (a, b)', a IN (a, b));
  print_boolean ('a NOT IN (a, b)', a NOT IN (a, b));
END;
/
 

100 IN (a, b, c) = TRUE
100 NOT IN (a, b, c) = FALSE
100 IN (a, b) = NULL
100 NOT IN (a, b) = NULL
a IN (a, b) = NULL
a NOT IN (a, b) = NULL

For this explanation, assume that a simple CASE expression has this syntax:

CASE selector
WHEN { selector_value_1a | dangling_predicate_1a }
  [ , ..., { selector_value_1n | dangling_predicate_1n } ] THEN result_1
WHEN { selector_value_2a | dangling_predicate_2a }
  [ , ..., { selector_value_2n | dangling_predicate_2n } ] THEN result_2
...
WHEN { selector_value_na | dangling_predicate_na }
  [ , ..., { selector_value_nn | dangling_predicate_nn }] THEN result_n
[ ELSE
  else_result ]
END;

The selector is an expression (typically a single variable). Each selector_value and each result can be either a literal or an expression. A dangling_predicate can also be used either instead of or in combination with one or multiple selector_values. At least one result must not be the literal NULL.

A dangling_predicate is an ordinary expression with its left operand missing, for example < 2. Using a dangling_predicate allows for more complicated comparisons that would otherwise require a searched CASE statement.

The simple CASE expression returns the first result for which the selector_value or dangling_predicate matches selector. Remaining expressions are not evaluated. If no selector_value or dangling_predicate matches selector, the CASE expression returns else_result if it exists and NULL otherwise.

A list of comma-separated selector_values and or dangling_predicates can be used with each WHEN clause if multiple choices map to a single result. As with selector_values and dangling_predicates listed in separate WHEN clauses, only the first selector_value or dangling_predicate to match the selector is evaluated.

DECLARE
  grade CHAR(1) := 'B';
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE grade
      WHEN 'A' THEN 'Excellent'
      WHEN 'B' THEN 'Very Good'
      WHEN 'C' THEN 'Good'
      WHEN 'D' THEN 'Fair'
      WHEN 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;
    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
 

Grade B is Very Good

DECLARE
  grade CHAR(1); -- NULL by default
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
  CASE grade
    WHEN NULL THEN 'No grade assigned'
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Very Good'
    WHEN 'C' THEN 'Good'
    WHEN 'D' THEN 'Fair'
    WHEN 'F' THEN 'Poor'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/

Grade  is No such grade

DECLARE
    salary NUMBER := 7000;
    salary_level VARCHAR2(20);
BEGIN
    salary_level :=
	CASE salary
		WHEN 1000, 2000 THEN 'low'
		WHEN 3000, 4000, 5000 THEN 'normal'
		WHEN 6000, 7000, 8000 THEN 'high'
		ELSE 'executive pay'
	END;
	DBMS_OUTPUT.PUT_LINE('Salary level is: ' || salary_level);
END;
/

Salary level is: high

DECLARE
    data_val NUMBER := 30;
    status VARCHAR2(20);
BEGIN
    status :=
	CASE data_val/2
		WHEN < 0, > 50 THEN 'outlier'
		WHEN BETWEEN 10 AND 30 THEN 'good'
		ELSE 'bad'
	END;
	DBMS_OUTPUT.PUT_LINE('The data status is: ' || status);
END;
/

The data status is: good

For this explanation, assume that a searched CASE expression has this syntax:

CASE
WHEN boolean_expression_1 THEN result_1
WHEN boolean_expression_2 THEN result_2
...
WHEN boolean_expression_n THEN result_n
[ ELSE
  else_result ]
END]

The searched CASE expression returns the first result for which boolean_expression is TRUE. Remaining expressions are not evaluated. If no boolean_expression is TRUE, the CASE expression returns else_result if it exists and NULL otherwise.

DECLARE
  grade      CHAR(1) := 'B';
  appraisal  VARCHAR2(120);
  id         NUMBER  := 8429862;
  attendance NUMBER := 150;
  min_days   CONSTANT NUMBER := 200;
  
  FUNCTION attends_this_school (id NUMBER)
    RETURN BOOLEAN IS
  BEGIN
    RETURN TRUE;
  END;
BEGIN
  appraisal :=
  CASE
    WHEN attends_this_school(id) = FALSE
      THEN 'Student not enrolled'
    WHEN grade = 'F' OR attendance < min_days
      THEN 'Poor (poor performance or bad attendance)'
    WHEN grade = 'A' THEN 'Excellent'
    WHEN grade = 'B' THEN 'Very Good'
    WHEN grade = 'C' THEN 'Good'
    WHEN grade = 'D' THEN 'Fair'
    ELSE 'No such grade'
  END;
  DBMS_OUTPUT.PUT_LINE
    ('Result for student ' || id || ' is ' || appraisal);
END;
/
 

Result for student 8429862 is Poor (poor performance or bad attendance)

DECLARE
  grade CHAR(1); -- NULL by default
  appraisal VARCHAR2(20);
BEGIN
  appraisal :=
    CASE
      WHEN grade IS NULL THEN 'No grade assigned'
      WHEN grade = 'A' THEN 'Excellent'
      WHEN grade = 'B' THEN 'Very Good'
      WHEN grade = 'C' THEN 'Good'
      WHEN grade = 'D' THEN 'Fair'
      WHEN grade = 'F' THEN 'Poor'
      ELSE 'No such grade'
    END;
    DBMS_OUTPUT.PUT_LINE ('Grade ' || grade || ' is ' || appraisal);
END;
/
 

Grade  is No grade assigned

PLS_INTEGER static expressions are:

• PLS_INTEGER literals

  For information about literals, see "Literals".

• PLS_INTEGER static constants

  For information about static constants, see "Static Constants".

• NULL

BOOLEAN static expressions are:

• BOOLEAN literals (TRUE, FALSE, or NULL)

• BOOLEAN static constants

  For information about static constants, see "Static Constants".

• Where x and y are PLS_INTEGER static expressions:

  • x > y

  • x < y

  • x >= y

  • x <= y

  • x = y

  • x <> y

  For information about PLS_INTEGER static expressions, see "PLS_INTEGER Static Expressions".

• Where x and y are BOOLEAN expressions:

  • NOT y

  • x AND y

  • x OR y

  • x > y

  • x >= y

  • x = y

  • x <= y

  • x <> y

  For information about BOOLEAN expressions, see "BOOLEAN Expressions".

• Where x is a static expression:

  • x IS NULL

  • x IS NOT NULL

  For information about static expressions, see "Static Expressions".

VARCHAR2 static expressions are:

• String literal with maximum size of 32,767 bytes

  For information about literals, see "Literals".

• NULL

• TO_CHAR(x), where x is a PLS_INTEGER static expression

  For information about the TO_CHAR function, see Oracle Database SQL Language Reference.

• TO_CHAR(x, f, n) where x is a PLS_INTEGER static expression and f and n are VARCHAR2 static expressions

  For information about the TO_CHAR function, see Oracle Database SQL Language Reference.

• x || y where x and y are VARCHAR2 or PLS_INTEGER static expressions

  For information about PLS_INTEGER static expressions, see "PLS_INTEGER Static Expressions".

A static constant is declared in a package specification with this syntax:

constant_name CONSTANT data_type := static_expression;

The type of static_expression must be the same as data_type (either BOOLEAN or PLS_INTEGER).

The static constant must always be referenced as package_name.constant_name, even in the body of the package_name package.

If you use constant_name in the BOOLEAN expression in a conditional compilation directive in a PL/SQL unit, then the PL/SQL unit depends on the package package_name. If you alter the package specification, the dependent PL/SQL unit might become invalid and need recompilation (for information about the invalidation of dependent objects, see Oracle Database Development Guide).

If you use a package with static constants to control conditional compilation in multiple PL/SQL units, Oracle recommends that you create only the package specification, and dedicate it exclusively to controlling conditional compilation. This practice minimizes invalidations caused by altering the package specification.

To control conditional compilation in a single PL/SQL unit, you can set flags in the PLSQL_CCFLAGS compilation parameter. For information about this parameter, see "Assigning Values to Inquiry Directives" and Oracle Database Reference.

CREATE PACKAGE my_debug IS
  debug CONSTANT BOOLEAN := TRUE;
  trace CONSTANT BOOLEAN := TRUE;
END my_debug;
/
 
CREATE PROCEDURE my_proc1 AUTHID DEFINER IS
BEGIN
  $IF my_debug.debug $THEN
    DBMS_OUTPUT.put_line('Debugging ON');
  $ELSE
    DBMS_OUTPUT.put_line('Debugging OFF');
  $END
END my_proc1;
/
 
CREATE PROCEDURE my_proc2 AUTHID DEFINER IS
BEGIN
  $IF my_debug.trace $THEN
    DBMS_OUTPUT.put_line('Tracing ON');
  $ELSE
    DBMS_OUTPUT.put_line('Tracing OFF');
  $END
END my_proc2;
/