The LOOP statement is used to construct simple loops, allowing the repeated execution of a list of statements until the loop is terminated using LEAVE.
Syntax
[begin_label:] LOOP
statement_list
END LOOP [end_label]
Parameters
Parameter |
Description |
|---|---|
| begin_label | Optional. The label name at the start of the loop. |
| statement_list | The list of SQL/PL statements to be repeated. Each statement is terminated with a semicolon (;). |
| end_label | Optional. The label name at the end of the loop, which must match begin_label. |
The LOOP statement allows the repeated execution of statement_list, which consists of one or more statements, each terminated with a semicolon (;). The statements within the loop will be executed repeatedly until the loop is terminated using LEAVE. In stored functions, you can also use RETURN to exit the function entirely. Omitting the loop termination statement will result in an infinite loop. You can use label to mark the LOOP statement.
Examples
Example 1: Basic LOOP loop.
obclient> DELIMITER //
obclient> CREATE PROCEDURE simple_loop()
BEGIN
DECLARE counter INT DEFAULT 0;
loop_label: LOOP
SET counter = counter + 1;
IF counter >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP loop_label;
SELECT counter;
END //
obclient> DELIMITER ;
obclient> CALL simple_loop();
The query result is as follows:
+---------+
| counter |
+---------+
| 5 |
+---------+
1 row in set
Example 2: Using ITERATE to skip a loop iteration.
obclient> DELIMITER //
obclient> CREATE PROCEDURE iterate_loop(p1 INT)
BEGIN
loop_label1: LOOP
SET p1 = p1 + 10;
IF p1 < 100 THEN
ITERATE loop_label1;
END IF;
LEAVE loop_label1;
END LOOP loop_label1;
SET @x = p1;
SELECT @x;
END //
obclient> DELIMITER ;
obclient> CALL iterate_loop(50);
The query result is as follows:
+------+
| @x |
+------+
| 100 |
+------+
1 row in set
Example 3: Using LOOP in a stored function.
obclient> DELIMITER //
obclient> CREATE FUNCTION calculate_sum(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE sum_result INT DEFAULT 0;
sum_loop: LOOP
IF i > n THEN
LEAVE sum_loop;
END IF;
SET sum_result = sum_result + i;
SET i = i + 1;
END LOOP sum_loop;
RETURN sum_result;
END //
obclient> DELIMITER ;
obclient> SELECT calculate_sum(10);
The query result is as follows:
+-----------------+
| calculate_sum(10) |
+-----------------+
| 55 |
+-----------------+
1 row in set