CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The
CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is
searched case statement ::=
simple case statement ::=
The value of the
CASE operand and
WHEN operands in a simple
CASE statement can be any PL/SQL type other than
BFILE, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.
ELSE clause is omitted, the system substitutes a default action. For a
CASE statement, the default when none of the conditions matches is to raise a
CASE_NOT_FOUND exception. For a
CASE expression, the default is to return
WHEN clauses are executed in order. Each
WHEN clause is executed only once. After a matching
WHEN clause is found, subsequent
WHEN clauses are not executed. You can use multiple statements after a
WHEN clause, and that the expression in the
WHEN clause can be a literal, variable, function call, or any other kind of expression. The
WHEN clauses can use different conditions rather than all testing the same variable or using the same operator.
The statements in a
WHEN clause can modify the database and call non-deterministic functions. There is no fall-through mechanism as in the C
switch statement. Once a
WHEN clause is matched and its statements are executed, the
CASE statement ends.
CASE statement is appropriate when there is some different action to be taken for each alternative. If you just need to choose among several values to assign to a variable, you can code an assignment statement using a
CASE expression instead.
You can include
CASE expressions inside SQL queries, for example instead of a call to the
DECODE function or some other function that translates from one value to another.
Example 13-2 shows the use of a simple CASE statement.
DECLARE jobid employees.job_id%TYPE; empid employees.employee_id%TYPE := 115; sal_raise NUMBER(3,2); BEGIN SELECT job_id INTO jobid from employees WHERE employee_id = empid; CASE WHEN jobid = 'PU_CLERK' THEN sal_raise := .09; WHEN jobid = 'SH_CLERK' THEN sal_raise := .08; WHEN jobid = 'ST_CLERK' THEN sal_raise := .07; ELSE sal_raise := 0; END CASE; END; /
For examples, see the following:
COALESCEfunctions in Oracle Database SQL Reference