Chapter 6:PL/SQL Loop

PL/SQL Loop

PL/SQL Loop Basic Loop, FOR Loop, WHILE Loop repeat a number of block statements in your PL/SQL program. These are also known as iterative control statements.

Types of PL/SQL Loops

There are 3 types of PL/SQL Loops.

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

Syntax for a basic loop:

LOOP
sequence_of_statements;
END LOOP;

Example,

DECLARE  
i NUMBER := 1;  
BEGIN  
LOOP  
EXIT WHEN i>7;  
DBMS_OUTPUT.PUT_LINE(i);  
i := i+1;  
END LOOP;  
END;  

result:

1
2
3
4
5
6
7

Note: You must follow these steps while using PL/SQL Exit Loop.

WHILE LOOP

Sometimes, you don’t know in advance how many times a sequence of statements needs to execute because it depends on a condition which is not fixed at compile time. In such cases, you should use PL/SQL WHILE LOOP statement.

The following illustrates the PL/SQL WHILE LOOP syntax:

WHILE <condition>   
 LOOP statements;   
END LOOP;  

Example:

DECLARE
   no NUMBER := 1;
BEGIN
WHILE no < 10 LOOP
DBMS_OUTPUT.PUT_LINE(no);
        no := no + 1;
    END LOOP; 
  END;
/

Result:

1
2
3
4
5
6
7
8
9
10

FOR Loop

PL/SQL FOR loop is an iterative statement that allows you to execute a sequence of statements a fixed number of times. Unlike the PL/SQL WHILE loop, the number of iterations of the PL/SQL FOR loop is known before the loop starts.

Syntax of for loop:

FOR counter IN initial_value .. final_value LOOP  
  LOOP statements;   
END LOOP;  

Example

BEGIN
    FOR no IN 1 .. 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration : ' || no);
    END LOOP;
END;/

Result

Iteration : 1
Iteration : 2
Iteration : 3
Iteration : 4
Iteration : 5

PL/SQL procedure successfully completed.