MySQL存储过程之while、repeat循环

一、创建示例表

/* test数据库下建立testa表 */
CREATE TABLE `test`.`testa`  (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

一、while循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `PRO_WHILE`()
BEGIN
	DECLARE i INT DEFAULT 0;
	
	WHILE (i<10) DO
		/*当满足条件的时候执行此代码块*/
		BEGIN
			INSERT INTO testa (ID) VALUES(i);
			SET i=i+1;
		END;
	END WHILE;

END

运行结果如下：

2、repeat循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `PRO_REPEAT`()
BEGIN
    DECLARE i INT DEFAULT 100;
    
    REPEAT
        BEGIN
            INSERT INTO testa (ID) VALUES(i);
            SET i=i+1;
        END;
    /*当满足条件的时候结束此代码块*/
    UNTIL i>=110 
    END REPEAT;

END

运行结果如下：