Oracle PL/SQL LOOP with Example

What are Loops?

Loops allows a certain part of the code in a program to get executed for the desired number of times.

In this tutorial, we are going to see the loop concept in PL/SQL and flow of control in loops. You will learn-

• Introduction to Loops Concept
• Loop Control Statements
• Types of Loop in PL/SQL
• Basic Loop Statement
• Labeling of Loops

Introduction to Loops Concept

Loops concept provides the following advantage in coding.

• Reusability of code
• Reduced code size
• Easy flow of control
• Reduced Complexity

The below diagram shows the looping concept in a pictorial manner

In the above diagram, the loop condition will be checked, and as long as the loop condition is satisfied, the execution block will be executed.

In each iteration, the loop counter variable that actually decides the loop condition should modify to make the control exit from the loop. In some cases, this loop counter variable is increment/decrement operator for a predefined count, and in some case, it is a search condition that keeps on executing the block till it satisfies it.

Loop Control Statements

Before learning the loops concept, it is mandatory to learn of loop control statements. Loop control statements are those that actually control the flow of execution inside the loop. Below is the detailed description about the loop control statements.

CONTINUE

This keyword sends an instruction to the PL/SQL engine that whenever PL/SQL engine encounters this keyword inside the loop, then it will skip the remaining code in the execution block of the code, and next iteration will start immediately. This will be mainly used if the code inside the loop wants to be skipped for certain iteration values.

EXIT / EXIT WHEN

This keyword sends an instruction to the PL/SQL engine that whenever PL/SQL engine encounters this keyword, then it will immediately exit from the current loop. If the PL/SQL engine encounters the EXIT in a nested loop, then it will come out of the loop in which it has been defined, i.e. in a nested loops, giving EXIT in the inner loop will only exit the control from inner loop but not from the outer loop. 'EXIT WHEN' is followed by an expression which gives a Boolean result. If the result is TRUE, then the control will EXIT.

GOTO

This statement will transfer the control to the labeled statement ("GOTO <label> ;"). This has the following restrictions

• Transfer of control can be done only within the subprograms.
• Transfer of control cannot be done from exception handling part to the execution part

Usage of this statement is not recommended unless there are no other alternatives, as the code-control traceability will be very difficult in the program due to the transfer of control from one part to another part.

Types of Loop in PL/SQL

PL/SQL provides following three types of loops

• Basic loop statement
• For loop statement
• While loop statement

Basic Loop Statement

This loop statement is the simplest loop structure in PL/SQL. The execution block starts with keyword 'LOOP' and ends with the keyword 'END LOOP'.

The exit condition should be given inside this execution block so that control exit from the loop.

It needs EXIT keyword to be given explicitly in the execution part to exit from the loop.

 LOOP
<execution block starts>
<EXIT condition based on developer criteria> 
<execution_block_ends>
END LOOP;
 

Syntax Explanation:

• In the above syntax, key word 'LOOP' marks the beginning of the loop and 'END LOOP' marks the end of the loop.
• The execution block contains all the code that needs to be executed including the EXIT condition.
• The execution part can contain any execution statement.

Note: Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop.

Example 1: In this example, we are going to print number from 1 to 5 using basic loop statement. For that, we will execute the following code.

DECLARE

a NUMBER:=1; 
BEGIN
dbms_output.put_line('Program started.');
LOOP
dbms_output.put_line(a);
a:=a+1;
EXIT WHEN a>5;
END LOOP;
dbms_output.put_line('Program completed');
END:
/

Code Explanation:

• Code line 2: Declaring the variable 'a' as 'NUMBER' data type and initializing it with value '1'.
• Code line 4: Printing the statement "Program started".
• Code line 5: Keyword 'LOOP' marks the beginning of the loop.
• Code line 6: Prints the value of 'a'.
• Code line 7: Increments the value of 'a' by +1.
• Code line 8: Checks whether the value of 'a' is greater than 5.
• Code line 9: Keyword 'END LOOP' marks the end of execution block.
• The code from line 6 to line 8 will continue to execute till 'a' reaches the value 6, as the condition will return TRUE, and the control will EXIT from the loop.

• Code line 10: Printing the statement "Program completed"

Labeling of Loops

In PL/SQL, the loops can be labeled. The label should be enclosed between "<<" and ">>". The labeling of loops particularly in nested loop codes will give more readability. The label can be given in EXIT command to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops, by giving the exit command followed by outer loop label.

<<OUTER_LOOP>>
LOOP 
 <execution_block_starts>
 .
 <<INNER_LOOP>>
 LOOP --inner
  <execution_part>
 END LOOP;
 .
 <executi_block_ends>
END LOOP;

Syntax Explanation:

• In the above syntax, the out loop has one more loop inside it.
• The '<<OUTER_LOOP>>' and '<<INNER_LOOP>>' are the labels of these loops.

Example 1: In this example, we are going to print number starting from 1 using Basic loop statement. Each number will be printed as many times as its value. The upper limit of the series is fixed at the program declaration part. Let us learn how we can use the label concept to achieve this. For that, we will execute the following code

DECLARE
a NUMBER:=0;
b NUMBER;
upper-limit NUMBER :=4;
BEGIN
dbms_output.put_line(‘Program started.' ); 
«outerloop»‭ ‬
LOOP 
a:=a+1;
b:=l;
«inner loop»
LOOP
EXIT outer_loop WHEN a > upper_limit;
dbms_output.put_line(a);
b:=b+l;
EXIT inner_loop WHEN b>a;
END LOOP;
END LOOP;
dbms_output.put_line('Program completed.');
END;
/

Code Explanation:

• Code line 2-3: Declaring the variable 'a' and 'b' as 'NUMBER' data type.
• Code line 4: Declaring the variable 'upper_limit' as 'NUMBER' data type with value '4'
• Code line 6: Printing the statement "Program started".
• Code line 7: The outer loop has been labeled as "outer_loop"
• Code line 9: The value of 'a' is incremented by 1.
• Code line 11: Inner loop has been labeled as "inner_loop".
• Code line 13: EXIT condition that check whether the value 'a' is higher than 'upper_limit' value. If not then it will go further, else it exits outer loop directly.
• Code line 14: Printing the value of 'b'.
• Code line 15: Increments the value of 'b' by +1.
• Code line 16: EXIT condition that checks whether the value of 'b' is higher than 'a'. If so, then it will exit the control from the inner loop.

• Code line 14: Printing the statement "Program completed"

Summary

Loop	Basic Loop
EXIT Criteria	Exit when encounters the keyword 'EXIT' in the execution part
Usage	Good to use when exit is not based on any particular condition.