Loops in Oracle

What is Iterative Statements in PL/SQL?

An iterative control Statements are used when we want to repeat the execution of one or more statements for specified number of times.

Scenario:-

You want to execute some statement multiple times, sometimes you know how many times, sometimes you don’t know how many times your statement should get executed or you want to execute statement at least one time. To do this kind of work, you would need to use loops. Loop will help you to execute your statement depending upon your requirement. There are different types of loops. You need to choose which one is best suitable for your requirement.

There are three types of loops in PL/SQL:-
 

         1) Simple loop

     2)  For loop

·   3) While loop

Let’s discuss, loop’s in detail:-

·                ·    Simple Loop

A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

The General Syntax to write a simple loop is:

LOOP 

   STATEMENTS; 

   EXIT; 

   {OR EXIT WHEN CONDITION ;}

END LOOP; 

Example of Simple Loop:

·          While Loop

 

A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false. If while condition is not true, loop will not execute even once.

The General Syntax to write a WHILE LOOPS is:-

WHILE <CONDITION> 

 LOOP STATEMENTS; 

END LOOP; 

Example of While loop:-

·         For loop

A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer.
The General Syntax to write a FOR LOOP is:

FOR COUNTER IN VAL1 (MIN_VALUE)..VAL2 (MAX_VALUE) 

  LOOP STATEMENTS; 

END LOOP;

Example of for loop:

Another example is:-

Read Also:-  Using Cursor