4 Using PL/SQL Control Structures

Using the IF-THEN Statement

The simplest form of IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF (not ENDIF):

IF condition THEN
   sequence_of_statements
END IF;

The sequence of statements is executed only if the condition is true. If the condition is false or null, the IF statement does nothing. In either case, control passes to the next statement.

IF sales > quota THEN
   compute_bonus(empid);
   UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;

You can place brief IF statements on a single line:

IF x > y THEN high := x; END IF;

Using the IF-THEN-ELSE Statement

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of statements:

IF condition THEN
   sequence_of_statements1
ELSE
   sequence_of_statements2
END IF;

The statements in the ELSE clause are executed only if the condition is false or null. The IF-THEN-ELSE statement ensures that one or the other sequence of statements is executed. In the following example, the first UPDATE statement is executed when the condition is true, and the second UPDATE statement is executed when the condition is false or null:

IF trans_type = 'CR' THEN
   UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
   UPDATE accounts SET balance = balance - debit WHERE ...
END IF;

IF statements can be nested:

IF trans_type = 'CR' THEN
   UPDATE accounts SET balance = balance + credit WHERE ...
ELSE
   IF new_balance >= minimum_balance THEN
      UPDATE accounts SET balance = balance - debit WHERE ...
   ELSE
      RAISE insufficient_funds;
   END IF;
END IF;

Using the IF-THEN-ELSIF Statement

Sometimes you want to choose between several alternatives. You can use the keyword ELSIF (not ELSEIF or ELSE IF) to introduce additional conditions:

IF condition1 THEN
   sequence_of_statements1
ELSIF condition2 THEN
   sequence_of_statements2
ELSE
   sequence_of_statements3
END IF;

If the first condition is false or null, the ELSIF clause tests another condition. An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is true, its associated sequence of statements is executed and control passes to the next statement. If all conditions are false or null, the sequence in the ELSE clause is executed. Consider the following example:

BEGIN
   IF sales > 50000 THEN
      bonus := 1500;
   ELSIF sales > 35000 THEN
      bonus := 500;
   ELSE
      bonus := 100;
   END IF;
   INSERT INTO payroll VALUES (emp_id, bonus, ...);
END;

If the value of sales is larger than 50000, the first and second conditions are true. Nevertheless, bonus is assigned the proper value of 1500 because the second condition is never tested. When the first condition is true, its associated statement is executed and control passes to the INSERT statement.

Using the CASE Statement

Like the IF statement, the CASE statement selects one sequence of statements to execute. However, to select the sequence, the CASE statement uses a selector rather than multiple Boolean expressions. (Recall from Chapter 2 that a selector is an expression whose value is used to select one of several alternatives.) To compare the IF and CASE statements, consider the following code that outputs descriptions of school grades:

IF grade = 'A' THEN
   dbms_output.put_line('Excellent');
ELSIF grade = 'B' THEN
   dbms_output.put_line('Very Good');
ELSIF grade = 'C' THEN
   dbms_output.put_line('Good');
ELSIF grade = 'D' THEN
   dbms_output. put_line('Fair');
ELSIF grade = 'F' THEN
   dbms_output.put_line('Poor');
ELSE
   dbms_output.put_line('No such grade');
END IF;

Notice the five Boolean expressions. In each instance, we test whether the same variable, grade, is equal to one of five values: 'A', 'B', 'C', 'D', or 'F'. Let us rewrite the preceding code using the CASE statement, as follows:

CASE grade
   WHEN 'A' THEN dbms_output.put_line('Excellent');
   WHEN 'B' THEN dbms_output.put_line('Very Good');
   WHEN 'C' THEN dbms_output.put_line('Good');
   WHEN 'D' THEN dbms_output.put_line('Fair');
   WHEN 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;

The CASE statement is more readable and more efficient. When possible, rewrite lengthy IF-THEN-ELSIF statements as CASE statements.

The CASE statement begins with the keyword CASE. The keyword is followed by a selector, which is the variable grade in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.

The selector is followed by one or more WHEN clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a WHEN-clause expression, that WHEN clause is executed. For instance, in the last example, if grade equals 'C', the program outputs 'Good'. Execution never falls through; if any WHEN clause is executed, control passes to the next statement.

