For loop example in MySQL

小开

最佳答案

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;

小开

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`()

小开

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: http://www.mysqltutorial.org/stored-procedures-loop.aspx

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.

小开

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 $$