PL/SQL Loops

Loops in PL/SQL provides a way of repeating a particular part of any program or any code statement as many times as required.

In PL/SQL we have three different loop options to choose from when we want to execute a statement repeatedly in our code block. They are:

1. Basic Loop
2. While Loop
3. For Loop

In this tutorial we will learn about all three of them while covering the concept of nexted loops which means loop inside a loop. So let's get started.

PL/SQL Basic Loop

Basic loop or simple loop is preferred in PL/SQL code when there is no surety about how many times the block of code is to be repeated. When we use the basic loop the code block will be executed at least once.

While using it, following two things must be considered:

• Simple loop always begins with the keyword LOOP and ends with a keyword END LOOP.
• A basic/simple loop can be terminated at any given point by using the exit statement or by specifying certain condition by using the statement exit when.

Syntax:

LOOP
	sequence of statements
END LOOP;

Let's see the simple loop in action in the code example below.

Time for an Example!

In the code below, we have used the loop to print counting from 1 to 10 on the console and have used the exit statement to break out of the loop.

set serveroutput on;

DECLARE
	i int;
BEGIN
	i := 1;
	LOOP
		if i>10 then
			exit;
		end if;
		dbms_output.put_line(i);
		i := i+1;
	END LOOP;
END;

1 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed

Let's take one more example where we will be using the exit when statement to break out of the loop.

set serveroutput on;

DECLARE
	i int;
BEGIN
	i := 0;
	LOOP
		i := i+2
		dbms_output.put_line(i);
		exit WHEN x > 10
	END LOOP;
END;

2 4 6 8 10 PL/SQL procedure successfully completed

PL/SQL: While Loop

It is an entry controlled loop which means that before entering in a while loop first the condition is tested, if the condition is TRUE the statement or a group of statements get executed and if the condition is FALSE the control will move out of the while loop.

Syntax:

WHILE <test_condition> LOOP
	<action>
END LOOP;

Let's see the while loop in action in the code example below.

Time for an Example!

Below we have a simple program to print the odd numbers between 1 to 10 using the while loop.

set serveroutput on;

DECLARE
	num int:=1;
BEGIN
	while(num <= 10) LOOP
		dbms_output.put_line(''|| no);
		num := num+2;
	END LOOP;
END;

1 3 5 7 9 PL/SQL procedure successfully completed.

NOTE: You must write the code to increment the value of the variable that you put in the condition otherwise the value of the variable will remain the same and the condition will always remain true.

PL/SQL: For Loop

This loop is used when some statements in PL/SQL code block are to be repeated for a fixed number of times.

When we use the for loop we are supposed to define a counter variable which decides how many time the loop will be executed based on a starting and ending value provided at the beginning of the loop.

The for loop automatically increments the value of the counter variable by 1 at the end of each loop cycle.

The programmer need not have to write any instruction for incrementing or decrementing value.

Syntax:

FOR counter_variable IN start_value..end_value LOOP
	statement to be executed
END LOOP;

Let's see the for loop in action in the example below.

Time for an Example!

In the example below we have used a the for loop to print numbers from 1 to 10.

set serveroutput on;
DECLARE
	i number(2);
BEGIN
	FOR i IN 1..10 LOOP
		dbms_output.put_line(i);
	END LOOP;
END;

1 2 3 4 5 6 7 8 9 10 PL/SQL procedure successfully completed.

Now. let's take another example where we will print the number in reverse order.

set serveroutput on;
DECLARE
	i number(2);
BEGIN
	FOR i IN REVERSE 1..10 LOOP
		dbms_output.put_line(i);
	END LOOP;
END;

10 9 8 7 6 5 4 3 2 1 PL/SQL procedure successfully completed.

In the above program, the REVERSE keyword is used to print the numbers in reverse order.