The ELSE clause works similarly to the ELSE clause in an IF statement. In the last example, if the grade is not one of the choices covered by a WHEN clause, the ELSE clause is selected, and the phrase 'No such grade' is output. The ELSE clause is optional. However, if you omit the ELSE clause, PL/SQL adds the following implicit ELSE clause:

ELSE RAISE CASE_NOT_FOUND;

There is always a default action, even when you omit the ELSE clause. If the CASE statement does not match any of the WHEN clauses and you omit the ELSE clause, PL/SQL raises the predefined exception CASE_NOT_FOUND.

The keywords END CASE terminate the CASE statement. These two keywords must be separated by a space. The CASE statement has the following form:

[<<label_name>>]
CASE selector
   WHEN expression1 THEN sequence_of_statements1;
   WHEN expression2 THEN sequence_of_statements2;
   ...
   WHEN expressionN THEN sequence_of_statementsN;
  [ELSE sequence_of_statementsN+1;]
END CASE [label_name];

Like PL/SQL blocks, CASE statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the CASE statement. Optionally, the label name can also appear at the end of the CASE statement.

Exceptions raised during the execution of a CASE statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

An alternative to the CASEstatement is the CASE expression, where each WHEN clause is an expression. For details, see "CASE Expressions".

[<<label_name>>]
CASE
   WHEN search_condition1 THEN sequence_of_statements1;
   WHEN search_condition2 THEN sequence_of_statements2;
   ...
   WHEN search_conditionN THEN sequence_of_statementsN;
  [ELSE sequence_of_statementsN+1;]
END CASE [label_name];

CASE
   WHEN grade = 'A' THEN dbms_output.put_line('Excellent');
   WHEN grade = 'B' THEN dbms_output.put_line('Very Good');
   WHEN grade = 'C' THEN dbms_output.put_line('Good');
   WHEN grade = 'D' THEN dbms_output.put_line('Fair');
   WHEN grade = 'F' THEN dbms_output.put_line('Poor');
   ELSE dbms_output.put_line('No such grade');
END CASE;

ELSE RAISE CASE_NOT_FOUND;

Guidelines for PL/SQL Conditional Statements

Avoid clumsy IF statements like those in the following example:

IF new_balance < minimum_balance THEN
   overdrawn := TRUE;
ELSE
   overdrawn := FALSE;
END IF;
...
IF overdrawn = TRUE THEN
   RAISE insufficient_funds;
END IF;

The value of a Boolean expression can be assigned directly to a Boolean variable. You can replace the first IF statement with a simple assignment:

overdrawn := new_balance < minimum_balance;

A Boolean variable is itself either true or false. You can simplify the condition in the second IF statement:

IF overdrawn THEN ...

When possible, use the ELSIF clause instead of nested IF statements. Your code will be easier to read and understand. Compare the following IF statements:

IF condition1 THEN           |     IF condition1 THEN
   statement1;               |        statement1; 
ELSE                         |     ELSIF condition2 THEN 
   IF condition2 THEN        |        statement2; 
      statement2;            |     ELSIF condition3 THEN 
   ELSE                      |        statement3; 
      IF condition3 THEN     |     END IF;
         statement3;         |
      END IF;                |
   END IF;                   |
END IF;                      |

These statements are logically equivalent, but the second statement makes the logic clearer.

To compare a single expression to multiple values, you can simplify the logic by using a single CASE statement instead of an IF with several ELSIF clauses.

Using the LOOP Statement

The simplest form of LOOP statement is the basic loop, which encloses a sequence of statements between the keywords LOOP and END LOOP, as follows:

LOOP
   sequence_of_statements
END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. You use an EXIT statement to stop looping and prevent an infinite loop. You can place one or more EXIT statements anywhere inside a loop, but not outside a loop. There are two forms of EXIT statements: EXIT and EXIT-WHEN.

Using the EXIT Statement

The EXIT statement forces a loop to complete unconditionally. When an EXIT statement is encountered, the loop completes immediately and control passes to the next statement:

LOOP
   IF credit_rating < 3 THEN
      EXIT;  -- exit loop immediately
   END IF;
END LOOP;
-- control resumes here

Remember, the EXIT statement must be placed inside a loop. To complete a PL/SQL block before its normal end is reached, you can use the RETURN statement. For more information, see "Using the RETURN Statement".

Using the EXIT-WHEN Statement

