Table of contents
1.
Introduction
2.
Basic loop
3.
WHILE loop
4.
FOR loop
5.
FAQs
6.
Key takeaways
Last Updated: Mar 27, 2024

PL/SQL Iterative Statements

Author ANKIT KUMAR
1 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

The iterative statements are used to repeat the execution of certain statements multiple times. This is achieved with the help of loops. Loops in PL/SQL provide a mechanism to perform specific tasks multiple times without having to write them multiple times. 

This article will discuss three main types of loops:

  • Basic loop
  • WHILE loop
  • FOR loop

We shall see the syntax of each of the above loops and an example of it.

Basic loop

Syntax:

LOOP

Statements;

[increment_statement]

EXIT condition;

END LOOP;

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

Syntax:

WHILE (boolean_expression) LOOP

statements ;

[increment_statement]

END LOOP;

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

Syntax:

FOR variable IN range LOOP

Statements;

END LOOP;

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 which 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 statement 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

Also Read - TCL Commands In SQL and SQL Clauses

FAQs

  1. What are iterative statements in PL/SQL?
    Iterative statements are used to repeat the execution of a statement a certain number of times.
     
  2. What are the various types of loops in PL/SQL?
    Basic loop, FOR loop, WHILE loop.
     
  3. Why is the CONTINUE keyword used in PL/SQL?
    The CONTINUE keyword is used to skip the current iteration in the loop.
     
  4. Why is the EXIT keyword used in PL?SQL?
    The EXIT keyword is used to terminate the loop.
     
  5. What is the difference between the basic loop and the WHILE loop?
    The basic loop executes at least once, whereas the WHILE loop execution depends on the boolean expression.

Take this awesome course from coding ninjas.

Key takeaways

  • 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.

Never stop learning. Explore the top 100 SQL problems here.

Recommended Reading: Characteristics of OOPS

Happy learning!

Live masterclass