Oracle PL/SQL WHILE LOOP with Example

What is a While Loop?

In SQL, a WHILE loop is a control flow statement that allows you to repeatedly execute a block of SQL statements as long as a certain condition is met.

WHILE condition LOOP   
    -- code to execute while condition is true
END LOOP;

When the WHILE loop is executed, the condition is evaluated at the beginning of each iteration. If the condition is true, then the statements in the loop body are executed. Once the statements in the loop body are executed, the condition is evaluated again. If the condition is still true, the statements in the loop body are executed again, and so on, until the condition becomes false. At that point, control passes to the next statement after the END statement.

Example:

In this example, we are going to print numbers from 1 to 10 using the WHILE loop statement. For that, we will execute the following code.

DECLARE @i INT = 1;  

WHILE @i <= 10
BEGIN
    PRINT 'The value of i is ' + CAST(@i AS VARCHAR(2));    
    SET @i = @i + 1; 
END

This WHILE loop will execute the block of code between the BEGIN and END statements as long as the variable @i is less than or equal to 10. Inside the loop, the PRINT statement will display the value of @i on each iteration. The CAST function is used to convert the integer value of @i to a string so that it can be concatenated with the text string in the PRINT statement.

Output:

Summary

The WHILE loop is used to iterate through the values from 1 to 10. Inside the loop, the PRINT statement displays the current value of the @counter variable, and the SET statement increments the value of the @counter variable by 1. The loop continues to execute until the @counter variable reaches 11, at which point the loop terminates.