The EXIT-WHEN statement lets a loop complete conditionally. When the EXIT statement is encountered, the condition in the WHEN clause is evaluated. If the condition is true, the loop completes and control passes to the next statement after the loop:

LOOP
   FETCH c1 INTO ...
   EXIT WHEN c1%NOTFOUND;  -- exit loop if condition is true
   ...
END LOOP;
CLOSE c1;

Until the condition is true, the loop cannot complete. A statement inside the loop must change the value of the condition. In the previous example, if the FETCH statement returns a row, the condition is false. When the FETCH statement fails to return a row, the condition is true, the loop completes, and control passes to the CLOSE statement.

The EXIT-WHEN statement replaces a simple IF statement. For example, compare the following statements:

IF count > 100 THEN     |     EXIT WHEN count > 100;
   EXIT;                |
END IF;                 |

These statements are logically equivalent, but the EXIT-WHEN statement is easier to read and understand.

Labeling a PL/SQL Loop

Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the LOOP statement, as follows:

<<label_name>>
LOOP
   sequence_of_statements
END LOOP;

Optionally, the label name can also appear at the end of the LOOP statement, as the following example shows:

<<my_loop>>
LOOP
   ...
END LOOP my_loop;

When you nest labeled loops, use ending label names to improve readability.

With either form of EXIT statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an EXIT statement, as follows:

<<outer>>
LOOP
   ...
   LOOP
      ...
      EXIT outer WHEN ...  -- exit both loops
   END LOOP;
   ...
END LOOP outer;

Every enclosing loop up to and including the labeled loop is exited.

Using the WHILE-LOOP Statement

The WHILE-LOOP statement executes the statements in the loop body as long as a condition is true:

WHILE condition LOOP
   sequence_of_statements
END LOOP;

Before each iteration of the loop, the condition is evaluated. If it is true, the sequence of statements is executed, then control resumes at the top of the loop. If it is false or null, the loop is skipped and control passes to the next statement:

WHILE total <= 25000 LOOP
   SELECT sal INTO salary FROM emp WHERE ...
   total := total + salary;
END LOOP;

The number of iterations depends on the condition and is unknown until the loop completes. The condition is tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value of total is larger than 25000, the condition is false and the loop is skipped.

Some languages have a LOOP UNTIL or REPEAT UNTIL structure, which tests the condition at the bottom of the loop instead of at the top, so that the sequence of statements is executed at least once. The equivalent in PL/SQL would be:

LOOP
   sequence_of_statements
   EXIT WHEN boolean_expression;
END LOOP;

To ensure that a WHILE loop executes at least once, use an initialized Boolean variable in the condition, as follows:

done := FALSE;
WHILE NOT done LOOP
   sequence_of_statements
   done := boolean_expression;
END LOOP;

A statement inside the loop must assign a new value to the Boolean variable to avoid an infinite loop.

Using the FOR-LOOP Statement

Simple FOR loops iterate over a specified range of integers. The number of iterations is known before the loop is entered. A double dot (..) serves as the range operator:

FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
   sequence_of_statements
END LOOP;

The range is evaluated when the FOR loop is first entered and is never re-evaluated.

As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.

FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i
   sequence_of_statements  -- executes three times
END LOOP;

If the lower bound equals the higher bound, the loop body is executed once:

FOR i IN 3..3 LOOP  -- assign the value 3 to i
   sequence_of_statements  -- executes one time
END LOOP;

By default, iteration proceeds upward from the lower bound to the higher bound. If you use the keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented. You still write the range bounds in ascending (not descending) order.

FOR i IN REVERSE 1..3 LOOP  -- assign the values 3,2,1 to i
   sequence_of_statements  -- executes three times
END LOOP;

Inside a FOR loop, the counter can be read but cannot be changed:

FOR ctr IN 1..10 LOOP
   IF NOT finished THEN
      INSERT INTO ... VALUES (ctr, ...);  -- OK
      factor := ctr * 2;  -- OK
   ELSE
      ctr := 10;  -- not allowed
   END IF;
END LOOP;

Tip: A useful variation of the FOR loop uses a SQL query instead of a range of integers. This technique lets you run a query and process all the rows of the result set with straightforward syntax. For details, see "Querying Data with PL/SQL: Implicit Cursor FOR Loop".

j IN -5..5
k IN REVERSE first..last
step IN 0..TRUNC(high/low) * 2

