Oracle WHILE LOOP Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

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.

See also: