Mysql 之 循环语句
官方英文文档:
http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html
************************** LOOP **************************
语法:
[begin_label:] LOOP statement_list END LOOP [end_label]
实例:
DELIMITER$$ CREATE PROCEDURE pro_loop(p1 INT) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN SELECT p1 AS result; ITERATE label1; END IF; SELECT 'END IF' AS result; LEAVE label1; END LOOP label1; SET @x = p1; SELECT @x as result; END$$
调用:
call pro_loop(8);
输出: 9 , END IF , 10;
说明:
ITERATE label1 : 继续循环label1
LEAVE label1 : 退出label1循环
注: 当把打印代码放到ITERATE , LEAVE 后,不能输出;
************************** WHILE **************************
语法:
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
实例:
DELIMITER$$ DROP PROCEDURE IF EXISTS pro_while$$ CREATE PROCEDURE pro_while ( i INT ) BEGIN DECLARE j INT DEFAULT 1; WHILE j < i DO SELECT j AS result; SET j = j + 1; END WHILE; END$$
调用:call pro_while(3);
输出:1 , 2
************************** REPEAT **************************
语法:
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
实例:
DELIMITER$$ DROP PROCEDURE IF EXISTS pro_repeat$$ CREATE PROCEDURE pro_repeat(p1 INT) BEGIN SET @X = 0; REPEAT SELECT @X AS result; SET @X = @X + 1; UNTIL @X > p1 END REPEAT; END$$
调用:call pro_repeat(2);
输出:0 , 1 , 2
说明: 先循环一次,再判断,直到@X > p1 就结束循环