PL/SQL While Loop

Last Updated : 30 Jan, 2026

A WHILE loop in PL/SQL is used to run a block of code again and again as long as a given condition is true. It checks the condition before every iteration and stops when the condition becomes false, making it useful when the number of executions is not known in advance.

  • Executes the code repeatedly while the condition is TRUE.
  • Stops automatically when the condition becomes FALSE.
  • Uses EXIT or EXIT WHEN to stop the loop early if needed.

Syntax:

WHILE condition
LOOP
   -- Statements to be executed as long as the condition is true
END LOOP;
  • Condition – If it is TRUE, the loop runs; if FALSE or NULL, the loop stops.
  • LOOP – Starts the loop code.
  • END LOOP – Ends the loop.

Examples of PL/SQL WHILE Loop

Let's look at some examples to better understand how to use the WHILE loop effectively in different scenarios.

Example 1: Using PL/SQL WHILE Loop for Iterative Execution

This example shows how a PL/SQL WHILE loop runs repeatedly by increasing a counter until a given condition is met.

DECLARE
   counter NUMBER := 1;
BEGIN
   WHILE counter <= 5 
   LOOP
      DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
      
      counter := counter + 1;
   END LOOP; 
END;
/
  • DECLARE: Used to declare variables (counter is set to 1).
  • BEGIN: Starts the program.
  • WHILE: Runs the loop while counter <= 5.
  • LOOP: Starts the loop block.
  • DBMS_OUTPUT.PUT_LINE: Prints the value of counter.
  • Increment Counter: Increases counter by 1 each time.
  • END LOOP: Ends the loop.
  • END: Ends the program.

Output:

Statement processed.
Counter value: 1
Counter value: 2
Counter value: 3
Counter value: 4
Counter value: 5

Example 2: Using PL/SQL WHILE Loop with a Conditional Check

This example shows a PL/SQL WHILE loop that keeps adding numbers until the total sum becomes 10 or more.

DECLARE
   total_sum NUMBER := 0;
   current_number NUMBER := 1;
BEGIN
   WHILE total_sum < 10
   LOOP
      total_sum := total_sum + current_number;

      DBMS_OUTPUT.PUT_LINE('Current Number: ' || current_number);
      DBMS_OUTPUT.PUT_LINE('Total Sum: ' || total_sum);

      current_number := current_number + 1;
   END LOOP;
END;

Output:

Statement processed.
Current Number: 1
Total Sum: 1
Current Number: 2
Total Sum: 3
Current Number: 3
Total Sum: 6
Current Number: 4
Total Sum: 10
  • The loop condition total_sum < 10 controls termination.
  • No EXIT WHEN is required because the condition is checked before each iteration.
  • The loop stops automatically once total_sum reaches 10.

Important Points About PL/SQL While Loop

The WHILE loop in PL/SQL repeatedly executes a block of code as long as a specified condition remains true.

  • A WHILE loop has no built-in counter, so the counter must be incremented manually.
  • If the loop condition never becomes false, the loop will run indefinitely.
  • In nested loops, the EXIT statement terminates only the innermost (current) loop.
  • EXIT or EXIT WHEN can be used to terminate the loop early based on a condition.
Comment

Explore