In Oracle PL/SQL there are different loop statements to repeat a block of code until a certain condition is met to exit the loop. Loop…End Loop, While Loop…End Loop and For Loop … End Loop.

In this article, we will discuss PL/SQL WHILE loop syntax, which involves variable declarations, loop initiation, condition definition, and an optional EXIT WHEN statement for controlled termination. This structure allows developers to create flexible and dynamic iterations tailored to specific programming needs.

WHILE Loop Statement

In PL/SQL, the WHILE loop statement is a control structure statement that repeatedly executes a code block that is inside the ‘While Loop’ as long as the specific condition set in the ‘While Loop’ is TRUE. The condition set in the ‘WHILE’ statement is a boolean expression that evaluates to TRUE, FALSE, or NULL. To terminate the loop prematurely or based on some specific scenario then the EXIT or EXIT WHEN statement is used.

The ‘WHILE LOOP’ is used when you are not sure about the number of times the code block needs to execute. Only during the WHILE Loop execution, the specific condition set to end the execution is made TRUE and the control moves out of the WHILE Loop statement.

Syntax:

WHILE condition

LOOP

— Statements to be executed as long as the condition is true

END LOOP;

Examples of PL/SQL WHILE Loop

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

In this example, we delve into the usage of the PL/SQL WHILE loop for iterative execution. The code demonstrates a basic scenario where a counter variable is initialized and incremented within the loop, with the loop executing until a specified condition is met.

DECLARE
   counter NUMBER := 1;  -- Initialize a counter variable
BEGIN
   -- Start the WHILE loop
   WHILE counter <= 5  -- Condition to check
   LOOP
      -- Statements to be executed as long as the condition is true
      DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
      
      -- Increment the counter
      counter := counter + 1;
   END LOOP;  -- End of the loop
END;
/

Explanation:

• DECLARE: This section is used to declare variables. In this example, we declare a variable named counter and initialize it to 1.
• BEGIN: Marks the beginning of the executable section.
• WHILE Loop: The WHILE loop is used to repeatedly execute the block of statements as long as the specified condition (counter <= 5) is true.
• LOOP: Marks the beginning of the loop block.
• DBMS_OUTPUT.PUT_LINE: This statement is used to display output in the console. In this example, it prints the current value of the counter variable.
• Increment Counter: The counter variable is incremented by 1 in each iteration.
• END LOOP: Marks the end of the loop block.
• END; Marks the end of the executable section.

In the above example, the WHILE loop will iterate as long as the counter is less than or equal to 5.

Output:

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

Example 2: WHILE Loop Example Terminated by EXIT WHEN Statement

This example illustrates the use of a PL/SQL WHILE loop with the EXIT WHEN statement, showcasing a scenario where the loop iterates until a certain condition is met. The code calculates the sum of numbers until the total sum reaches or exceeds 10.

DECLARE
   total_sum NUMBER := 0;  -- Initialize a variable to store the sum
   current_number NUMBER := 1;  -- Initialize a variable for the loop
BEGIN
   -- Start the WHILE loop with EXIT WHEN statement
   WHILE total_sum < 10
   LOOP
      -- Add the current number to the total sum
      total_sum := total_sum + current_number;

      -- Display the current state
      DBMS_OUTPUT.PUT_LINE('Current Number: ' || current_number);
      DBMS_OUTPUT.PUT_LINE('Total Sum: ' || total_sum);
      
      -- Increment the current number
      current_number := current_number + 1;

      -- Exit the loop when the total sum exceeds or equals 10
      EXIT WHEN total_sum >= 10;
   END LOOP;  -- End of the loop
END;
/

In the above example, the loop continues as long as the total_sum is less than 10. The loop adds the current_number to the total_sum in each iteration and increments the current_number. The loop will exit when the total_sum becomes greater than or equal to 10.

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

Conclusion

In conclusion, the WHILE loop in PL/SQL provides a powerful control structure for executing a block of statements repeatedly as long as a specified condition holds true. This WHILE loop structure allows for flexible control flow within a PL/SQL program, enabling us to perform iterative tasks efficiently. A better understanding of how to effectively use the WHILE loop is essential for writing efficient and readable PL/SQL code. This is true especially when dealing with scenarios that require repetitive execution until a specific condition is met.