mysql while循环和添加循环控制条件案例

**案例：批量插入，根据次数插入到admin表中**
DELIMITER $
CREATE PROCEDURE pp(IN insertCount INT)
BEGIN
	DECLARE  i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');
		SET i=i+1;
	END WHILE ;
END $

CALL pp(10)$
**案例：批量插入，根据次数插入到admin表中如果次数大于20则停止(添上循环控制语句leave)**

CREATE PROCEDURE pp2(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END $

CALL pp2(30)$

**案例：批量插入，根据次数插入到admin表中,只插入偶数次(添上循环控制语句iterate)**
CREATE PROCEDURE pp3(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 0;
	a:WHILE i<=insertCount DO
	SET i=i+1; #放这，使得iterate对次数无影响
	IF MOD(i,2)!=0 THEN ITERATE a;
	END IF;
	INSERT INTO admin(username,`password`) VALUES(CONCAT('rose',i),'666');

	END WHILE a;
END $

CALL pp3(30)$