PL/SQL Loops

There may arise a situation when you need to execute a particular block of code several number of times. Programming languages provide various control structures that allow for more complicated execution paths and Loops are one of them. In other words, you may sometimes need to run a particular block of code in the program for a specific number of times or until a certain condition is satisfied. For this purpose, Oracle PL/SQL provides us the Loop facility. These statements are executed sequentially one by one from top to bottom. Loop technique in PL/SQL is also known as iterative statements.

The following are the Iterative structures or Loops provided in Oracle PL/SQL.

PL/SQL Loops

PL/SQL General Loop

A General Loop in PL/SQL is used to execute a set of statements at least once before the termination of the loop. An EXIT condition has to be specified in the loop; otherwise the looping process will get into a never ending loop, also known as an Infinite Loop. When the EXIT condition in the loop is satisfied, the control exits from the loop.

In a PL/SQL Loop, the statements are enclosed between the keywords LOOP and END LOOP. In every Loop, the statements are executed and then control restarts from the top of the loop until a certain condition is satisfied.

Syntax


Alternatively, you can use an EXIT WHEN statement to exit from the Loop. If you use just an EXIT statement, the statements in the loop is executed only once.

Example


Output

PL/SQL General Loop

PL/SQL FOR Loop

A FOR LOOP is used to execute a series of statements for a specific number of times. Iteration occurs between the starting and ending integer values in a given range. The counter variable (which is mandatory) is always incremented by 1. The loop exits when the counter attains the value of the end integer.

Syntax


Here, the variable var1 represents the start value in the range and var2 represents the end value. The counter variable is implicitly declared in the declaration section, so it’s not necessary to declare it explicitly. The counter variable is automatically incremented by 1 and therefore does not need to be incremented explicitly. EXIT WHEN statement and EXIT statements can be optionally used in FOR loops.

Example


Output

PL/SQL FOR Loop

PL/SQL WHILE Loop

This Loop repeats a series of statements while a given condition is true. Before entering the loop body, a condition in the while section is verified and if it turns out to be TRUE, then the control executed the loop until the condition becomes FALSE. It tests the condition before executing the loop body. The condition is verified at the beginning of each iteration. EXIT WHEN statement and EXIT statements can be used in a while loop.

Syntax


Example


Output

PL/SQL WHILE Loop

PL/SQL Nested Loop

You can optionally use one or more loop inside another Simple Loop, While Loop or a FOR loop. This type of a structure is used to verify or evaluate two conditions simultaneously or one after another.

Control Statements in Loops

There are some control statements that are used inside or in association with the above mentioned loops to control the functioning of these loops and termination of these loops based on their conditions.

EXIT Statement
When a loop encounters the term EXIT, it automatically terminates the loop and the control passes to the very next statement after the Loop.

EXIT-WHEN Statement
The EXIT-WHEN statement allows a loop complete conditionally. When the EXIT-WHEN statement is encountered, the condition in the WHEN Block is evaluated. If the condition evaluates to be TRUE, the loop completes (or terminates) and control passes to the very next statement after the loop.

Example


These two statements are logically correct and equivalent, but the EXIT-WHEN statement is easier comparatively.

CONTINUE Statement
This statement causes the loop to skip the remaining part of its lock and immediately re-evaluate its condition prior to reiterating or getting into the loop once again.

GOTO Statement
This statement helps to transfer the control to a labeled statement in another part of the very same program. It is generally not advised to use GOTO statements in programs. A GOTO statement can’t be branched out into a sub-block, LOOP statement, an IF statement or a CASE statement. 

A GOTO statement cannot branch out of a sub-program. To end a sub-program early, you can use the RETURN statement or a GOTO statement to branch to a place right before the end of the sub-program.

Leave a Reply

Your email address will not be published. Required fields are marked *