Press "Enter" to skip to content

Oracle WHILE LOOP Example

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:

Vinish Kapoor

I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me, it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub and get notifications for new posts.

You may also like:

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *