HomeOracle Oracle PL/SQL Control Structures and Loops
Oracle PL/SQL Control Structures and Loops
In this fourth part of a six part series, we look at the CASE statement and learn how to create loops in Oracle PL/SQL. This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
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 .