mysql while循环和添加循环控制条件案例-蒲公英云

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

红太狼 2021-07-24 13:11 542阅读 0赞

**案例：批量插入，根据次数插入到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)$