Iterative Statements/Loops In PL/SQL
Concept of While Loop is not new to the programming world and almost all the programming languages support this concept. While loop, is not the exception to other loops. It also executes block of statements several times but this loop is best usable when number of iterations to be performed are unknown.
Syntax
WHILE condition LOOP
Statement 1;
Statemen 2;
…
Statement 3;
END LOOP;
The keyword WHILE marks the beginning of the loop followed by word CONDITION which will serve your test condition. This will get evaluated either to be true or to be false and at the end of our first line we have another keyword which is LOOP. The statements 1 through N are sequence of executable statements which define the body of the loop. And at the end we have a reserved phrase END LOOP which indicates the ending of the while loop.
In order to execute the body of the loop the test condition needs to be true. If this condition is evaluated to be true then the control will jump inside the loop and execute whatever statements it has. This iteration will continue until the test condition becomes false. As soon as the test condition is evaluated to be false the control will come out of the loop and execute the statement which immediately follows the loop.
Examples
Example 1
DECLARE
 v_counter NUMBER :=1;
 v_result NUMBER ;
BEGIN
 WHILE v_counter <= 10
LOOP
 v_result := 9 *v_counter;
DBMS_OUTPUT.PUT_LINE(‘9’||’ x ‘||v_counter||’ = ‘||v_result);
 v_counter := v_counter+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘out’);
END;
/
In this example we have declared two variables – v_counter which will serve as a counter and variable v_result which will hold the result of multiplication.
Down in the execution section we have our while loop. The first statement inside the loop body is an arithmetic expression which will perform the multiplication of our table and will store the result in v_result variable.
2nd Statement is an output statement which will print the result of the multiplication in a formatted manner.
And the third statement is an update statement which will update the counter with each iteration.
This while loop will keep on iterating until the counter is less than 10 or it becomes equal to 10. Once the value of the counter becomes 10 the while loop will terminate and execute the first statement immediately outside the loop body.
Example 2 Boolean Expression
DECLARE
 v_test   BOOLEAN := TRUE;
 v_counter NUMBER := 0;
BEGIN
 WHILE v_test LOOP
 v_counter := v_counter+1;
DBMS_OUTPUT.PUT_LINE( v_counter );
 IF v_counter = 10 THEN
 v_test   := FALSE;
 END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE (‘This Statement is outside the loop and will always execute’);
END;
With the Boolean expression in loop we have to write the code which will change its value to false and terminate the loop. Failing to do so can make your loop an infinity loop. In the above program the simple IF THEN block inside the loop body will change the value of the Boolean expression v_test and set it on false when counter becomes equal to 10 this till terminate the loop and bring the control over the first statement immediately outside the loop body.
That’s all about While Loop. Hope you found this article helpful. Kindly please share it on your social networking and help me reach out to more people. Thanks & have a fantastic day!
