[标签：] while 条件 do
循环体；
end while [标签]；

[标签：] loop
循环体；
end loop [标签]；

[标签：] repeat
循环体；
until 条件
end repeat [标签]；

CREATE TABLE t4(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
age TINYINT NOT NULL,
gender CHAR(1)
) ENGINE=INNODB CHARSET utf8;

DELIMITER $$

USE `world`$$

DROP PROCEDURE IF EXISTS `p_while`$$

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_while`( IN num INT)
BEGIN
    
    DECLARE u_n VARCHAR(64);
    DECLARE u_a TINYINT;
    DECLARE u_g CHAR(1);
    DECLARE i INT DEFAULT 0;
    
    WHILE i < num
    DO
    SELECT SUBSTR(REPLACE(UUID(),‘-‘,‘‘),1,6) INTO u_n;
    SELECT CEIL(RAND()*18)+17 INTO u_a;
    SELECT SUBSTR(‘MF‘,CEIL(RAND()*2),1) INTO u_g;
    INSERT INTO t4(NAME,age) VALUES(u_n,u_a);
    SET i = i+1;
    END WHILE;
    END$$

DELIMITER ;

CALL p_while(100)

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `world`.`p_repeat`(IN num INT)
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT ‘string‘*/
    BEGIN
    
    DECLARE u_n VARCHAR(64);
    DECLARE u_a TINYINT;
    DECLARE u_g CHAR(1);
    DECLARE i INT DEFAULT 0;
    
    REPEAT
    SELECT SUBSTR(REPLACE(UUID(),‘-‘,‘‘),1,6) INTO u_n;
    SELECT CEIL(RAND()*18)+17 INTO u_a;
    SELECT SUBSTR(‘MF‘,CEIL(RAND()*2),1) INTO u_g;
    INSERT INTO t4(NAME,age,gender) VALUES(u_n,u_a,u_g);
    SET i = i+1;
    UNTIL i > num
    END REPEAT;
    

    END$$

DELIMITER ;

CALL p_repeat(100)

DELIMITER $$

USE `world`$$

DROP PROCEDURE IF EXISTS `p_loop`$$

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_loop`( IN num INT)
BEGIN
    
    DECLARE u_n VARCHAR(64);
    DECLARE u_a TINYINT;
    DECLARE u_g CHAR(1);
    DECLARE i INT DEFAULT 0;
    
    lab1:LOOP            -- 定义循环标签
    SELECT SUBSTR(REPLACE(UUID(),‘-‘,‘‘),1,6) INTO u_n;
    SELECT CEIL(RAND()*18)+17 INTO u_a;
    SELECT SUBSTR(‘MF‘,CEIL(RAND()*2),1) INTO u_g;
    IF i > num
    THEN LEAVE lab1;  -- 告知退出哪个标签的循环
    ELSE
    INSERT INTO t4(NAME,age,gender) VALUES(u_n,u_a,u_g);
    SET i = i+1;
    END IF;
    END LOOP lab1;
    END$$

DELIMITER ;

CALL p_loop(100)

DELIMITER $$

USE `world`$$

DROP PROCEDURE IF EXISTS `p_iterate`$$

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_iterate`( IN num INT)
BEGIN
    
    DECLARE u_n VARCHAR(64);
    DECLARE u_a TINYINT;
    DECLARE u_g CHAR(1);
    DECLARE i INT DEFAULT 1;
    
    lab1: WHILE i < num
    DO
    SELECT SUBSTR(REPLACE(UUID(),‘-‘,‘‘),1,6) INTO u_n;
    SELECT CEIL(RAND()*18)+17 INTO u_a;
    SELECT SUBSTR(‘MF‘,CEIL(RAND()*2),1) INTO u_g;
    SET i = i+1;
    IF MOD(i,2)=0 
    THEN
    ITERATE lab1;
    ELSE
    INSERT INTO t4(NAME,age,gender) VALUES(CONCAT(u_n,‘_‘,i),u_a,u_g);
    END IF;
    END WHILE lab1;
    END$$

DELIMITER ;

CALL p_iterate(100)