MYSQL WHILE LOOP CONTINUE is not recognized

Question

i try to skip one condition in loop mysql, and it not work

DELIMITER // 
CREATE PROCEDURE loop_2()
BEGIN
    DECLARE v1 INT;
    DECLARE res TEXT;
    SET v1 = 5;
    SET res = "ok ";
    loop_4: WHILE v1 > 0 DO
        IF (v1 = 4) THEN
            CONTINUE loop_4;
        END IF;
        SET res = CONCAT(res, ", ", v1);
        SET v1 = v1 - 1;
    END WHILE loop_4;
    SELECT res;
END;

i wait for result, like "res: 5,3,2,1" (4 is skipped), but mysql say the syntax is incorrect.

If I replace

CONTINUE

with

LEAVE

it work, but the result is not what I need

Seems like you want ITERATE? - dev.mysql.com/doc/refman/5.7/en/iterate.html I beleive you'd still need to decrement though. — zzevannn
– zzevannn, Commented Feb 28, 2018 at 16:39
ITERATE is start loop again, from first value, i need to skip some value — Denis Rohlinsky
– Denis Rohlinsky, Commented Feb 28, 2018 at 16:55
As I mentioned, you still need to decrement v1. See my answer. — zzevannn
– zzevannn, Commented Feb 28, 2018 at 17:16
do you can to modify my example (code), to help me understand, what it mean? — Denis Rohlinsky
– Denis Rohlinsky, Commented Feb 28, 2018 at 17:19

zzevannn · Accepted Answer · 2018-02-28 17:17:51Z

9

ITERATE is what you want. In a comment, you mentioned

ITERATE is start loop again, from first value, i need to skip some value

That is NOT the case. ITERATE is equivalent to the concept of continue. Your loop was "starting over" (really, running indefinitely) because originally you had:

 loop_4: WHILE v1 > 0 DO
        IF (v1 = 4) THEN
            CONTINUE loop_4;
        END IF;
        SET res = CONCAT(res, ", ", v1);
        SET v1 = v1 - 1;
    END WHILE loop_4;

Which says, when v1 is 4, go back to loop_4 - the value of v1 here is unchanged, so it will infinitely return to loop_4 with v1=4 then enter that if and start over again. As this is a while loop, you need to decrement v1 on your own inside the if, e.g.:

mysql> delimiter $$
mysql> CREATE PROCEDURE loop_2()
    -> BEGIN
    ->     DECLARE v1 INT;
    ->     DECLARE res TEXT;
    ->     SET v1 = 5;
    ->     SET res = "ok ";
    ->     loop_4: WHILE v1 > 0 DO
    ->         IF (v1 = 4) THEN
    ->              SET v1 = v1 - 1;
    ->              ITERATE loop_4;
    ->         END IF;
    ->         SET res = CONCAT(res, ", ", v1);
    ->         SET v1 = v1 - 1;
    ->     END WHILE loop_4;
    ->     SELECT res;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call loop_2();
+-----------------+
| res             |
+-----------------+
| ok , 5, 3, 2, 1 |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

answered Feb 28, 2018 at 17:17

zzevannn

3,8142 gold badges14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Denis Rohlinsky Over a year ago

but if condition is like (var = "abc"), action is same?

Collectives™ on Stack Overflow

MYSQL WHILE LOOP CONTINUE is not recognized

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related