The LOOP statement is used to construct simple loops, allowing repeated execution of a list of statements until the loop is terminated with LEAVE.
Syntax
[begin_label:] LOOP
statement_list
END LOOP [end_label]
Parameters
Parameter |
Note |
|---|---|
| begin_label | Optional. The name of the tag at the start of the loop. |
| statement_list | List of SQL/PL statements to be executed repeatedly. Each statement ends with a semicolon (;). |
| end_label | Optional. The tag name at the end of the loop, which must correspond to the begin_label The same. |
The LOOP statement allows repeated execution of a statement_list, which consists of one or more statements, each ending with a semicolon (;). The statements in the loop are executed repeatedly until the loop is terminated with LEAVE. In a stored function, you can also use RETURN to exit the function entirely. Omitting a loop termination statement results in an infinite loop. A label can be used to mark the LOOP statement.
Examples
Example 1: A 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: Use ITERATE to skip loop iterations.
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: Use 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