Do you think IIT Guwahati certified course can help you in your career?
No
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.
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:
What are iterative statements in PL/SQL? Iterative statements are used to repeat the execution of a statement a certain number of times.
What are the various types of loops in PL/SQL? Basic loop, FOR loop, WHILE loop.
Why is the CONTINUE keyword used in PL/SQL? The CONTINUE keyword is used to skip the current iteration in the loop.
Why is the EXIT keyword used in PL?SQL? The EXIT keyword is used to terminate the loop.
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.