PL/SQL User's Guide and Reference Release 2 (9.2) Part Number A96624-01 |
|
PL/SQL Language Elements, 5 of 52
The CASE
statement selects a sequence of statements to execute. To select the sequence, the CASE
statement uses a selector (an expression whose value is used to select one of several alternatives) or, in the searched CASE
statement, multiple search conditions.
searched_case_statement ::=
[ <<label_name>> ] CASE { WHEN boolean_expression THEN {statement;} ... }... [ ELSE {statement;}... ] END CASE [ label_name ];
simple_case_statement ::=
[ <<label_name>> ] CASE case_operand { WHEN when_operand THEN {statement;} ... }... [ ELSE {statement;}... ] END CASE [ label_name ];
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
.
Each WHEN
clause is executed only once.
The WHEN
clauses are executed in order.
After a matching WHEN
clause is found, subsequent WHEN
clauses are not executed.
Because the execution order of the WHEN
clauses is defined by the preceding rules, the statements in a WHEN
clause are allowed to modify the database and call non-deterministic functions.
There is no "fall-through" 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.
The following example shows a simple CASE
statement. Notice that you can use multiple statements after a WHEN
clause.
DECLARE n number; BEGIN CASE n WHEN 1 THEN dbms_output.put_line('n = 1'); WHEN 2 THEN dbms_output.put_line('n = 2'); dbms_output.put_line('That implies n > 1'); ELSE dbms_output.put_line('n is some other value.'); END CASE; END;
The following example shows a searched CASE
statement. Notice that the WHEN
clauses can use different conditions rather than all testing the same variable or using the same operator. Because this example does not use an ELSE
clause, an exception is raised if none of the WHEN
conditions are met.
DECLARE quantity NUMBER; projected NUMBER; needed NUMBER; BEGIN <<here>> CASE WHEN quantity is null THEN dbms_output.put_line('Quantity not available'); WHEN quantity + projected >= needed THEN dbms_output.put_line('Quantity ' || quantity || ' should be enough if projections are met.'); WHEN quantity >= 0 THEN dbms_output.put_line('Quantity ' || quantity || ' is probably not enough.'); END CASE here; EXCEPTION WHEN CASE_NOT_FOUND THEN dbms_output.put_line('Somehow quantity must be less than 0.') END;
"Conditional Control: IF and CASE Statements", "CASE Expressions", NULLIF and COALESCE expressions in Oracle9i SQL Reference
|
Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|