mysql.while i_mysql实例 while循环中的if语句

1，while循环中使用if分支语句

代码：

mysql>

mysql> delimiter $$

mysql>

mysql> CREATE PROCEDURE myProc() //创建while循环的存储过程 if分支语句示例

-> BEGIN

->

-> DECLARE i int;

-> SET i=1;

-> loop1: WHILE i<=10 DO

-> IF MOD(i,2)<>0 THEN /*Even number - try again*/

-> SELECT CONCAT(i," is an odd number");

-> END IF;

-> SET i=i+1;

-> END WHILE loop1;

-> END$$

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> delimiter ;

mysql> call myProc(); //调用存储过程

+-------------------------------+

| CONCAT(i," is an odd number") |

+-------------------------------+

| 1 is an odd number |

+-------------------------------+

1 row in set (0.02 sec)

+-------------------------------+

| CONCAT(i," is an odd number") |

+-------------------------------+

| 3 is an odd number |

+-------------------------------+

1 row in set (0.02 sec)

+-------------------------------+

| CONCAT(i," is an odd number") |

+-------------------------------+

| 5 is an odd number |

+-------------------------------+

1 row in set (0.02 sec)

+-------------------------------+

| CONCAT(i," is an odd number") |

+-------------------------------+

| 7 is an odd number |

+-------------------------------+

1 row in set (0.02 sec)

+-------------------------------+

| CONCAT(i," is an odd number") |

+-------------------------------+

| 9 is an odd number |

+-------------------------------+

1 row in set (0.02 sec)

Query OK, 0 rows affected (0.38 sec)

mysql> drop procedure myProc;

Query OK, 0 rows affected (0.00 sec)

2，带label标签的while循环

代码：

DELIMITER $$

CREATE PROCEDURE AGAIN

(OUT RESULT INTEGER)

BEGIN

DECLARE COUNTER INTEGER DEFAULT 1;

SET RESULT = 0;

LOOP1: WHILE COUNTER <= 1000 DO

SET COUNTER = COUNTER + 1;

IF COUNTER > 100 THEN

LEAVE LOOP1;

ELSE

ITERATE LOOP1;

END IF;

SET RESULT = COUNTER * 10;

END WHILE LOOP1;

END$$

DELIMITER ;