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:
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.
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:
LOOPand ends with a keyword
exitstatement or by specifying certain condition by using the statement
LOOP sequence of statements END LOOP;
Let's see the simple loop in action in the code example below.
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
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.
WHILE <test_condition> LOOP <action> END LOOP;
Let's see the while loop in action in the code example below.
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.
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.
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.
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.