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
LOOP Statement1; Statement2; EXIT; END LOOP;
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
declare var1 integer; begin var1:=&var1; loop dbms_output.put_line(var1); var1:=var1+1; exit when var1>5; end loop; end; /
Output
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
FOR counter IN var1....var2
LOOP statement1;
statement2;
END LOOP;
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
declare var1 integer; begin var1:=&var1; for var1 in 1..5 loop dbms_output.put_line(var1); end loop; end; /
Output
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
WHILE <condition>
LOOP statement1;
statement2;
END LOOP;
Example
declare var1 integer; begin var1:=&var1; while var1<5 loop dbms_output.put_line(var10); var1:=var1+1; end loop; end; /
Output
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
IF count > 50 THEN EXIT; ENDIF; EXIT WHEN count > 150;
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.