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 or expressions.
The searched CASE
statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE
.
Topics
Syntax
simple_case_statement ::=
searched_case_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 predicatesIS JSON
and IS
OF
are not supported.
ELSE statement [statement ]...
The statement
s 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_expression
s 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_expression
s 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 statement
s 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"
Related Topics
In this chapter:
In other chapters:
See Also:
-
Oracle Database SQL Language Reference for information about the
NULLIF
function -
Oracle Database SQL Language Reference for information about the
COALESCE
function