[Chapter 7] 7.2 The Simple Loop

7.2 The Simple Loop

The structure of the simple loop is the most basic of all the loop constructs. It consists of the LOOP keyword, the body of executable code, and the END LOOP keywords, as shown here:

LOOP
   <executable statement(s)>
END LOOP;

The loop boundary consists solely of the LOOP and END LOOP reserved words. The body must consist of at least one executable statement. The following table summarizes the properties of the single loop:

Property	Description
How the loop is terminated	The simple loop is terminated when an EXIT statement is executed in the body of the loop. If this statement is not executed, the simple loop becomes a true infinite loop.
When the test for termination takes place	The test takes place inside the body of the loop -- and then only if an EXIT or EXIT WHEN statement is executed. Therefore, the body -- or part of the body -- of the simple loop will always execute at least once.
Reason to use this loop	Use the simple loop when: You are not sure how many times you will want the loop to execute, and You want the loop to run at least once.

Property

Description

How the loop is terminated

The simple loop is terminated when an EXIT statement is executed in the body of the loop. If this statement is not executed, the simple loop becomes a true infinite loop.

When the test for termination takes place

The test takes place inside the body of the loop -- and then only if an EXIT or EXIT WHEN statement is executed. Therefore, the body -- or part of the body -- of the simple loop will always execute at least once.

Reason to use this loop

Use the simple loop when:

You are not sure how many times you will want the loop to execute, and
You want the loop to run at least once.

This loop is useful when you want to guarantee that the body (or at least part of the body) executes at least one time. Because there is no condition associated with the loop boundary that determines whether or not it should execute, the body of the loop will always execute the first time.

The simple loop will terminate only when an EXIT statement is executed in its body (see "Terminating a Single Loop: EXIT and EXIT WHEN" below). Because this doesn't have to be the case, a simple loop can also become an infinite loop. This could cause difficulties in your program and is something to be avoided.

The following example shows a simple loop which is truly infinite; it keeps checking for messages from a particular pipe so that it can respond immediately and display the information in the pipe. (This is the central concept behind a DBMS_PIPE-based debugger for PL/SQL code -- a prototype of which may be found in the dbg.doc file on the companion disk. See Appendix A, What's on the Companion Disk?, for details.)

DECLARE
   pipe_status INTEGER;
   message_text VARCHAR2
BEGIN
   LOOP
      pipe_status := DBMS_PIPE.RECEIVE_MESSAGE ('execution_trace');
      IF pipe_status = 0
      THEN
         DBMS_PIPE.UNPACK_MESSAGE (message_text);
         DBMS_OUTPUT.PUT_LINE (message_text);
      END IF;
   END LOOP;
END;

7.2.1 Terminating a Simple Loop: EXIT and EXIT WHEN

Be very careful when you use simple loops. Make sure they always have a way to stop. To force a simple loop to stop processing, execute an EXIT or EXIT WHEN statement within the body of the loop. The syntax for these statements is as follows:

EXIT:
EXIT WHEN condition;

where condition is a Boolean expression.

The following example demonstrates how the EXIT forces the loop to immediately halt execution and pass control to the next statement after the END LOOP statement. The account_balance procedure returns the amount of money remaining in the account specified by the account ID. If there is less than $1000 left, the EXIT statement is executed and the loop is terminated. Otherwise, the program applies the balance to the outstanding orders for that account.

LOOP
   balance_remaining := account_balance (account_id);
   IF balance_remaining < 1000
   THEN
      EXIT;
   ELSE
      apply_balance (account_id, balance_remaining);
   END IF;
END LOOP;

You can use an EXIT statement only within a LOOP.

PL/SQL also offers the EXIT WHEN statement, which supports the concept of "conditional termination" of the loop. Essentially, the EXIT WHEN combines an IF-THEN statement with the EXIT statement. Using the same example I showed above, the EXIT WHEN changes the loop to:

LOOP
   /* Calculate the balance */
   balance_remaining := account_balance (account_id);

   /* Embed the IF logic into the EXIT statement */
   EXIT WHEN balance_remaining < 1000;

   /* Apply balance if still executing the loop */
   apply_balance (account_id, balance_remaining);
END LOOP;

Notice that the loop no longer requires an IF statement to determine when it should exit. Instead, that conditional logic is embedded inside the EXIT WHEN statement.

EXIT WHEN is a very concise and readable way to terminate a simple loop; I recommend its use over the unconditional EXIT statement. After all, you should always have an EXIT statement nested within an IF-THEN. If you don't, then you either have an infinite loop or you have a loop that executes just once. In the latter case, it is better to execute the body of the loop without using a loop. If the EXIT is always included within the IF-THEN, you'd be better off using a language construct built specifically for the purpose. The EXIT WHEN construct also reduces the amount of code you need to write.

7.2.2 Emulating a REPEAT UNTIL Loop

PL/SQL does not provide a REPEAT UNTIL loop in which the condition is tested after the body of the loop is executed and thus guarantees that the loop always executes at least once. You can, however, emulate a REPEAT UNTIL with a simple loop, as follows:

LOOP
   ... body of loop ...
   EXIT WHEN boolean_condition;
END LOOP;

where boolean_condition is a Boolean variable or an expression that evaluates to a Boolean value of TRUE or FALSE.


7.1 Loop Basics		7.3 The Numeric FOR Loop