A Guide to Iterative Processing with Loops in PL/SQL

PL/SQL provides support to various types of loops to iterate a portion of program many times based on some condition. Iterative control structures let the same code to be executed repeatedly.

Each type of loop is designed for a specific purpose, has its own rules and properties. As a best practice, the programmer must review the properties of the loop like, the method in which the loop is terminated, the test condition for termination, the reasons it is used for and choose the loop that is best suited for a specific requirement.

Every loop has the Loop boundary and the Loop body. The body of the loop has the sequence of executable statements that are repeatedly executed. The Loop boundary consists of the statements that initiate the loop, condition that causes the loop to terminate and the END LOOP statement that signals the end of the loop.

Different types of Loops are the simple Loop, the FOR Loop, the While Loop.

The Simple loop is basic and easy to code and use. It consists of structure as depicted below.

LOOP
executable statement(s)
END LOOP;

The loop is terminate by the EXIT or EXIT-WHEN clause. The EXIT statement finishes execution of loop without checking any condition. The EXIT-WHEN statement causes the looping process to terminate when the condition specified is encountered. Example
EXIT when num > 100;
The simple loop is used when the loop has to be executed at least once and the number of times it has to further execute is not known.

The FOR Loop is used to iterate the executable portion for known number of times. There are two kinds of PL/SQL FOR Loops. They are the numeric FOR Loop and the cursor FOR Loop.
The general syntax for the numeric FOR Loop is as below.

FOR loop index in [REVERSE] lower bound.. upper bound
LOOP
executable statement(s)
END LOOP;

The double dot operator is used to specify the range of the index variable between lower bound and upper bound values. The index variable cannot be referenced within the body of the loop. The numeric FOR Loop terminates unconditionally when the loop is executed specified number of times. After each execution of the loop body the index variable is checked against the upper bound value. If it exceeds the loop is terminated else index value is incremented by one and the loop executed again. The REVERSE keyword is used to decrement index value from the upper bound value to lower bound value. Even when the REVERSE is specified the lower bound value and upper bound value should be specified in the same order. Terminating Loop with EXIT statement is not a recommended practice

Example
FOR LOOP_COUNT IN 1..10
LOOP
…executable statement…
END LOOP;

The Cursor FOR Loop is a loop that is defined by an explicit cursor. The general way in which a cursor is used involves opening the cursor, beginning a loop , fetching a row from the cursor, checking for end of data, manipulating the data fetched , ending the loop and closing the cursor. The concept of cursor FOR loop reduces the code by implicitly performing most of these steps. The basic syntax is

FOR record_index in [cursor_name, (explicit SELECT statement)]
LOOP
executable statement(s)
END LOOP;

Where record_index is an implicit declared record by PL/SQL with the %ROWTYPE attribute aligned with the cursor specified by cursor_name.

After each execution of the loop body, PL/SQL performs another fetch till the %NOTFOUND attribute of the cursor is set to TRUE that is the loop terminates when all the records are fetched. Terminating Loop with EXIT statement is allowed but not a recommended practice. It is used when all records fetched by a cursor has to be processed.

The WHILE Loop repeats the set of executable statements present in the body of the loop till the given condition evaluates to TRUE. The general syntax of the WHILE-LOOP statement is

While condition LOOP
executable statement(s)
END LOOP;

The loop is executed based only on the evaluation of condition. It is used mainly when the loop need not be executed mandatory even once; execution is based only on condition. The information needed to assess the condition must be in place before the loop is executed for the first time.

Thus Looping constructs in PL/SQL provides the programmer with a lot of flexibility, ease and modularity to write code to handle almost any situation provided the best construct is chosen by the programmer.

You may also be interested in this article: The Ultimate Guide to Shopping in Alicante: Unveiling the City’s Best Shopping Centers and Unique Boutiques

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |