Updated June 8, 2023
Introduction to PLSQL While Loop
PLSQL while loop is the statement which can be used to execute the block of code containing one or more statements in it multiple times that is repetitively. We can run the number of statements multiple times with the use of while loop and specifying a condition which can evaluate to either true, false or NULL. As long as the condition specified in the argument of while loop evaluates to true the block of code is been executed repetitively but as soon as condition becomes either false or NULL the loop stops its execution and the execution of while loop terminates.
In this article, we will look at the syntax, working, flowchart and implementation of while loop in PL/ SQL along with the help of certain examples.
Syntax of PLSQL While Loop
The syntax of the while loop is as shown below –
WHILE condition_for_evaluation
LOOP
// block of code containing statements
END LOOP;
In the above syntax the terminologies used are described one by one in detail –
- Condition_for_evaluation: This is the condition to be specified inside the brackets after writing the keyword WHILE. This condition evaluates to a Boolean a NULL value which means there should be three possible values after execution of this condition which are TRUE, FALSE or NULL.
- Working and use of condition: Until and unless this condition does become true the code inside the while loop is not executed. If in the beginning itself the condition becomes false or NULL then while loop is not even executed single time which is a different behavior as compared to other loops whose body executes at least once. Before execution of the body inside the while each time the condition is being evaluated first, if it results true only then the body is executed.
- Block of Code Containing Statements: This forms the body of the while loop which contains the portion that we want to execute repetitively. It may contain a single or multiple statements that form the block. They are enclosed between the beginning clause of LOOP and ending clause of END LOOP statements.
Note: In case if you want to come out of the WHILE loop execution inside its body prematurely that is before the execution of the block is being done for expected number of times, you can make the use of EXIT WHEN or EXIT clauses to do so.
Flow chart: The execution and flow of control of program in while loop can be understood by observing the below diagram of flow chart –
As we can see the program begins and can execute the other statements that we have specified before while loop condition. Once, it comes at while loop statement, it evaluates that condition. IF the condition is true then it executes the statements inside the block after that it again goes for checking the same condition and evaluating the condition if gives true executes the block. This process continues till the condition which is specified does noy becomes false. Once the condition that is specified in while evaluates to false the flow of execution is transferred to the line which is outside the while loop, if any statements are present in the program after END LOOP clause they are executed and the execution of the program is stopped there itself.
Examples of PLSQL While Loop
Let us now understand the implementation of while loop in PL/ SQL with the help of certain examples.
Example #1
Let us try to print the value of variable named countOfLoop which will be initialized at 51 value. The value of this variable will be increased in each of the execution of the while loop body. This means that the numbers that will print will be in sequence. They will start at 51 and end at 60 as we will put a condition saying that the value of this variable should be less than or equal to 60. Hence, the body inside the while loop will execute for 10 times till condition evaluates to true. Our program in PL/ SQL will be as shown below –
DECLARE
countOfLoop NUMBER := 51;
BEGIN
WHILE countOfLoop <= 60
LOOP
DBMS_OUTPUT.PUT_LINE( 'My count of loop value : ' || countOfLoop );
countOfLoop := countOfLoop + 1;
END LOOP;
END;
The output of execution of above PL/ SQL program prints the numbers starting from 51 to 60 by using the while loop
In the above example
- The variable is initialized to value of 51.
- The value of variable is incremented inside the loop body by one each time.
- The condition evaluates to true till value of variable is between 51 to 60.
- When the variable will have the value 61 then condition will become false and hence the execution of loop will stop.
Example #2
Let us now consider an example where we will try to understand the usage of EXIT WHEN clause for premature termination of execution of while loop. We will consider the same example of printing the value of counter variable. But here, we will add an additional clause inside the while loop which will specify that if the value of the loop counter variable is 53 then the while loop execution should be terminated there itself. Our PL/ SQL program to demonstrate this behavior can be as shown below –
DECLARE
count_of_loop NUMBER := 51;
BEGIN
WHILE count_of_loop <= 60
LOOP
DBMS_OUTPUT.PUT_LINE( My count of loop value : ' || count_of_loop );
count_of_loop := count_of_loop + 1;
EXIT WHEN count_of_loop = 53;
END LOOP;
END;
The execution of above program gives the following output which shows that the while loop has terminated its execution when value of variable counter became 53 as we can see only two values one for 51 and other for 52.
Conclusion
The WHILE loop in PL/ SQL is used to execute the block of code containing multiple or single statements in it repetitively based on certain condition whose evaluated value is considered as the factor to determine number of looping executions. This condition can evaluate to Boolean value of NULL. The EXIT WHEN and EXIT clause are used for pre mature termination of loop execution.
Recommended Articles
This is a guide to PLSQL While Loop. Here we also discuss the introduction and syntax of PLSQL While Loop along with different examples and its code implementation. You may also have a look at the following articles to learn more –
