PL/SQL While Loop

Last Updated : 22 Oct, 2024

Oracle PL/SQL provides various loop structures that help developers execute a block of code multiple times based on certain conditions. The main loop structures include LOOP ... END LOOP, WHILE ... END LOOP, and FOR ... END LOOP. In this article, we will explore the WHILE loop in detail, including its syntax, usage scenarios, and examples. We'll also discuss controlling the loop flow using statements like EXIT and EXIT WHEN.

What is a WHILE Loop in PL/SQL?

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;

condition: A Boolean expression that determines whether the loop will continue executing. If it evaluates to TRUE, the loop executes the block of code inside. If FALSE or NULL, the loop exits.
LOOP: Marks the beginning of the code block to be executed.
END LOOP: Marks the end of the loop block.

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

In this example, we look 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: Using EXIT WHEN for Loop Termination

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; /

Explanation:

The loop continues as long as total_sum is less than 10.
total_sum is updated by adding current_number in each iteration.
The EXIT WHEN statement exits the loop when total_sum reaches or exceeds 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

Important Points About PL/SQL While Loop

Unlike some programming languages, there is no built-in loop counter in a WHILE loop, so any counter must be manually incremented.
If the loop condition does not eventually evaluate to FALSE, the loop will continue indefinitely, potentially causing the program to hang.
When using nested WHILE loops, an EXIT statement only exits the current loop. To exit multiple nested loops, you would need to use multiple EXIT statements.
The EXIT or EXIT WHEN statements can be used to terminate the loop prematurely, providing more control over the loop execution.

Swift - While Loop

julietmaria

Improve

Article Tags :