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
PL/SQL While Loop
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.
Contact Us