CASE Statement

The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.

Syntax

searched_case_statement ::=

[ <<label_name>> ]
CASE { WHEN boolean_expression THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];

simple_case_statement ::=

[ <<label_name>> ]
CASE case_operand
{ WHEN when_operand THEN {statement;} ... }...
[ ELSE {statement;}... ]
END CASE [ label_name ];

Keyword and Parameter Description

The value of the CASE operand and WHEN operands in a simple CASE statement can be any PL/SQL type other than BLOB, BFILE, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.

If the ELSE clause is omitted, the system substitutes a default action. For a CASE statement, the default when none of the conditions matches is to raise a CASE_NOT_FOUND exception. For a CASE expression, the default is to return NULL.

Usage Notes

The WHEN clauses are executed in order.

Each WHEN clause is executed only once.

After a matching WHEN clause is found, subsequent WHEN clauses are not executed.

The statements in a WHEN clause can modify the database and call non-deterministic functions.

There is no "fall-through" as in the C switch statement. Once a WHEN clause is matched and its statements are executed, the CASE statement ends.

The CASE statement is appropriate when there is some different action to be taken for each alternative. If you just need to choose among several values to assign to a variable, you can code an assignment statement using a CASE expression instead.

You can include CASE expressions inside SQL queries, for example instead of a call to the DECODE function or some other function that translates from one value to another.

Examples

The following example shows a simple CASE statement. Notice that you can use multiple statements after a WHEN clause, and that the expression in the WHEN clause can be a literal, variable, function call, or any other kind of expression.

DECLARE
  n number := 2;
BEGIN
  CASE n
    WHEN 1 THEN dbms_output.put_line('n = 1');
    WHEN 2 THEN
      dbms_output.put_line('n = 2');
      dbms_output.put_line('That implies n > 1');
    WHEN 2+2 THEN
      dbms_output.put_line('n = 4');
    ELSE dbms_output.put_line('n is some other value.');
  END CASE;
END;
/

The following example shows a searched CASE statement. Notice that the WHEN clauses can use different conditions rather than all testing the same variable or using the same operator. Because this example does not use an ELSE clause, an exception is raised if none of the WHEN conditions are met.

DECLARE
  quantity NUMBER := 100;
  projected NUMBER := 30;
  needed NUMBER := 999;
BEGIN
<<here>>
  CASE
    WHEN quantity is null THEN
      dbms_output.put_line('Quantity not available');
    WHEN quantity + projected >= needed THEN
      dbms_output.put_line('Quantity ' || quantity ||
        ' should be enough if projections are met.');
    WHEN quantity >= 0 THEN
      dbms_output.put_line('Quantity ' || quantity || ' is probably not enough.');
  END CASE here;
  EXCEPTION
    WHEN CASE_NOT_FOUND THEN
      dbms_output.put_line('Somehow quantity is less than 0.');
END;
/

Related Topics

"Testing Conditions: IF and CASE Statements", CASE Expressions, NULLIF and COALESCE expressions in Oracle Database SQL Reference