How to do While Loops?
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;
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)