MySQL 流程控制while语句

• while语句是存储过程或函数中表达循环执行的一种方式

DELIMITER //
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
update students set gender=-1 where sid=v1;
SET v1 = v1 - 1;
END WHILE;
END;
//
DELIMITER ;

mysql> DELIMITER //
mysql> CREATE PROCEDURE dowhile()
    -> BEGIN
    -> DECLARE v1 INT DEFAULT 5;
    -> WHILE v1 > 0 DO
    -> update students set gender=-1 where sid=v1;
    -> SET v1 = v1 - 1;
    -> END WHILE;
    -> END;
    -> //
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;
mysql> 
mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname  | gender | dept_id | brithday            |
+-----+--------+--------+---------+---------------------+
|   1 | Andrew | 0      |       1 | 1983-01-01 00:00:00 |
|   2 | Andy   | 0      |       1 | 1983-01-01 00:00:00 |
|   3 | Bob    | 0      |       1 | 1983-01-01 00:00:00 |
|   4 | Ruth   | 1      |       2 | 1983-01-01 00:00:00 |
|   5 | Mike   | 0      |       2 | 1986-01-01 00:00:00 |
|   6 | John   | 0      |       3 | 1986-01-01 00:00:00 |
|   7 | Cindy  | 1      |       3 | 1986-01-01 00:00:00 |
|   8 | Susan  | 1      |       3 | 1986-01-01 00:00:00 |
+-----+--------+--------+---------+---------------------+
8 rows in set (0.00 sec)

mysql> call dowhile();
Query OK, 1 row affected (0.15 sec)

mysql> select * from students;
+-----+--------+--------+---------+---------------------+
| sid | sname  | gender | dept_id | brithday            |
+-----+--------+--------+---------+---------------------+
|   1 | Andrew | -1     |       1 | 1983-01-01 00:00:00 |
|   2 | Andy   | -1     |       1 | 1983-01-01 00:00:00 |
|   3 | Bob    | -1     |       1 | 1983-01-01 00:00:00 |
|   4 | Ruth   | -1     |       2 | 1983-01-01 00:00:00 |
|   5 | Mike   | -1     |       2 | 1986-01-01 00:00:00 |
|   6 | John   | 0      |       3 | 1986-01-01 00:00:00 |
|   7 | Cindy  | 1      |       3 | 1986-01-01 00:00:00 |
|   8 | Susan  | 1      |       3 | 1986-01-01 00:00:00 |
+-----+--------+--------+---------+---------------------+
8 rows in set (0.00 sec)