In Oracle PL/SQL, WHILE LOOP statement executes the code written between WHILE LOOP and END LOOP until the condition is true. Below I am giving some examples for Oracle WHILE LOOP statement.
Syntax
WHILE logical_condition LOOP -- some PL/SQL code END LOOP;
Oracle WHILE LOOP Examples
1. Loop 10 Times and Print The Table
In the following example, WHILE LOOP will execute the statements until the value of n_num variable is less than or equal to 10. it will print the table of 2 by incrementing the value of n_num variable with 1 for each iteration of the loop.
SET SERVEROUTPUT ON;
DECLARE
n_num NUMBER;
n_table number := 2;
BEGIN
n_num := 1;
WHILE n_num <= 10
LOOP
DBMS_OUTPUT.put_line ('2 x ' || n_num || ' = '||(n_table * n_num));
n_num := n_num + 1;
END LOOP;
END;
/Output:
2 x 1 = 2 2 x 2 = 4 2 x 3 = 6 2 x 4 = 8 2 x 5 = 10 2 x 6 = 12 2 x 7 = 14 2 x 8 = 16 2 x 9 = 18 2 x 10 = 20 PL/SQL procedure successfully completed.
Don't forget to increment the value of n_num variable, so that it can reach upto 10, else it will be infinite loop.
2. To Loop Until The Boolean Variable Value is TRUE
In the following example, it will loop until the b_run boolean variable value is TRUE and will print the n_num variable value by increment it with 1 for each iteration and when the value of n_num variable is greater than 5 then will set the b_run variable to FALSE, so that WHILE LOOP can finish its job.
SET SERVEROUTPUT ON; DECLARE n_num NUMBER; b_run BOOLEAN := TRUE; BEGIN n_num := 1; WHILE b_run LOOP DBMS_OUTPUT.put_line (n_num || ' Times'); n_num := n_num + 1; IF n_num > 5 THEN b_run := FALSE; END IF; END LOOP; END; /
Output:
1 Times 2 Times 3 Times 4 Times 5 Times PL/SQL procedure successfully completed.
For this also, don't forget to set b_run variable value to FALSE, else it will be infinite loop. You can also write the exit; instead of b_run := FALSE; statement to exit the loop.
