Iterative Control in PL/SQL

Types of Iterative Control in PL/SQL

There are three main types of loops in PL/SQL iterative Statements

• Basic loop
• WHILE loop
• FOR loop

Basic loop

The Basic LOOP executes a block of code repeatedly until an explicit EXIT condition is met. It does not have a predefined iteration limit and must be manually controlled using EXIT WHEN.

Syntax:

LOOP
Statements;
[increment_statement]
EXIT condition;
END LOOP;

Explanation:

The basic loop will execute the statement provided a certain number of times until the exit condition is met. It is necessary to have an EXIT statement so that the loop does not run indefinitely. There is also an increment statement that can be used to increase/decrease the changing variable in the loop.

Example:

DECLARE 
    i NUMBER(3) := 1;
BEGIN 
    LOOP 
        DBMS_OUTPUT.PUT_LINE(i);
        i:=i+1;
        IF(i>10) THEN 
            EXIT;
        END IF;
    END LOOP;
END;

The output at the SQL prompt will be:

1
2
3
4
5
6
7
8
9
10

Initially, we declare a variable i with a value equal to 1. Once it enters the loop block, the value of i is displayed using the DBMS_OUTPUT.PUT_LINE(). The i value is then incremented by 1. So when the i value was 1, it will change to 2 after incrementing by 1.( i=i+1). The loop continues until the exit condition is true. The exit condition in the above example is if i>10. So when the i value was 10, and it was displayed in the prompt, the i value now changes to 11 after incrementing by 1. Since the i value is greater than 10, therefore the exit condition is true, and hence further execution of the loop is stopped, and the control goes to the end of the loop.

Instead of using IF statement, we could have used the WHEN clause to exit.

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

WHILE loop

The WHILE LOOP executes a block of code as long as the given condition remains TRUE. The condition is checked before each iteration, and the loop stops when it becomes FALSE.

Syntax:

WHILE (boolean_expression) LOOP
statements ;
[increment_statement]
END LOOP;

Explanation:

The WHILE loop in PL/SQL  is used to check the entry condition, and if the entry condition is true, only then is the loop executed. The basic loop executes at least once, whereas the WHILE loop will first check the condition provided in the boolean expression. If the condition is false, the control does not enter the loop.

Example:

DECLARE  
  i NUMBER(4) := 10;  
BEGIN  
  WHILE (i <= 100)
  LOOP    
    DBMS_OUTPUT.PUT_LINE(i);   
    i := i+10;
  END LOOP;  
END;

The output at the SQL prompt will be:

10
20
30
40
50
60
70
80
90
100

In the above example, the i value is set to 10 initially. When the loop begins, the WHILE loop will always check whether the value of i is less than or equal to 100. Only if the condition is true, then the control will enter the loop. Every time the i value is incremented by 10. In the last iteration, when the i value becomes 100, it will be displayed in the prompt. Now the i value becomes 100+10=110. Since 110 is not less than or equal to 100, the control will not enter the loop, and hence the loop terminates.

We can use CONTINUE and EXIT statements in the WHILE loop, just as we did in the basic loop.

Also Read, DCL Commands in SQL and Tcl Commands in SQL

FOR loop

The FOR LOOP executes a block of code a fixed number of times based on a specified range. It automatically increments the loop variable in each iteration.

Syntax:

FOR variable IN range LOOP
Statements;
END LOOP;

Explanation:

The for loop in PL/SQL provides implicit variable declaration, implicit incrementation of the variable by one, and implicit exit also. In the FOR loop, we do not have to declare the variable as we did in the previous two types of loop. While writing the loop statement, the variable is declared implicitly. The range consists of the starting value, from where the value of the iterating variable begins, and the end value, which determines the last value that the variable can have. In each loop, the variable is incremented by one.

Example:

BEGIN
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

The output at the SQL prompt will be:

1
2
3
4
5
6
7
8
9
10

Here we did not declare the variable i explicitly. The range is provided with the starting value followed by two dots and then the ending value. Just like the previous types of loop, we can use the CONTINUE and EXIT statements in the FOR loop.

Example:

BEGIN
    FOR i IN 1..10 LOOP
        IF i=5 THEN
            CONTINUE;
        END IF;
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

The output at the SQL prompt will be:

1
2
3
4
6
7
8
9
10

The FOR loop can also be used to display the output in reverse order. For example, if we want to display the numbers from 10 to 1 instead of 1 to 10, we use the REVERSE keyword. 

Example:

BEGIN
    FOR i IN REVERSE 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

It must be observed here that the range provided is not 10 to 1, but 1 to 10. The range is always provided from low to high. The output of the above code will be:

10
9
8
7
6
5
4
3
2
1

Frequently Asked Questions

How to do iterations in SQL?

Iterations in SQL are done using cursors, recursive queries (WITH RECURSIVE), and procedural constructs like LOOP, WHILE, and FOR loops in PL/SQL or T-SQL.

What are the three main types of iterative structures?

The three main iterative structures are LOOP (basic loop with EXIT condition), WHILE LOOP (executes while a condition is TRUE), and FOR LOOP (fixed iterations).

What is looping in PL/SQL?

Looping in PL/SQL allows executing a block of code repeatedly using LOOP, WHILE, or FOR, until a specified condition is met, improving automation.

How to use CASE in PL/SQL?

The CASE statement in PL/SQL is used for conditional logic inside SELECT, WHERE, or procedural blocks to execute different actions based on conditions.

Example:

DECLARE num NUMBER := 5; 
BEGIN 
 CASE  
   WHEN num > 0 THEN DBMS_OUTPUT.PUT_LINE('Positive');  
   WHEN num < 0 THEN DBMS_OUTPUT.PUT_LINE('Negative');  
   ELSE DBMS_OUTPUT.PUT_LINE('Zero');  
 END CASE; 
END;

Conclusion

• Iterative statements are used to repeat the execution of a statement a certain number of times.
• Three types of loop in PL/SQL are Basic loop, FOR loop, WHILE loop.
• The basic loop will execute the statement provided a certain number of times until the exit condition is met. 
• The WHILE loop is used to check the entry condition and if the entry condition is true, only then is the loop executed.
• The basic loop executes at least once.
• The for loop provides implicit variable declaration, implicit incrementation of the variable by one, and implicit exit also.

Recommended Reading:

•  Characteristics of OOPS
•  TCL Commands In SQL and SQL Clauses
• DCL Commands in SQL
• Tcl Commands in SQL