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:
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;
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.
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;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.