CASE Statement

The CASE statement chooses from a sequence of conditions and runs a corresponding statement.

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

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

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

simple_case_statement ::=

searched_case_statement ::=

See:

• boolean_expression ::=

• statement ::=

Semantics

label

An undeclared identifier. If the CASE statement has multiple labels, they must be unique for that statement.

selector

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

WHEN { selector_value | boolean_expression } THEN statement

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

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

ELSE statement [statement ]...

In the simple CASE statement, the statements after ELSE run if and only if no selector_value has the same value as selector.

In the searched CASE statement, the statements after ELSE run if and only if no boolean_expression has the value TRUE.

Without the ELSE clause, if no selector_value has the same value as selector or no boolean_expression has the value TRUE, the system raises a CASE_NOT_FOUND exception.

Examples

• Example 4-6, "Simple CASE Statement"

• Example 4-7, "Searched CASE Statement"

Related Topics

In this chapter:

• "IF Statement"

In other chapters:

• "CASE Expressions"

• "Conditional Selection Statements"

• "Simple CASE Statement"

• "Searched CASE Statement"