Mysql 之 循环语句

[b]官方英文文档：[/b]
[url]http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html[/url]


[color=red]************************** [b]LOOP[/b] **************************[/color]
[b]语法：[/b]

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

[b]实例：[/b]

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$$

[b]调用：[/b]
call pro_loop(8);
[b]输出：[/b] 9 ， END IF ， 10;

[b]说明：[/b]
ITERATE label1 ： 继续循环label1
LEAVE label1 ： 退出label1循环
[color=red][b]注：[/b] 当把打印代码放到ITERATE , LEAVE 后，不能输出;[/color]


[color=red]************************** [b]WHILE[/b] **************************[/color]
[b]语法：[/b]

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

[b]实例：[/b]

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$$

[b]调用：[/b]call pro_while(3);
[b]输出：[/b]1 , 2


[color=red]************************** [b]REPEAT[/b] **************************[/color]
[b]语法：[/b]

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

[b]实例：[/b]

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$$

[b]调用：[/b]call pro_repeat(2);
[b]输出：[/b]0 , 1 , 2
[color=red]说明： 先循环一次，再判断，直到@X > p1 就结束循环[/color]