Home » PL/SQL

Looping in PL/SQL (Procedural Language Extension of SQL)

In this article we will learn about different types of loops in PL/SQL, their syntaxes and executions.
Submitted by Yash Kumar, on October 03, 2017

In PL/SQL there are three types of loops:

1. Exit loop
2. while loop
3. for loop

Let’s learn one by one...

1) Exit loop

Exit loop is unconditional looping. It must be controlled by some condition.

Syntax:

loop <keyword>
statement1;
statement2;
statement3;
................;(number of statements)
end loop; <end loop keyword>

Example:

declare         ------declaration section of variables
i integer;
begin           -----starting the execution of code
i:=1;              ------initialization of variable i
loop             ------starting of loop
dbms_output.put_line('value of i is '||i);  -----prints the value of i
i:=i+1;                -----increment  of  i
if(i=6) then      ----- starting of if condition
exit;                ----- this works like break in c language 
end if;             ------ end of if block
end loop;        ----- end of exit loop
end;              ------end of code

Output

value of i is 1
value of i is 2
value of i is 3
value of i is 4
value of i is 5

Statement processed.
0.11 seconds

Let's see how it works?

i is initialized to 1. When it enters the loop block it will print "value of i is 1". Now it is incremented by 1. Now the condition if(i=6) is false so it will not go in the if block. When i becomes 6 it will go in the if block and exit keyword makes the control out of loop and code is terminated with the above output.

2) while loop

Syntax

while <condition> loop
statement1;
statement2;
statement3;
................;(number of statements)
end loop;

Example

declare         -----declaration section of variables
i  integer;
begin             -----starting the execution of code    
i:=1;              ----initialization of variable i
while i<=5  loop             ----- starting of loop
dbms_output.put_line('value of i is '||i);  ----prints the value of i
i:=i+1;                ------ increment  of  i
end loop;        ------ end of exit loop
end;              ------end of code

Note: Before entering in the while loop the variable must be initialized.

Output

value of i is 1
value of i is 2
value of i is 3
value of i is 4
value of i is 5

Statement processed.

0.00 seconds

Let's see how it works?

When control enters in the code i is initialized by one. Then it prints "value of i is 1". After that i is incremented by 1. When control reach the statement "end loop;" it again returns to the statement while i<=5 loop and if condition is true it will again execute the code within while block. When condition becomes false control get out of loop and the code is terminated.

3) For loop

Syntax:

for <variable name> in <initial value>..<final value> loop
statement1;
statement2;
statement3;
................;(number of statements)
end loop;

Example

declare         -----declaration section of variables
i integer;
begin           ------starting the execution of code
for i in 1..5 loop             -----starting of loop
dbms_output.put_line('value of i is '||i);  ------prints the value of i
end loop;        ------ end of exit loop
end;              ------end of code

Output

value of i is 1
value of i is 2
value of i is 3
value of i is 4
value of i is 5

Statement processed.

0.02 seconds

Let's see how it works?

Among all the PL/SQL loops for loop is the easiest as it does not require initialization and incrementing of variables. "1..5" works as 1 to 5 . "1..5" ← this will tell the machine to initialize the variable with 1 and execute the loop repeatedly until the value of i becomes greater than 5.