PL/SQL - Case Statement

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];