CHAPTER 4

Conditional and Sequential Control

IF Statements

Using operators such as IS NULL and IS NOT NULL or functions
such as COALESCE and NVL2 are good ways to detect and deal with
potentially NULL values. For every variable that you reference in
every Boolean expression that you write, be sure to think carefully
about the consequences if that variable is NULL.

Nested IF Statements

IF condition1
THEN
  IF condition2
  THEN
    statements2
  ELSE
    IF condition3
    THEN
      statements3
    ELSIF condition4
    THEN
      statements4
    END IF;
  END IF;
END IF;

CASE Statements and Expressions

CASE employee_type
WHEN 'S' THEN
award_salary_bonus(employee_id);
WHEN 'H' THEN
award_hourly_bonus(employee_id);
WHEN 'C' THEN
award_commissioned_bonus(employee_id);
ELSE
RAISE invalid_employee_type;
END CASE;

In other words, if you don’t specify an ELSE clause, and none of the results in the WHEN
clauses match the result of the CASE expression, PL/SQL raises a CASE_NOT_FOUND
error. This behavior is different from what I’m used to with IF statements. When an IF
statement lacks an ELSE clause, nothing happens when the condition is not met. With
CASE, the analogous situation leads to an error.

Searched CASE Statements

CASE
WHEN expression1 THEN
statements1
WHEN expression2 THEN
statements2
...
ELSE
statements_else
END CASE;

Loop Basics

/* File on web: loop_examples.sql */
PROCEDURE display_multiple_years (
  start_year_in IN PLS_INTEGER
  ,end_year_in IN PLS_INTEGER
)
IS
  l_current_year PLS_INTEGER := start_year_in;
BEGIN
  LOOP
    EXIT WHEN l_current_year > end_year_in;
      display_total_sales (l_current_year);
      l_current_year := l_current_year + 1;
   END LOOP;
END display_multiple_years;

Oracle offers a numeric and a cursor FOR loop.

/* File on web: loop_examples.sql */
PROCEDURE display_multiple_years (
  start_year_in IN PLS_INTEGER
  ,end_year_in IN PLS_INTEGER
)
IS
BEGIN
  FOR l_current_year IN start_year_in .. end_year_in
    LOOP
    display_total_sales (l_current_year);
  END LOOP;
END display_multiple_years;

/* File on web: loop_examples.sql */
PROCEDURE display_multiple_years (
  start_year_in IN PLS_INTEGER
  ,end_year_in IN PLS_INTEGER
)
IS
BEGIN
  FOR sales_rec IN (
    SELECT *
    FROM sales_data
    WHERE year BETWEEN start_year_in AND end_year_in)
  LOOP
    display_total_sales (sales_rec.year);
  END LOOP;
END display_multiple_years;

The WHILE loop
simple loop

LOOP
  executable statement(s)
END LOOP;

EXIT;
EXIT WHEN condition;

As anyone who has accidentally run such an infinite loop can attest, it’s likely
that the loop will consume large portions of the CPU. The solution for this, in addition
to ensuring that your data gathering is performed as efficiently as possible, is to pause
between iterations:

LOOP
  data_gathering_procedure;
  DBMS_LOCK.sleep(10); -- do nothing for 10 seconds
END LOOP;

During the sleep period, the program uses virtually no cycles.

The WHILE Loop

WHILE condition
LOOP
  executable statement(s)
END LOOP;

The Numeric FOR Loop

FOR loop index IN [REVERSE] lowest number .. highest number
 LOOP
  executable statement(s)
END LOOP;

Even when you specify a REVERSE direction, you must still list the lowest bound
before the highest bound. If the first number is greater than the second number,
the body of the loop will not execute at all. If the lowest and highest bounds have
the same value, the loop will execute just once.

The Cursor FOR Loop

Here is the basic syntax of a cursor FOR loop:

FOR record IN { cursor_name | (explicit SELECT statement) }
LOOP
  executable statement(s)
END LOOP;

where record is a record declared implicitly by PL/SQL with the %ROWTYPE attribute
against the cursor specified by cursor_name.

Example of Cursor FOR Loops

Suppose I need to update the bills for all pets staying in my pet hotel, the Share-a-Din-
Din Inn. The following example contains an anonymous block that uses a cursor, occupancy_cur, to select the room number and pet ID number for all occupants of the
Inn. The procedure update_bill adds any new changes to that pet’s room charges:

DECLARE
   CURSOR occupancy_cur IS
      SELECT pet_id, room_number
      FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
   occupancy_rec occupancy_cur%ROWTYPE;
BEGIN
  OPEN occupancy_cur;
  LOOP
    FETCH occupancy_cur INTO occupancy_rec;
    EXIT WHEN occupancy_cur%NOTFOUND;
    update_bill
    (occupancy_rec.pet_id, occupancy_rec.room_number);
  END LOOP;
  CLOSE occupancy_cur;
END;

This code leaves nothing to the imagination. In addition to defining the cursor (line 2),
you must explicitly declare the record for the cursor (line 5), open the cursor (line 7),
start up an infinite loop (line 8), fetch a row from the cursor set into the record (line 9),
check for an end-of-data condition with the %NOTFOUND cursor attribute (line 10),
and finally perform the update (line 11). When you are all done, you have to remember
to close the cursor (line 14).

If I convert this PL/SQL block to use a cursor FOR loop, then I have:

DECLARE
  CURSOR occupancy_cur IS
  SELECT pet_id, room_number
  FROM occupancy WHERE occupied_dt = TRUNC (SYSDATE);
BEGIN
  FOR occupancy_rec IN occupancy_cur
  LOOP
    update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);
  END LOOP;
END;

You can use the loop label to qualify the name of the loop indexing variable (either
a record or a number). Again, this can be helpful for readability. Here is an example

<<year_loop>>
FOR year_number IN 1800..1995
LOOP
  <<month_loop>>
  FOR month_number IN 1 .. 12
  LOOP
    IF year_loop.year_number = 1900 THEN ... END IF;
  END LOOP month_loop;
END LOOP year_loop;


EXIT loop_label;
EXIT loop_label WHEN condition;

The CONTINUE Statement

Here is a simple example of using CONTINUE WHEN to skip over loop body execution

for even numbers:
BEGIN
  FOR l_index IN 1 .. 10
  LOOP
    CONTINUE WHEN MOD (l_index, 2) = 0;
    DBMS_OUTPUT.PUT_LINE ('Loop index = ' || TO_CHAR (l_index));
  END LOOP;
END;
/
The output is:
Loop index = 1
Loop index = 3
Loop index = 5
Loop index = 7
Loop index = 9

Exception Handlers