Oracle PL/SQL Control Structures and Loops

The CASE statement is useful when you need to compare a value against an array of possibilities. While doing so is certainly possible using an IF statement, the code readability improves considerably by using the CASE statement. The CASE statement has two different forms, CASE-WHEN and the searched CASE statement. The CASE-WHEN statement identifies the variable to be compared in the first line of the CASE statement and performs the comparisons to the variable in subsequent lines. Here is the CASE-WHEN syntax:

CASE expression
   WHEN expression THEN statement_list
   [WHEN expression THEN statement_list] . . .
   [ELSE statement_list]
END CASE;

The ELSE condition executes if none of the other WHEN conditions evaluate to TRUE . Consider the following example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE state
   WHEN 'AL' THEN tax_rate := .04;
   WHEN 'AK' THEN tax_rate := .00;
   ...
   WHEN 'WY' THEN tax_rate := .06;
END CASE;

Alternatively, the searched CASE statement gives you a bit more flexibility (at the expense of more typing). Here is the searched CASE syntax:

CASE
   WHEN condition THEN statement_list
   [WHEN condition THEN statement_list] . . .
   [ELSE statement_list]
END CASE;

Consider the following revised example, which sets a variable containing the appropriate sales tax rate by comparing a customer’s state to a list of values:

CASE
   WHEN state='AL' THEN tax_rate := .04;
   WHEN state='AK' THEN tax_rate := .00;
   ...
   WHEN state='WY' THEN tax_rate := .06;
END CASE;

The form of the CASE statement is sometimes driven by your programming style. However, when you have complex conditions that cannot be represented in the CASE-WHEN syntax, you have no choice but to use the searched CASE . In either case (no pun intended), the readability of your code is dramatically improved in contrast to representing the same logic using
IF-THEN-[ELSIF][-ELSE]-END IF .