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, |
+------------+
