Executes one of a sequence of alternate statement blocks in
a compound statement of a multistatement procedure. Unlike
the simple CASE control statement, the searched CASE control
statement supports arbitrary predicates for the WHEN clause that
can contain variable and parameter references.
1 – Environment
You can use the searched CASE control statement in a compound
statement of a multistatement procedure:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1mcase-searched-statement = [m [1m [m [1;4mCASE[m [1mqqqqqwqq> [1;4mWHEN[m[1m q> predicate q> [1;4mTHEN[m[1m qqqqqk[m [1m [m [1m x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m x mqwq> compound-use-statement qwqwk [m [1m x mqqqqqqqqqqqq <qqqqqqqqqqqqqj xx [m [1m mqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqjx [m [1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [1;4mEND[m[1m [1;4mCASE[m[1m qq> [m [1m mq> [1;4mELSE[m[1m qwq> compound-use-statement qwj [m [1m mqqqqqqqqqqqqq <qqqqqqqqqqqqj [m
3 – Arguments
3.1 – ELSE
Specifies the set of SQL statements to be executed when the WHEN
clause evaluates to FALSE or UNKNOWN.
3.2 – THEN
Specifies the set of SQL statements to be executed when the WHEN
clause evaluates to TRUE.
3.3 – WHEN
Determines whether the compound use statements in the THEN clause
are to be executed or the compound use statements in the ELSE
clause are to be executed. If the predicate evaluates to TRUE,
then the compound use statements in the THEN clause are executed.
If the predicate evaluates to FALSE or UNKNOWN, then the compound
use statements in the ELSE clause are executed.
4 – Examples
Example 1: Specifying Predicates with Variable References
SQL> CREATE TABLE T (C INT);
SQL> BEGIN
cont> DECLARE :V INTEGER = 10;
cont> DECLARE :X INTEGER = 0;
cont> CASE
cont> WHEN :V = 1 THEN INSERT INTO T(C) VALUES (:X + 1);
cont> WHEN :V = 2 THEN INSERT INTO T(C) VALUES (:X + 2);
cont> WHEN :V > 3 THEN INSERT INTO T(C) VALUES (:X);
cont> ELSE INSERT INTO T(C) VALUES (-1);
cont> END CASE;
cont> END;