MySQL LOOP Statement

Master the MySQL LOOP statement — the unconditional infinite loop with manual termination via LEAVE. Covers labels, ITERATE for continue, the canonical cursor-iteration pattern with CONTINUE HANDLER FOR NOT FOUND, the strict variables→cursors→handlers DECLARE order, and when to use LOOP vs WHILE vs REPEAT.

The LOOP statement is MySQL's most basic looping construct — an unconditional, infinite loop. It runs its body forever unless you explicitly break out with a LEAVE statement (or terminate the procedure some other way). LOOP is the building block beneath the higher-level WHILE and REPEAT loops.

Use LOOP when you need full manual control over when the loop ends — for example, when iterating through a cursor and stopping based on a condition that doesn't fit either WHILE's pre-test or REPEAT's post-test pattern.

The optional label at the front gives the loop a name you can refer to with LEAVE or ITERATE. It's required as soon as you have nested loops — otherwise MySQL doesn't know which loop you mean.

Two control statements work with any labeled loop:

The typical pattern: combine IF with LEAVE to make the loop terminate when some condition is reached:

Count from 1 to 5, printing each value, and stop. The simplest possible LOOP:

DROP PROCEDURE IF EXISTS simple_loop;

DELIMITER $$

CREATE PROCEDURE simple_loop()
BEGIN
    DECLARE counter INT DEFAULT 0;

    -- Loop until counter reaches 5
    simple_loop_label: LOOP

        SET counter = counter + 1;
        SELECT counter;

        IF counter >= 5 THEN
            LEAVE simple_loop_label;
        END IF;

    END LOOP simple_loop_label;
END$$

DELIMITER ;

CALL simple_loop();

The most common real-world use of LOOP: walk through a query result set with a cursor, processing each row. The loop ends when the cursor's NOT FOUND condition fires.

DROP PROCEDURE IF EXISTS emp_details;

DELIMITER $$

CREATE PROCEDURE emp_details()
BEGIN
    -- Step 1: Declare variables FIRST
    DECLARE v_emp_id   INT;
    DECLARE v_salary   DECIMAL(10, 2);
    DECLARE v_done     INT DEFAULT 0;

    -- Step 2: Declare the cursor
    DECLARE emp_cursor CURSOR FOR
        SELECT employee_id, salary FROM employees;

    -- Step 3: Declare the handler LAST
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- Open and walk
    OPEN emp_cursor;

    emp_loop: LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary;

        IF v_done = 1 THEN
            LEAVE emp_loop;
        END IF;

        -- Process the row — here we just print a formatted message
        SELECT CONCAT('Employee ID: ', v_emp_id,
                      ', Salary: ',  v_salary) AS message;
    END LOOP emp_loop;

    CLOSE emp_cursor;
END$$

DELIMITER ;

CALL emp_details();

To skip the rest of one iteration and jump back to the top, use ITERATE. Here we print only even numbers up to 10:

DROP PROCEDURE IF EXISTS even_only;

DELIMITER $$

CREATE PROCEDURE even_only()
BEGIN
    DECLARE i INT DEFAULT 0;

    even_loop: LOOP
        SET i = i + 1;

        IF i > 10 THEN
            LEAVE even_loop;     -- finished
        END IF;

        IF i % 2 = 1 THEN
            ITERATE even_loop;   -- skip odd numbers
        END IF;

        SELECT i AS even_number;
    END LOOP even_loop;
END$$

DELIMITER ;

CALL even_only();

Pick LOOP when termination doesn't naturally fit a top-of-loop or bottom-of-loop test — most cursor-driven processing falls into this bucket.

1. Always label loops — even when you only have one. Makes LEAVE and ITERATE unambiguous and the code easier to follow.
2. Always have a guaranteed exit path. A LOOP with no reachable LEAVE runs forever and ties up the connection.
3. Respect the DECLARE order: variables → cursors → handlers. MySQL enforces this strictly.
4. Use CONTINUE HANDLER FOR NOT FOUND, not EXIT HANDLER, for cursor loops. EXIT terminates the entire BEGIN ... END block, which is usually not what you want.
5. Always CLOSE cursors after the loop ends. MySQL closes them automatically when the procedure returns, but explicit CLOSE is clearer and frees resources sooner.
6. Use SQL where you can instead of cursor loops. If your loop is just doing UPDATE or INSERT per row, a single set-based statement is almost always faster.

• LOOP is MySQL's unconditional, infinite loop — you control when it ends with LEAVE.
• LEAVE label; exits the loop (like break); ITERATE label; jumps back to the top (like continue).
• Always label the loop and put your termination check inside an IF with LEAVE.
• The most common real-world LOOP pattern is cursor iteration — declare vars, declare cursor, declare CONTINUE HANDLER FOR NOT FOUND, OPEN, FETCH inside the loop, LEAVE on done, CLOSE.
• Choose LOOP over WHILE/REPEAT when the termination logic doesn't fit a single top-of-loop or bottom-of-loop test.