For loop example in MySQL

In MySQL, I have this stored procedure with a LOOP:

DELIMITER $$ CREATE PROCEDURE ABC() BEGIN DECLARE a INT Default 0 ; simple_loop: LOOP SET a=a+1; select a; IF a=5 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ 

It always prints 1. What is the correct syntax for a MySQL Loop?

4 Answers

drop table if exists foo; create table foo ( id int unsigned not null auto_increment primary key, val smallint unsigned not null default 0 ) engine=innodb; drop procedure if exists load_foo_test_data; delimiter # create procedure load_foo_test_data() begin declare v_max int unsigned default 1000; declare v_counter int unsigned default 0; truncate table foo; start transaction; while v_counter < v_max do insert into foo (val) values ( floor(0 + (rand() * 65535)) ); set v_counter=v_counter+1; end while; commit; end # delimiter ; call load_foo_test_data(); select * from foo order by id; 

While loop syntax example in MySQL:

delimiter // CREATE procedure yourdatabase.while_example() wholeblock:BEGIN declare str VARCHAR(255) default ''; declare x INT default 0; SET x = 1; WHILE x <= 5 DO SET str = CONCAT(str,x,','); SET x = x + 1; END WHILE; select str; END// 

Which prints:

mysql> call while_example(); +------------+ | str | +------------+ | 1,2,3,4,5, | +------------+ 

REPEAT loop syntax example in MySQL:

delimiter // CREATE procedure yourdb.repeat_loop_example() wholeblock:BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = 5; SET str = ''; REPEAT SET str = CONCAT(str,x,','); SET x = x - 1; UNTIL x <= 0 END REPEAT; SELECT str; END// 

Which prints:

mysql> call repeat_loop_example(); +------------+ | str | +------------+ | 5,4,3,2,1, | +------------+ 

FOR loop syntax example in MySQL:

delimiter // CREATE procedure yourdatabase.for_loop_example() wholeblock:BEGIN DECLARE x INT; DECLARE str VARCHAR(255); SET x = -5; SET str = ''; loop_label: LOOP IF x > 0 THEN LEAVE loop_label; END IF; SET str = CONCAT(str,x,','); SET x = x + 1; ITERATE loop_label; END LOOP; SELECT str; END// 

Which prints:

mysql> call for_loop_example(); +-------------------+ | str | +-------------------+ | -5,-4,-3,-2,-1,0, | +-------------------+ 1 row in set (0.00 sec) 

Do the tutorial:

If I catch you pushing this kind of MySQL for-loop constructs into production, I'm going to shoot you with the foam missile launcher. You can use a pipe wrench to bang in a nail, but doing so makes you look silly.

Assume you have one table with name 'table1'. It contain one column 'col1' with varchar type. Query to crate table is give below

CREATE TABLE `table1` ( `col1` VARCHAR(50) NULL DEFAULT NULL ) 

Now if you want to insert number from 1 to 50 in that table then use following stored procedure

DELIMITER $$ CREATE PROCEDURE ABC() BEGIN DECLARE a INT Default 1 ; simple_loop: LOOP insert into table1 values(a); SET a=a+1; IF a=51 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$ 

To call that stored procedure use

CALL `ABC`() 

You can exchange this local variable for a global, it would be easier.

DROP PROCEDURE IF EXISTS ABC; DELIMITER $$ CREATE PROCEDURE ABC() BEGIN SET @a = 0; simple_loop: LOOP SET @a=@a+1; select @a; IF @a=5 THEN LEAVE simple_loop; END IF; END LOOP simple_loop; END $$