通过这个实例,学习存储过程的使用,涉及到存储过程循环体。
关于循环体的实现方式总结为3种:
实现方式一(while ..循环条件..do..循环体.. end while):
1 DELIMITER $$ 2 CREATE PROCEDURE insert_many_i_data() 3 BEGIN 4 DECLARE i INT; 5 SET i=0; 6 WHILE i <10000 DO 7 SET i=i+1; 8 INSERT INTO account(accountid,balance) VALUES(i,1); 9 END WHILE; 10 END$$
实现方式二(repeat ..循环体..until..循环条件.. end repeat):
1 DELIMITER $$ 2 CREATE PROCEDURE insert_repeat_data() 3 BEGIN 4 DECLARE i INT; 5 SET i=0; 6 REPEAT 7 SET i=i+1; 8 INSERT INTO account(accountid,balance) VALUES(i,1); 9 UNTIL i <10000 10 END REPEAT; 11 END$$
实现方式三(loop..end loop):
DELIMITER $$ CREATE PROCEDURE insert_loop_data() BEGIN DECLARE i INT; SET i=0; loop_name:LOOP SET i=i+1; INSERT INTO account(accountid,balance) VALUES(i,1); IF i <10000 THEN LEAVE loop_name; END IF; END LOOP; END$$