PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# 3
Control Structures

One ship drives east and another drives west
With the selfsame winds that blow.
'Tis the set of the sails and not the gales
Which tells us the way to go.

Ella Wheeler Wilcox

This chapter shows you how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. And, their proper use leads naturally to a well-structured program.

##### Major Topics

Overview
Conditional Control: IF Statements
Iterative Control: LOOP and EXIT Statements
Sequential Control: GOTO and NULL Statements

## Overview

According to the structure theorem, any computer program can be written using the basic control structures shown in Figure 3-1. They can be combined in any way necessary to deal with a given problem.

#### Figure 3-1 Control Structures

The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a Boolean value (`TRUE` or `FALSE`). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur.

## Conditional Control: IF Statements

Often, it is necessary to take alternative actions depending on circumstances. The `IF` statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of `IF` statements: `IF-THEN`, `IF-THEN-ELSE`, and `IF-THEN-ELSIF`.

### IF-THEN

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

    IF condition THEN
       sequence_of_statements;
    END IF;

The sequence of statements is executed only if the condition yields `TRUE`. If the condition yields `FALSE` or `NULL`, the `IF` statement does nothing. In either case, control passes to the next statement. An example follows:

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

You might want to place brief `IF` statements on a single line, as in

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

### IF-THEN-ELSE

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

    IF condition THEN
       sequence_of_statements1;
    ELSE
       sequence_of_statements2;
    END IF;

The sequence of statements in the `ELSE` clause is executed only if the condition yields `FALSE` or `NULL`. Thus, the `ELSE` clause ensures that a sequence of statements is executed. In the following example, the first or second `UPDATE` statement is executed when the condition is true or false, respectively:

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

The `THEN` and `ELSE` clauses can include `IF` statements. That is, `IF` statements can be nested, as the following example shows:

    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;

### IF-THEN-ELSIF

Sometimes you want to select an action from several mutually exclusive alternatives. The third form of `IF` statement uses the keyword `ELSIF` (not `ELSEIF`) to introduce additional conditions, as follows:

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

If the first condition yields `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 yields `TRUE`, its associated sequence of statements is executed and control passes to the next statement. If all conditions yield `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 more 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 yields `TRUE`, its associated statement is executed and control passes to the `INSERT` statement.

### Guidelines

Avoid clumsy `IF` statements like those in the following example:

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

This code disregards two useful facts. First, the value of a Boolean expression can be assigned directly to a Boolean variable. So, you can replace the first `IF` statement with a simple assignment, as follows:

    overdrawn := new_balance < minimum_balance;

Second, a Boolean variable is itself either true or false. So, you can simplify the condition in the second `IF` statement, as follows:

    IF overdrawn THEN ...

When possible, use the `ELSIF` clause instead of nested `IF` statements. That way, 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 first statement obscures the flow of logic, whereas the second statement reveals it.

## Iterative Control: LOOP and EXIT Statements

`LOOP` statements let you execute a sequence of statements multiple times. There are three forms of `LOOP` statements: `LOOP`, `WHILE-LOOP`, and `FOR-LOOP`.

### LOOP

The simplest form of `LOOP` statement is the basic (or infinite) 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. If further processing is undesirable or impossible, you can use an `EXIT` statement to complete the loop. You can place one or more `EXIT` statements anywhere inside a loop, but nowhere outside a loop. There are two forms of `EXIT` statements: `EXIT` and `EXIT-WHEN`.

#### EXIT

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. An example follows:

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

The next example shows that you cannot use the `EXIT` statement to complete a PL/SQL block:

    BEGIN
       ...
       IF credit_rating < 3 THEN
          ...
          EXIT;  -- illegal
       END IF;
    END;

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 "RETURN Statement".

#### EXIT-WHEN

The `EXIT-WHEN` statement allows a loop to complete conditionally. When the `EXIT` statement is encountered, the condition in the `WHEN` clause is evaluated. If the condition yields `TRUE`, the loop completes and control passes to the next statement after the loop. An example follows:

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

