About
The case statement is a Flow / Control Structure
PL/SQL provides two forms of case statement.
Syntax
Like any PL/SQL blocks, CASE statements can be labeled.
Equality
The CASE statement:
- begins with the keyword CASE.
- followed by a selector_expression
- followed by one or more WHEN clauses checked sequentially. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed. Execution never falls through; if any WHEN clause is executed, control passes to the next statement.
- followed by an optional ELSE clause. Default to ELSE RAISE CASE_NOT_FOUND;
- followed by the keywords END CASE that terminate the CASE statement.
[<<label_name>>]
CASE selector expression
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
...
WHEN expressionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
Searched (Boolean)
PL/SQL also provides a searched CASE statement.
The CASE statement:
- begins with the keyword CASE.
- followed by one or more WHEN clauses checked sequentially. If the value of the selector return true that WHEN clause is executed. If any WHEN clause is executed, control passes to the next statement, so subsequent selector are not evaluated.
- followed by an optional ELSE clause. Default to ELSE RAISE CASE_NOT_FOUND;
- followed by the keywords END CASE that terminate the CASE statement.
[<<label_name>>]
CASE
WHEN selector expression1 THEN sequence_of_statements1;
WHEN selector expression2 THEN sequence_of_statements2;
...
WHEN selector expressionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE [label_name];
Selector expression
The selector expression
- can be a variable or an arbitrarily complex expression such as function calls)
- is evaluated only once.
- value can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.