Loops
In SQL, loops are used to repeatedly execute a block of code as long as a certain condition is met. These loops can be useful when performing operations that require repetition, such as processing a set of rows or performing iterative calculations.
Types of Loops
1. WHILE Loop
The WHILE
loop is the most common loop in SQL and is used to execute a block of SQL statements as long as a given condition remains true.
The syntax for WHILE
loop is as follows:
WHILE condition
BEGIN
-- SQL statements to execute
END
Here’s an example to print the value of @counter
from 1 to 5 using a WHILE
loop:
DECLARE @counter INT = 1;WHILE @counter <= 5BEGINPRINT 'Counter: ' + CAST(@counter AS VARCHAR);SET @counter = @counter + 1;END
2. FOR Loop
In SQL, a FOR
loop is often used for iterating over a specific range of values. It is more common in Oracle databases than other SQL variants.
The syntax for FOR
loop is as follows:
FOR counter IN lower_bound..upper_bound
LOOP
-- SQL statements to execute
END LOOP;
Here’s an example to print the value of counter
from 1 to 5 using a FOR
loop:
BEGINFOR counter IN 1..5 LOOPDBMS_OUTPUT.PUT_LINE('Counter: ' || counter);END LOOP;END;
3. LOOP
The LOOP
structure in SQL is a powerful tool for executing a block of statements repeatedly until a specific condition is met. Unlike other loop types, a LOOP
does not inherently depend on an initial condition. Instead, it runs unconditionally and requires an explicit exit condition within the loop body to stop execution. This flexibility makes it highly versatile but demands careful implementation to avoid infinite loops.
The syntax for using LOOP
is as follows:
LOOP
-- SQL statements
IF condition THEN
EXIT;
END IF;
END LOOP;
Here’s an example to print the value of counter
from 1 to 5 using a LOOP
:
DECLAREcounter INT := 1;BEGINLOOPEXIT WHEN counter > 5;DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);counter := counter + 1;END LOOP;END;
Infinite Loops
Loops without a properly defined exit condition can lead to infinite loops, where the code continues executing indefinitely. This often occurs when the condition in a WHILE
or LOOP
structure is never met or incorrectly updated.
Here is an example of an infinite loop in SQL:
DECLARE @counter INT = 1;WHILE @counter > 0BEGINPRINT 'This loop will run indefinitely';-- No exit condition or update to @counterEND
Note: Be cautious of creating infinite loops, especially if the exit condition is not correctly defined, as it can lead to system performance degradation.
Contribute to Docs
- Learn more about how to get involved.
- Edit this page on GitHub to fix an error or make an improvement.
- Submit feedback to let us know how we can improve Docs.
Learn SQL on Codecademy
- Skill path
Analyze Data with SQL
Learn to analyze data with SQL and prepare for technical interviews.Includes 9 CoursesWith CertificateBeginner Friendly17 hours - Free course
Learn SQL
In this SQL course, you'll learn how to manage large datasets and analyze real data using the standard data management language.Beginner Friendly5 hours