CASE Statement

The CASE statement selects a sequence of statements to execute. To select the sequence, the CASE statement uses a selector (an expression whose value is used to select one of several alternatives) or, in the searched CASE statement, multiple search conditions.

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

Each WHEN clause is executed only once.

The WHEN clauses are executed in order.

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

Because the execution order of the WHEN clauses is defined by the preceding rules, the statements in a WHEN clause are allowed to 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.

Examples

The following example shows a simple CASE statement. Notice that you can use multiple statements after a WHEN clause.

DECLARE
  n number;
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');
    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;
  projected NUMBER;
  needed NUMBER;
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 must be less than 0.')
END;

Related Topics

"Conditional Control: IF and CASE Statements", "CASE Expressions", NULLIF and COALESCE expressions in Oracle9i SQL Reference