The CASE statement selects an execution path based on multiple conditions. This statement should not be confused with the CASE expression, which allows an expression to be selected based on the evaluation of one or more conditions.
No privileges are required to invoke the CASE statement. However, the privileges held by the authorization ID of the statement must include all necessary privileges to invoke the SQL statements and expressions that are embedded in the CASE statement.
>>-CASE--+-| searched-case-statement-when-clause |-+------------> '-| simple-case-statement-when-clause |---' >--END CASE---------------------------------------------------->< simple-case-statement-when-clause |--expression---------------------------------------------------> .--------------------------------------------------------. | .----------------------------. | V V | | >----WHEN--expression--THEN----SQL-procedure-statement--;-+-+---> >--+--------------------------------------+---------------------| | .----------------------------. | | V | | '-ELSE----SQL-procedure-statement--;-+-' searched-case-statement-when-clause .--------------------------------------------------------------. | .----------------------------. | V V | | |----WHEN--search-condition--THEN----SQL-procedure-statement--;-+-+--> >--+--------------------------------------+---------------------| | .----------------------------. | | V | | '-ELSE----SQL-procedure-statement--;-+-'
Depending on the value of SQL variable v_workdept, update column DEPTNAME in table DEPARTMENT with the appropriate name.
The following example shows how to do this using the syntax for a simple-case-statement-when-clause:
CASE v_workdept
WHEN'A00'
THEN UPDATE department
SET deptname = 'DATA ACCESS 1';
WHEN 'B01'
THEN UPDATE department
SET deptname = 'DATA ACCESS 2';
ELSE UPDATE department
SET deptname = 'DATA ACCESS 3';
END CASE
The following example shows how to do this using the syntax for a searched-case-statement-when-clause:
CASE
WHEN v_workdept = 'A00'
THEN UPDATE department
SET deptname = 'DATA ACCESS 1';
WHEN v_workdept = 'B01'
THEN UPDATE department
SET deptname = 'DATA ACCESS 2';
ELSE UPDATE department
SET deptname = 'DATA ACCESS 3';
END CASE