How to do While Loops?

Question

How can we use while loops in MySQL? My test script:

BEGIN
    SELECT 0 INTO @n;
    WHILE @n < 10 DO
    SELECT @n;
    SET @n := @n +1;
    END WHILE;
END;

But it has syntax errors. I'm running the loop using the SQLyog client in a standard query window. The syntax errors are of the following form:

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near...

I've also tried to use the while loop example provided by https://dev.mysql.com/doc/refman/5.6/en/while.html but it still didn't work.

Which part of the script is wrong? (Using MySQL 5.6.)

I'm trying to make a 6 month moving average of some data so I was hoping that a while loop would be able to append the "new" 6 month average onto the "old" 6 month average through each iteration of the loop.

Answer 1

You can't do a for loop in an SQL editor without a stored procedure. I use MySQL Workbench to make this.

A quick stored procedure should do the job:

DROP PROCEDURE IF EXISTS proc_loop_test;
CREATE PROCEDURE proc_loop_test()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  test_loop : LOOP
    IF (int_val = 10) THEN
      LEAVE test_loop;
    END IF;

    SET int_val = int_val +1;
    SELECT int_val; 
  END LOOP; 
END;

Answer 2

Just to clarify that "while" loops do work:

It was rightfully said that you need to create a stored procedure (can't run anonymous blocks like in Oracle for instance), and it sometimes don't work well in "generic" SQL editors (as PL/SQL don't always work well either)

You can use MySQL Workbench, or also the command line:

16:54 [test]:> delimiter $$
16:54 [test]:> create procedure testwhile ()
    -> begin
    ->   declare n int;
    ->   set n:=0;
    ->   while n <10 do
    ->     select n;
    ->     set n := n+1;
    ->   end while;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)
16:54 [test]:> delimiter ;
16:54 [test]:> call testwhile();
[...]

As a side note, @variables work as well but they're session variables. In this case I prefer local stored procedure variables (cant be seen or modified outside the scope of the procedure)

Answer 3

I encountered an issue with confusing syntax error complaints from mysql workbench, despite copy pasting examples found from official documentation and other stack exchange site answers. If you have a long query that's broken up onto multiple lines (for human readability) then you may need to use a delimiter to make it clearer to the machine about which line breaks are "meaningful".

An example of a multi line query (albeit edited) in a procedure that was syntatically accepted by mysql workbench for me.

DELIMITER //
DROP PROCEDURE IF EXISTS methodLoop;
CREATE PROCEDURE methodLoop(p1 INT)
BEGIN
    label1: LOOP
        SET p1 = p1-1;
        IF p1 > 0 THEN

            SELECT *
            FROM Table1 foo
            LEFT JOIN Table 2 bar ON foo.id = bar.id;

        END IF;

    END LOOP label1;
END//

DELIMITER ;

call methodLoop(10);

https://stackoverflow.com/questions/52412225/mysql-create-insert-procedure-statement-incomplete