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

This loop is useful when you want to guarantee that the body (or at least part of the body) will execute 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 (or its close cousin, EXIT WHEN) statement is executed in its body, or when an exception is raised (and goes unhandled) within the body of the loop.

5.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 conditional termination of the loop. Essentially, the EXIT WHEN combines an IF-THEN statement with the EXIT statement. Using the same example, 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.

So when should you use EXIT WHEN, and when is the stripped-down EXIT more appropriate?

· EXIT WHEN is best used when there is a single conditional expression that determines whether or not a loop should terminate. The previous example demonstrates this scenario clearly.

· In situations with multiple conditions for exiting or when you need to set a "return value" coming out of the loop based on different conditions, you are probably better off using an IF or CASE statement, with EXIT statements in one or more of the clauses.

The following example demonstrates a preferred use of EXIT. It is taken from a function that determines if two files are equal (i.e., contain the same content):