存储过程（四）

流程控制的使用

IF

通过if语句，判断student表格中女生人数的多少。

mysql> delimiter //
mysql> create procedure proc()
    -> begin
    -> declare num int;
    -> select count(*) from student where gender=2 into num;
    -> if num > 5
    ->     then select "女生比较多" as "结果";
    ->     else select "女生比较少" as "结果";
    -> end if;
    -> end //
Query OK, 0 rows affected (0.04 sec)

mysql> call proc() //
+------------+
| 结果       |
+------------+
| 女生比较多  |
+------------+
1 row in set (0.04 sec)

Query OK, 0 rows affected (0.05 sec)

CASE

mysql> delimiter //
mysql> create procedure proc1()
    -> begin
    -> declare num int;
    -> select count(*) from student where gender=2 into num;
    -> case num
    ->     when 0 then select "这个班真惨";
    ->     when 5 then select "这个班还行";
    ->     else select "具体数字不考虑";
    -> end case;
    -> end //
Query OK, 0 rows affected (0.04 sec)

mysql> call proc1() //
+----------------+
| 具体数字不考虑 |
+----------------+
| 具体数字不考虑 |
+----------------+
1 row in set (0.04 sec)