PL/SQL - Case Statement

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

The case statement is a Flow / Control Structure

PL/SQL provides two forms of case statement.

3 - Syntax

Like any PL/SQL blocks, CASE statements can be labeled.

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

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

4 - 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.

5 - Documentation / Reference

Advertising
lang/plsql/case.txt · Last modified: 2017/09/06 22:18 by gerardnico