DECLARE
   TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER;
   dates DateList;
   k CONSTANT INTEGER := 5;  -- set new increment
BEGIN
   FOR j IN 1..3 LOOP
      dates(j*k) := SYSDATE;  -- multiply loop counter by increment
   END LOOP;
   ...
END;

SELECT COUNT(empno) INTO emp_count FROM emp;
FOR i IN 1..emp_count LOOP
   ...
END LOOP;

-- limit becomes 1
FOR i IN 2..limit LOOP
   sequence_of_statements  -- executes zero times
END LOOP;
-- control passes here

FOR ctr IN 1..10 LOOP
   ...
END LOOP;
sum := ctr - 1;  -- not allowed

DECLARE
   ctr  INTEGER := 3;
BEGIN
   ...
   FOR ctr IN 1..25 LOOP
      ...
      IF ctr > 10 THEN ...  -- Refers to loop counter
   END LOOP;
-- After the loop, ctr refers to the original variable with value 3.
END;

<<main>>
DECLARE
   ctr  INTEGER;
   ...
BEGIN
   ...
   FOR ctr IN 1..25 LOOP
      ...
      IF main.ctr > 10 THEN  -- refers to global variable
         ...
      END IF;
   END LOOP;
END main;

<<outer>>
FOR step IN 1..25 LOOP
   FOR step IN 1..10 LOOP
      ...
      IF outer.step > 15 THEN ...
   END LOOP;
END LOOP outer;

FOR j IN 1..10 LOOP
   FETCH c1 INTO emp_rec;
   EXIT WHEN c1%NOTFOUND;
   ...
END LOOP;

<<outer>>
FOR i IN 1..5 LOOP
   ...
   FOR j IN 1..10 LOOP
      FETCH c1 INTO emp_rec;
      EXIT outer WHEN c1%NOTFOUND;  -- exit both FOR loops
      ...
   END LOOP;
END LOOP outer;
-- control passes here

Using the GOTO Statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement transfers control to the labeled statement or block. In the following example, you go to an executable statement farther down in a sequence of statements:

BEGIN
   ...
   GOTO insert_row;
   ...
   <<insert_row>>
   INSERT INTO emp VALUES ...
END;

In the next example, you go to a PL/SQL block farther up in a sequence of statements:

DECLARE
   x NUMBER := 0;
BEGIN
   <<increment_x>>
   BEGIN
      x := x + 1;
   END;
   IF x < 10 THEN
      GOTO increment_x;
   END IF;
END;

The label end_loop in the following example is not allowed because it does not precede an executable statement:

DECLARE
   done  BOOLEAN;
BEGIN
   FOR i IN 1..50 LOOP
      IF done THEN
         GOTO end_loop;
      END IF;
   <<end_loop>>  -- not allowed
   END LOOP;  -- not an executable statement
END;

To correct the previous example, add the NULL statement::

FOR i IN 1..50 LOOP
   IF done THEN
      GOTO end_loop;
   END IF;
   ...
<<end_loop>>
NULL;  -- an executable statement
END LOOP;

As the following example shows, a GOTO statement can branch to an enclosing block from the current block:

DECLARE
   my_ename  CHAR(10);
BEGIN
   <<get_name>>
   SELECT ename INTO my_ename FROM emp WHERE ...
   BEGIN
      GOTO get_name;  -- branch to enclosing block
   END;
END;

The GOTO statement branches to the first enclosing block in which the referenced label appears.

BEGIN
   GOTO update_row;  -- can't branch into IF statement
   IF valid THEN
      <<update_row>>
      UPDATE emp SET ...
   END IF;
END;

Using the NULL Statement

The NULL statement does nothing, and passes control to the next statement. (Some languages refer to such an instruction as a no-op.)

You can use the NULL statement to indicate that you are aware of a possibility, but no action is necessary. In the following example, the NULL statement shows that you have chosen not to take any action for unnamed exceptions:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
      ROLLBACK;
   WHEN VALUE_ERROR THEN
      INSERT INTO errors VALUES ...
      COMMIT;
   WHEN OTHERS THEN
      NULL;
END;

The NULL statement is a handy way to create placeholders and stub procedures. In the following example, the NULL statement lets you compile this procedure, then fill in the real body later:

PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS
BEGIN
   NULL;
END debit_account;