Until the condition yields `TRUE`, the loop cannot complete. So, statements within the loop must change the value of the condition. In the last example, if the `FETCH` statement returns a row, the condition yields `FALSE`. When the `FETCH` statement fails to return a row, the condition yields `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.

#### Loop Labels

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, you can 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.

### WHILE-LOOP

The `WHILE-LOOP` statement associates a condition with a sequence of statements enclosed by the keywords `LOOP` and `END` `LOOP`, as follows:

    WHILE condition LOOP
       sequence_of_statements;
    END LOOP;

Before each iteration of the loop, the condition is evaluated. If the condition yields `TRUE`, the sequence of statements is executed, then control resumes at the top of the loop. If the condition yields `FALSE` or `NULL`, the loop is bypassed and control passes to the next statement. An example follows:

    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. Since the condition is tested at the top of the loop, the sequence might execute zero times. In the last example, if the initial value of `total` is greater than 25000, the condition yields `FALSE` and the loop is bypassed.

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. Therefore, the sequence of statements is executed at least once. PL/SQL has no such structure, but you can easily build one, as follows:

    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. Otherwise, you have an infinite loop. For example, the following `LOOP` statements are logically equivalent:

    WHILE TRUE LOOP     |     LOOP
       ...              |        ...
    END LOOP;           |     END LOOP;

### FOR-LOOP

Whereas the number of iterations through a `WHILE` loop is unknown until the loop completes, the number of iterations through a `FOR` loop is known before the loop is entered. `FOR` loops iterate over a specified range of integers. (Cursor `FOR` loops, which iterate over the result set of a cursor, are discussed in Chapter 5.) The range is part of an iteration scheme, which is enclosed by the keywords `FOR` and `LOOP`. A double dot (`..`) serves as the range operator. The syntax follows:

    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;

The following example shows that if the lower bound equals the higher bound, the sequence of statements 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. However, if you use the keyword `REVERSE`, iteration proceeds downward from the higher bound to the lower bound, as the example below shows. After each iteration, the loop counter is decremented.

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

Nevertheless, you write the range bounds in ascending (not descending) order.

Inside a `FOR` loop, the loop counter can be referenced like a constant. So, the loop counter can appear in expressions but cannot be assigned values, as the following example shows:

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

#### Iteration Schemes

The bounds of a loop range can be literals, variables, or expressions but must evaluate to integers. For example, the following iteration schemes are legal:

    j IN -5..5
    k IN REVERSE first..last
    step IN 0..TRUNC(high/low) * 2
    code IN ASCII('A')..ASCII('J')

As you can see, the lower bound need not be 1. However, the loop counter increment (or decrement) must be 1. Some languages provide a `STEP` clause, which lets you specify a different increment. An example written in BASIC follows:

    FOR J = 5 TO 15 STEP 5  :REM assign values 5,10,15 to J
       sequence_of_statements  -- J has values 5,10,15
    NEXT J

PL/SQL has no such structure, but you can easily build one. Consider the following example:

    FOR j IN 5..15 LOOP  -- assign values 5,6,7,... to j
       IF MOD(j, 5) = 0 THEN  -- pass multiples of 5
          sequence_of_statements;  -- j has values 5,10,15
       END IF;
    END LOOP;

This loop is logically equivalent to the previous BASIC loop. Within the sequence of statements, the loop counter has only the values 5, 10, and 15.

You might prefer the less elegant but more efficient method shown in the example below. Within the sequence of statements, each reference to the loop counter is multiplied by the increment.

    FOR j IN 1..3 LOOP  -- assign values 1,2,3 to j
       sequence_of_statements;  -- each j becomes j*5
    END LOOP;

#### Dynamic Ranges

PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:

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

The value of `emp_count` is unknown at compile time; the `SELECT` statement returns the value at run time.

What happens if the lower bound of a loop range evaluates to a larger integer than the upper bound? As the next example shows, the sequence of statements within the loop is not executed and control passes to the next statement:

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

#### Scope Rules

The loop counter is defined only within the loop. You cannot reference it outside the loop. After the loop is exited, the loop counter is undefined, as the following example shows:

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

You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type `INTEGER`. The next example shows that the local declaration hides any global declaration:

    DECLARE
       ctr  INTEGER;
    BEGIN
       ...
       FOR ctr IN 1..25 LOOP
          ...
          IF ctr > 10 THEN ...  -- refers to loop counter
       END LOOP;
    END;

To reference the global variable in this example, you must use a label and dot notation, as follows:

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

The same scope rules apply to nested `FOR` loops. Consider the example below. Both loop counters have the same name. So, to reference the outer loop counter from the inner loop, you must use a label and dot notation, as follows:

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

#### Using the EXIT Statement

The `EXIT` statement allows a `FOR` loop to complete prematurely. For example, the following loop normally executes ten times, but as soon as the `FETCH` statement fails to return a row, the loop completes no matter how many times it has executed:

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

Suppose you must exit from a nested `FOR` loop prematurely. 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 to specify which `FOR` loop to exit, as follows:

    <<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

## Sequential Control: GOTO and NULL Statements

Unlike the `IF` and `LOOP` statements, the `GOTO` and `NULL` statements are not crucial to PL/SQL programming. The structure of PL/SQL is such that the `GOTO` statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The `NULL` statement can make the meaning and action of conditional statements clear and so improve readability.

Overuse of `GOTO` statements can result in complex, unstructured code (sometimes called spaghetti code) that is hard to understand and maintain. So, use `GOTO` statements sparingly. For example, to branch from a deeply nested structure to an error-handling routine, raise an exception rather than use a `GOTO` statement.

### 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:

    BEGIN
       ...
       <<update_row>>
       BEGIN
          UPDATE emp SET ...
          ...
       END;
       ...
       GOTO update_row;
       ...
    END;

The label `<<end_loop>>` in the following example is illegal 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>>  -- illegal
       END LOOP;  -- not an executable statement
    END;

To debug the last example, simply add the `NULL` statement, as follows:

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

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.

#### Restrictions

Some possible destinations of a `GOTO` statement are illegal. Specifically, a `GOTO` statement cannot branch into an `IF` statement, `LOOP` statement, or sub-block. For example, the following `GOTO` statement is illegal:

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

Also, a `GOTO` statement cannot branch from one `IF` statement clause to another, as the following example shows:

    BEGIN
       ...
       IF valid THEN
          ...
          GOTO update_row;  -- illegal branch into ELSE clause
       ELSE
          ...
          <<update_row>>
          UPDATE emp SET ...
       END IF;
    END;

The next example shows that a `GOTO` statement cannot branch from an enclosing block into a sub-block:

    BEGIN
       ...
       IF status = 'OBSOLETE' THEN
          GOTO delete_part;  -- illegal branch into sub-block
       END IF;
       ...
       BEGIN
          ...
          <<delete_part>>
          DELETE FROM parts WHERE ...
       END;
    END;

Also, a `GOTO` statement cannot branch out of a subprogram, as the following example shows:

    DECLARE
       ...
       PROCEDURE compute_bonus (emp_id NUMBER) IS
       BEGIN
          ...
          GOTO update_row;  -- illegal branch out of subprogram
       END;
    BEGIN
       ...
       <<update_row>>
       UPDATE emp SET ...
    END;

Finally, a `GOTO` statement cannot branch from an exception handler into the current block. For example, the following `GOTO` statement is illegal:

    DECLARE
       ...
       pe_ratio  REAL;
    BEGIN
       ...
       SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ...
       <<insert_row>>
       INSERT INTO stats VALUES (pe_ratio, ...);
    EXCEPTION
       WHEN ZERO_DIVIDE THEN
          pe_ratio := 0;
          GOTO insert_row;  -- illegal branch into current block
    END;

However, a `GOTO` statement can branch from an exception handler into an enclosing block.

### NULL Statement

The `NULL` statement explicitly specifies inaction; it does nothing other than pass control to the next statement. It can, however, improve readability. In a construct allowing alternative actions, the `NULL` statement serves as a placeholder. It tells readers that the associated alternative has not been overlooked, but that indeed no action is necessary. In the following example, the `NULL` statement shows that no action is taken for unnamed exceptions:

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

Each clause in an `IF` statement must contain at least one executable statement. The `NULL` statement meets this requirement. So, you can use the `NULL` statement in clauses that correspond to circumstances in which no action is taken. In the following example, the `NULL` statement emphasizes that only top-rated employees receive bonuses:

    IF rating > 90 THEN
       compute_bonus(emp_id);
    ELSE
       NULL;
    END IF;

Also, the `NULL` statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that allows you to defer the definition of a procedure or function until you test and debug the main program. In the following example, the `NULL` statement meets the requirement that at least one statement must appear in the executable part of a subprogram:

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

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index