MySQL存储过程---流程控制（循环）

循环类型

while

语法

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

loop   死循环

语法

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

 

repeat

语法

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

循环控制

iterate ----> continue
leave -----> break

实例

随机向表中插入1万行数据

id：1-10000

name：6位随机字符

age：18-35

gender：M/F

建表

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;

1、创建存储过程 while

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)

2、创建repeat循环

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)

 

3、创建loop循环

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)

流程控制

iterate

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)