MySQL: ใช้ FOR LOOP ใน Stored Procedure

Syntax สำหรับ FOR LOOP ใน MySQL Stored Procedure:

DELIMITER //
CREATE PROCEDURE yourProcedureName()
wholeblock: BEGIN
    DECLARE var1 INT;
    DECLARE var2 INT;
    DECLARE var3 VARCHAR(255);
    SET var1 = 1;
    SET var2 = 10;
    SET var3 = '';
    
    loop_label: LOOP
        IF var1 > var2 THEN
            LEAVE loop_label;
        END IF;
        SET var3 = CONCAT(var3, var1, ',');
        SET var1 = var1 + 1;
        ITERATE loop_label;
    END LOOP;
    
    SELECT var3;
END //
DELIMITER ;

ตัวอย่างจริง: พิมพ์ 1 ถึง 10

mysql> DELIMITER //
mysql> CREATE PROCEDURE ForLoop()
    -> wholeblock: BEGIN
    -> DECLARE start INT;
    -> DECLARE maxLimit INT;
    -> DECLARE result VARCHAR(255);
    -> SET start = 1;
    -> SET maxLimit = 10;
    -> SET result = '';
    -> loop_label: LOOP
    -> IF start > 10 THEN LEAVE loop_label; END IF;
    -> SET result = CONCAT(result, start, ',');
    -> SET start = start + 1;
    -> ITERATE loop_label;
    -> END LOOP;
    -> SELECT result;
    -> END //
Query OK, 0 rows affected (0.37 sec)
mysql> DELIMITER ;

เรียกใช้ Stored Procedure

mysql> CALL ForLoop();

ผลลัพธ์

+-----------------------+
| result                |
+-----------------------+
| 1,2,3,4,5,6,7,8,9,10, |
+-----------------------+