While Loop in Oracle PL/SQL with Example

By : Fiona Brown Fiona Brown
Hours Updated

⚡ Smart Summary

Oracle PL/SQL WHILE Loop repeats a block of code while a condition remains true. It is an entry-checking loop, testing the exit condition before each iteration, so the block may run zero times, and it needs no explicit EXIT keyword.

  • 🔁 Purpose: A WHILE loop runs while a condition is true and stops as soon as it is false.
  • 🚦 Entry Check: The condition is tested before the block, so the loop can run zero times.
  • 🧮 No EXIT Needed: The condition is validated implicitly each iteration, so no explicit EXIT is required.
  • 🔢 Counter: A variable inside the block must change so the condition eventually becomes false.
  • ♾️ Infinite Risk: If the counter never fails the condition, the loop runs forever.
  • ⚖️ vs FOR: Use WHILE when the iteration count is unknown, and FOR when it is fixed.
  • 🚪 Early Exit: EXIT WHEN can still leave a WHILE loop before the condition turns false.
Read More

Oracle PL/SQL While Loop

What is a PL/SQL While Loop?

The While Loop in PL/SQL works similarly to the basic loop statement, except that the exit condition is at the very beginning of the loop. It works like an entry-checking loop, where the execution block runs only if the condition is satisfied, because the exit condition is checked before execution.

It does not explicitly require the ‘EXIT’ keyword to exit the loop, since it validates the condition implicitly each time.

PL/SQL While Loop Syntax

WHILE <EXIT condition>
LOOP
<execution block starts>
.
.
.
<execution_block_ends>
END LOOP;
  • The keyword ‘WHILE’ marks the beginning of the loop and ‘END LOOP’ marks the end.
  • The exit condition is evaluated each time before the execution part starts executing.
  • The execution block contains all the code that needs to run.
  • The execution part can contain any executable statement.

Example of Oracle PL/SQL While Loop

In this example, we print numbers from 1 to 5 using a WHILE loop statement.

PL/SQL While Loop Example
PL/SQL While Loop Example
DECLARE
a NUMBER := 1;
BEGIN
dbms_output.put_line('Program started');
WHILE (a <= 5)
LOOP
dbms_output.put_line(a);
a:= a+1;
END LOOP;
dbms_output.put_line('Program completed');
END;
/

Code Explanation:

  • Code line 2: Declaring the variable ‘a’ as ‘NUMBER’ and initializing it with value ‘1’.
  • Code line 4: Printing the statement “Program started”.
  • Code line 5: The keyword ‘WHILE’ marks the beginning of the loop and checks whether the value of ‘a’ is less than or equal to 5.
  • Code line 7: Prints the value of ‘a’.
  • Code line 8: Increments the value of ‘a’ by 1.
  • Code line 9: The keyword ‘END LOOP’ marks the end of the execution block.
  • Lines 7 and 8 continue to execute until ‘a’ reaches the value 6, at which point the condition returns FALSE and control exits the loop.
  • Code line 10: Printing the statement “Program completed”.

RELATED ARTICLES

WHILE Loop vs FOR Loop vs Basic Loop

PL/SQL offers three loop types, and choosing the right one keeps code clear. The WHILE loop suits a condition-driven repeat, while the other two suit a fixed count or an explicitly controlled exit.

Loop Exit criteria Best used when
WHILE loop Condition at the top becomes false The iteration count is unknown
FOR loop Counter reaches the range end The iteration count is known
Basic loop Explicit EXIT in the block The exit is controlled manually

Because the WHILE loop tests its condition at the top, the body may run zero times if the condition is false from the start. This is the key difference from a basic loop, whose body always runs at least once.

Avoiding an Infinite While Loop

A WHILE loop depends entirely on the controlling variable moving toward the exit condition. If it never does, the loop runs forever and the session hangs. Three habits prevent this:

  • Change the counter every iteration. The example increments ‘a’ inside the loop; removing that line would loop forever.
  • Test the boundary. Confirm the condition can actually become false for the starting value, not just for an ideal case.
  • Add a safety EXIT WHEN. For a data-driven loop, an EXIT WHEN on a maximum iteration count acts as a guard against an unexpected value.

When the number of repetitions is genuinely fixed, a FOR loop removes the risk entirely, because its counter is managed automatically.

FAQs

Yes. Because the condition is checked before the block, if it is false at the start the body never runs. This entry check is the main difference from a basic loop, which always runs once.

Make sure a variable inside the loop changes toward the exit condition every iteration. Adding an EXIT WHEN on a maximum count is a useful safety net for data-driven loops.

Use WHILE when the number of iterations is not known and depends on a condition. Use FOR when you loop over a fixed range, since its counter is managed automatically.

Often. AI can check whether the controlling variable moves toward the exit condition and warn when it does not. A loop whose exit depends on live data still needs a test to be certain.

Yes. Although a WHILE loop exits when its top condition turns false, an EXIT or EXIT WHEN inside the body can leave it early, which is handy for breaking out on a special case.

Summarize this post with: