Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
The 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 TRUE
.
Syntax
searched case statement ::=
simple case statement ::=
Keyword and Parameter Description
The value of the CASE
operand and WHEN
operands in a simple CASE
statement can be any PL/SQL type other than BLOB
, BFILE
, an object type, a PL/SQL record, an index-by table, a varray, or a nested table.
If the 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 NULL
.
Usage Notes
The 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.
The 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.
Example 13-2 Using a 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:
Related Topics
NULLIF
and COALESCE
functions in Oracle Database SQL Reference