CASE Statement

The simple CASE statement evaluates a single expression and compares it to several potential values or expressions.

The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE.

Topics

Syntax

simple_case_statement ::=

Description of the illustration simple_case_statement.eps

searched_case_statement ::=

Description of the illustration searched_case_statement.eps

(boolean_expression ::=, statement ::=)

Semantics

simple_case_statement

selector

Expression whose value is evaluated once and used to select one of several alternatives. selector can have any PL/SQL data type except BLOB, BFILE, or a user-defined type.

WHEN { selector_value | dangling_predicate }

[ , ..., { selector_value | dangling_predicate } ] THEN statement

selector_value can be an expression of any PL/SQL type except BLOB, BFILE, or a user-defined type.

The selector_values and dangling_predicates are evaluated sequentially. If the value of a selector_value equals the value of selector or a dangling_predicate is true, then the statement associated with that selector_value or dangling_predicate runs, and the CASE statement ends. Any subsequent selector_values and dangling_predicates are not evaluated.

Caution:

A statement can modify the database and invoke nondeterministic functions. There is no fall-through mechanism, as there is in the C switch statement.

Note:

Currently, the dangling predicates IS JSON and

IS
                    OF

are not supported.

ELSE statement [statement ]...

The statements run if and only if no selector_value has the same value as selector and no dangling_predicate is true.

Without the ELSE clause, if no selector_value has the same value as selector and no dangling_predicate is true, the system raises the predefined exception CASE_NOT_FOUND.

label

A label that identifies the statement (see "statement ::=" and "label").

searched_case_statement

WHEN boolean_expression THEN statement

The boolean_expressions are evaluated sequentially. If the value of a boolean_expression is TRUE, the statement associated with that boolean_expression runs, and the CASE statement ends. Subsequent boolean_expressions are not evaluated.

Caution:

A statement can modify the database and invoke nondeterministic functions. There is no fall-through mechanism, as there is in the C switch statement.

ELSE statement [statement ]...

The statements run if and only if no boolean_expression has the value TRUE.

Without the ELSE clause, if no boolean_expression has the value TRUE, the system raises the predefined exception CASE_NOT_FOUND.

label

A label that identifies the statement (see "statement ::=" and "label").

Examples

Example 5-6, "Simple CASE Statement"
Example 5-8, "Searched CASE Statement"