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 .

{mospagebreak title=Oracle Iterators: LOOP-END LOOP, EXIT, and EXIT-WHEN}

Some tasks, such as inserting a number of new rows into a table, require the ability to repeatedly execute over a set of statements. This section introduces the various methods available for iterating and exiting loops.

LOOP

The most basic way to loop through a series of statements is with the LOOP-END LOOP construct using this syntax:

LOOP

statement1;

statement2;

. . .

END LOOP;

The first question that may come to mind is, how useful is this plain LOOP construct if you can’t exit the loop? The DBA will not be too happy if your subprogram runs indefinitely. The EXIT constructs will address this issue in the next section.

EXIT and EXIT-WHEN

The EXIT statement forces a loop to complete unconditionally. As you might expect, you execute the EXIT statement based on a condition in an IF statement. Consider the following example where you display the square roots of the numbers one through ten:

DECLARE

countr NUMBER := 1;

BEGIN

LOOP

dbms_output.put_line(‘Square root of ‘ || countr || ‘ is ‘ || SQRT(countr));

countr := countr + 1;

IF countr > 10 THEN

EXIT;

END IF;

END LOOP;

dbms_output.put_line(‘End of Calculations.’);

END;

The counter is initialized in the DECLARE section; within the loop, the counter is incremented. Once the counter reaches the threshold value in the IF statement, the loop terminates and continues execution after the END LOOP statement. The output looks like this:

——————————————–

Square root of 1 is 1

Square root of 2 is 1.41421356237309504880168872420969807857 Square root of 3 is 1.73205080756887729352744634150587236694 Square root of 4 is 2

Square root of 5 is 2.23606797749978969640917366873127623544 Square root of 6 is 2.44948974278317809819728407470589139197 Square root of 7 is 2.64575131106459059050161575363926042571 Square root of 8 is 2.82842712474619009760337744841939615714 Square root of 9 is 3

Square root of 10 is 3.16227766016837933199889354443271853372

End of Calculations.

Statement processed.

——————————————–

You can alternatively use the EXIT-WHEN construct to improve the readability of your code if you only use the IF statement to check for a termination condition. You can rewrite the previous code example as follows:

DECLARE

countr NUMBER := 1;

BEGIN

LOOP

dbms_output.put_line(‘Square root of ‘ || countr || ‘ is ‘ || SQRT(countr));

countr := countr + 1;

EXIT WHEN countr > 10;

END LOOP;

dbms_output.put_line(‘End of Calculations.’);

END;

{mospagebreak title=The FOR and WHILE-LOOP in Oracle PL/SQL}

As yet another alternative to EXIT, you can place your loop termination condition at the beginning of the loop using this syntax:

WHILE condition LOOP

statement1;

statement2;

. . .

END LOOP;

While this syntax may be more readable, it also has one major distinction compared to the previously discussed loop constructs: if the condition in the WHILE clause is not true the first time through the loop, the statements within the loop are not executed at all. In contrast, all previous versions of the LOOP construct execute the code within the loop at least once. Here is the previous example rewritten to use WHILE :

DECLARE

countr NUMBER := 1;

BEGIN

WHILE countr < 11 LOOP

dbms_output.put_line(‘Square root of ‘ || countr || ‘ is ‘ || SQRT(countr));

countr := countr + 1;

END LOOP;

dbms_output.put_line(‘End of Calculations.’);

END;

FOR-LOOP

If your application needs to iterate over a range of integers, you can use the FOR-LOOP construct and simplify your code even more. Here is the syntax:

FOR variable IN startvalue..endvalue LOOP

statement1;

statement2;

. . .

END LOOP;

Within the loop, the variable variable starts with a value of startvalue and terminates the loop when the value of variable exceeds endvalue . Rewriting our well-worn example from earlier in the chapter (and while we’re at it, dropping the unnecessary variable declaration) looks like this:

BEGIN

FOR i IN 1..10 LOOP

dbms_output.put_line(‘Square root of ‘ || i || ‘ is ‘ || SQRT(i));

END LOOP;

dbms_output.put_line(‘End of Calculations.’);

END;

Note that you do not need to include the loop variable in the declaration section. You can, however, explicitly declare your loop variables depending on your programming standards.

In our final loop example, you want to iterate your loop in reverse order and produce the square roots starting with ten and ending at one. As you might expect, all you need to add is the REVERSE keyword to your LOOP clause as follows:

BEGIN

FOR i IN REVERSE 1..10 LOOP

dbms_output.put_line(‘Square root of ‘ || i || ‘ is ‘ || SQRT(i));

END LOOP;

dbms_output.put_line(‘End of Calculations.’);

END;

This produces the following output, as expected:

——————————————–

Square root of 10 is 3.16227766016837933199889354443271853372 Square root of 9 is 3

Square root of 8 is 2.82842712474619009760337744841939615714 Square root of 7 is 2.64575131106459059050161575363926042571 Square root of 6 is 2.44948974278317809819728407470589139197 Square root of 5 is 2.23606797749978969640917366873127623544 Square root of 4 is 2

Square root of 3 is 1.73205080756887729352744634150587236694 Square root of 2 is 1.41421356237309504880168872420969807857 Square root of 1 is 1

End of Calculations.

Statement processed.

——————————————–

Please check back for the next part of this series.