mysql 循环while、loop、REPEAT

##删除userInfo表

DROP TABLE IF EXISTSuserInfo;

CREATE TABLE userInfo(

userId INT AUTO_INCREMENT PRIMARY KEY,

userName VARCHAR(30),

userPwd VARCHAR(30)

);

CREATE TABLE test_trigger_table(

id INT,

NAME VARCHAR(30)

);

##插入数据

INSERT INTO userInfo VALUES(NULL,'王五','123456');

##查询

SELECT * FROM userInfo;

3.循环语句

(1).WHILE循环

[ label: ] WHILE expression DO

statements

END WHILE [ label ] ;

(2).LOOP循环

[ label: ] LOOP

statements

END LOOP [ label ] ;

(3).REPEAT UNTIL循环

[ label: ] REPEAT

statements

UNTIL expression

END REPEAT [ label ] ;

##repeat实例

##REPEAT ... END REPEAT

##drop PROCEDURE p_repeat;

DELIMITER//

CREATE PROCEDURE p_repeat ()

BEGIN

DECLARE v INT;

SET v = 0;

REPEAT

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET v = v + 1;

UNTIL v >= 5

END REPEAT;

END; //

##注释:这是一个REPEAT循环的例子，功能和前面WHILE循环一样。

## 区别在于它在执行后检查结果，而WHILE则是执行前检查。（译者语：可能等同于DO WHILE吧

######调用存储过程

CALL p_repeat();

SELECT COUNT(*) FROM userInfo;

##while实例

##drop PROCEDURE pro();

DELIMITER//

CREATE PROCEDURE pro_while()

BEGIN

DECLARE cout INT;

SET cout=10;

WHILE cout>0 DO

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET cout=cout-1;

END WHILE;

END;

//

##注释:这是一个while循环的例子，功能和前面loop循环一样。

## 区别在于它在执行前检查结果，而loop则是执行后检查。（译者语：可能等同于WHILE

CALL pro_while();

#####loop实例

####LOOP ... END LOOP

DELIMITER//

CREATE PROCEDURE p_loop ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

INSERT INTO userInfo VALUES(NULL,'王五','123456');

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

CALL p_loop();

SELECT COUNT(*) FROM userInfo;