Summary: in this tutorial, you will learn how PL/SQL WHILE loop statement to execute a sequence of statements based on a condition that is checked at the beginning of each iteration.
Introducing to PL/SQL WHILE Loop
Sometimes, you don’t know in advance how many times a sequence of statements needs to execute because the execution depends on a condition which is not fixed at the compiled time. In such cases, you should you PL/SQL
WHILE LOOP statement.
The following illustrates the PL/SQL
WHILE LOOP syntax:
WHILE condition LOOP sequence_of_statements; END LOOP;
condition is a Boolean variable or expression that evaluates to a Boolean values of
NULL. The condition is checked at the beginning of each iteration.
- If the
- If the
NULL, the loop terminates and control is passed to the next executable statement following the
It is important to note that the sequence of statements may not execute even a single time if the
condition evaluates to
NULL before entering the loop.
In addition, inside the loop you have to update some variables to make the
condition becomes to
NULL at some points to terminate the loop, otherwise you will have an endless loop.
WHILE loop is effective when you don’t know how many times the loop will execute. If the number of iteration is predetermined, you should use the PL/SQL FOR loop statement instead.
The following flowchart illustrates the PL/SQL WHILE loop statement:
Example of using PL/SQL WHILE LOOP
In this example, we will calculate factorial of 10 by using PL/SQL
WHILE LOOP statement. Here is the code sample of the example:
SET SERVEROUTPUT ON SIZE 1000000; DECLARE n_counter NUMBER := 10; n_factorial NUMBER := 1; n_temp NUMBER; BEGIN n_temp := n_counter; WHILE n_counter > 0 LOOP n_factorial := n_factorial * n_counter; n_counter := n_counter - 1; END LOOP; DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp || ' is ' || n_factorial); END; /
In this tutorial, you’ve learned how to use another iterative statement called PL/SQL WHILE loop statement to execute a sequence of statements based on a condition that is checked before each iteration of the loop.