Oracle PL/SQL LOOP with Example

Details

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

Loops concept provides the following advantage in coding.

The below diagram shows the looping concept in a pictorial manner

Loops in PL/SQL

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

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

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.

Loops in PL/SQL 

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

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.

Loops in PL/SQL 

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:

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.

Loops in PL/SQL 

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

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

Loops in PL/SQLLoops in PL/SQL 

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:

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.

 

YOU MIGHT LIKE: