MySQL从零到有20：可编程性之流程控制语句

时间：10-08来源：作者：点击数：20

背景

说到流程控制语句，我们在程序语法中用的比较多，比如C#的if..else...，while...，?: 等。同样的，在MySQL中，也有一些流程控制的语法，方便我们在写函数、存储过程的时候对逻辑进行控制和处理。

常见的过程式SQL语句可以用在存储过程或者函数体中。其中包括：IF函数、IF条件语句、CASE语句、LOOP语句、WHILE语句、REPEAT语句、LEAVE语句和ITERATE语句，它们极大的方便了我们进行流程控制。

下面我们一个一个来看。

流程语句分解

数据基础

mysql> select *  from students;
+-----------+-------------+-------+---------+-----+
| studentid | studentname | score | classid | sex |
+-----------+-------------+-------+---------+-----+
|         1 | brand       | 105.5 |       1 |   1 |
|         2 | helen       | 98.5  |       1 |   0 |
|         3 | lyn         | 97    |       1 |   0 |
|         4 | sol         | 97    |       1 |   1 |
|         5 | b1          | 89    |       2 |   1 |
|         6 | b2          | 90    |       2 |   1 |
|         7 | c1          | 76    |       3 |   0 |
|         8 | c2          | 73.5  |       3 |   0 |
|         9 | lala        | 73    |       0 |   0 |
|        10 | A           | 100   |       3 |   1 |
|        16 | test1       | 100   |       0 |   1 |
|        17 | trigger2    | 107   |       0 |   1 |
|        22 | trigger1    | 100   |       0 |   0 |
+-----------+-------------+-------+---------+-----+
13 rows in set

mysql> select *  from scores;
+-----------+---------+-------+
| scoregrad | downset | upset |
+-----------+---------+-------+
| A         |      81 |    90 |
| B         |      71 |    80 |
| C         |      61 |    70 |
| D         |      51 |    60 |
| S         |      91 |   100 |
| S+        |     101 |   120 |
+-----------+---------+-------+
6 rows in set

IF函数

有点类似C#语法中的三元表达式，有3个参数，第一个参数是表达式，后面两个是值，当表达式成立的时候取第一个值，表达式不成立的时候取第二个值。

if(expr,val1,val2);   --语法

输出学生信息中的名称和性别（1为男，0为女，这边用if函数进行转换）

mysql> select studentname,if(sex=0,'女','男') from students where classid<>0;
+-------------+---------------------+
| studentname | if(sex=0,'女','男') |
+-------------+---------------------+
| brand       | 男                  |
| helen       | 女                  |
| lyn         | 女                  |
| sol         | 男                  |
| b1          | 男                  |
| b2          | 男                  |
| c1          | 女                  |
| c2          | 女                  |
| A           | 男                  |
+-------------+---------------------+
9 rows in set

IF条件语句

IF语句用来进行条件判断，根据不同的条件执行不同的操作。该语句在执行时首先判断IF后的条件是否为真，则执行THEN后的语句，如果为假则继续判断IF语句直到为真为止，当以上都不满足时则执行ELSE语句后的内容。

IF condition THEN
    ...
ELSEIF condition THEN
    ...
ELSE
    ...
END IF

代码示例，根据考试成绩来分布不同的成绩等级

mysql>
/*如果存在函数func_test2，则删除*/
DROP FUNCTION IF EXISTS fun_if;
/*声明结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun_if(score DECIMAL(10,2))
  RETURNS CHAR
BEGIN
DECLARE score_grad VARCHAR(5) DEFAULT '';
IF score>100 THEN SET score_grad='S';
ELSEIF (score BETWEEN 91 AND 100) THEN SET score_grad='A';
ELSEIF (score BETWEEN 81 AND 90) THEN SET score_grad='B';
ELSEIF (score BETWEEN 71 AND 80) THEN SET score_grad='C' ;
ELSE set score_grad='D';
END IF;
return score_grad;
END $
/*重置结束符为;*/
DELIMITER ;
Query OK, 0 rows affected

执行结果

mysql> select fun_if(101),fun_if(100),fun_if(90),fun_if(80),fun_if(70);
+-------------+-------------+------------+------------+------------+
| fun_if(101) | fun_if(100) | fun_if(90) | fun_if(80) | fun_if(70) |
+-------------+-------------+------------+------------+------------+
| S           | A           | B          | C          | D          |
+-------------+-------------+------------+------------+------------+
1 row in set

CASE语句

CASE语句为多分支语句结构，该语句首先从WHEN后的VALUE中查找与CASE后的VALUE相等的值，如果查找到则执行该分支的内容，否则执行ELSE后的内容。CASE语句表示形式如下，类似C#中switch：

CASE expr
WHEN val1 THEN result1 or state1[;]（可选项，如果是语句需要加分号,结果值可以加）
WHEN val2 THEN result2 or state2
...
ELSE resultn or staten
END [CASE] （可选项，在begin end之间需加case，select后就不需要）

在select中使用示例

mysql> select studentname,case sex WHEN 0 THEN '女' WHEN 1 THEN '男' end as sex
from students where classid<>0;
+-------------+-----+
| studentname | sex |
+-------------+-----+
| brand       | 男  |
| helen       | 女  |
| lyn         | 女  |
| sol         | 男  |
| b1          | 男  |
| b2          | 男  |
| c1          | 女  |
| c2          | 女  |
| A           | 男  |
+-------------+-----+
9 rows in set

在函数或存储过程中使用示例

mysql>
/*如果存在函数func_test2，则删除*/
DROP FUNCTION IF EXISTS fun_case;
/*声明结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun_case(sex INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE sexStr VARCHAR(20) DEFAULT '';
CASE sex
WHEN 0 then set sexStr='女';
WHEN 1 then set sexStr='男';
ELSE set sexStr='不确定';
END CASE;
return sexStr;
END $
/*重置结束符为;*/
DELIMITER ;

Query OK, 0 rows affected

函数执行结果

mysql> select studentname,fun_case(sex) from students where classid<>0;
+-------------+---------------+
| studentname | fun_case(sex) |
+-------------+---------------+
| brand       | 男            |
| helen       | 女            |
| lyn         | 女            |
| sol         | 男            |
| b1          | 男            |
| b2          | 男            |
| c1          | 女            |
| c2          | 女            |
| A           | 男            |
+-------------+---------------+
9 rows in set

循环语句while

循环语句while 类似于C#中的while循环，我们知道在C#的while 或者 for 语句中，经常有用到两个关键语法：跳过当前循环（continue）和结束循环（break）。

同样的，在MySQL中也有两个语法对应跳过和结束循环。

ITERATE  loop_label;   --跳过当前循环

LEAVE  loop_label;   --结束循环

while 语法

[loop_label:]while condition do
 --Todo:loop body
end while [loop_label];

loop_label：循环标签，和iterate、leave结合用于在循环内部对循环进行控制：如：跳过本次循环、结束循环。

condition：循环条件，当满足条件的时候，就会执行循环体，条件不成立的时候结束循环。

while示例

下面脚本代码演示了将students表中studentid在给定数值范围内的数据存储到另外一张表中。

/*先清除studentCount表记录*/
truncate table studentcount;
/*存储过程如果存在则删除*/
DROP PROCEDURE IF EXISTS sp_while1;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE sp_while1(varial_count int)
  BEGIN
    DECLARE idx int DEFAULT 1;
    DECLARE uname VARCHAR(30) DEFAULT '';
    loop_label:WHILE idx<=varial_count DO
      select studentname into uname from students where studentid = idx;
      INSERT into studentCount values (idx,uname);
      SET idx=idx+1;
    END WHILE;
  END $
/*结束符置为;*/
DELIMITER ;

调用存储过程，给定数值范围是10，所以这边取出1~10的数据存储到studentCount表中

mysql> CALL sp_while1(10);
Query OK, 1 row affected

mysql> select * from studentCount;
+-----------+-------------+
| studentid | studentname |
+-----------+-------------+
|         1 | brand       |
|         2 | helen       |
|         3 | lyn         |
|         4 | sol         |
|         5 | b1          |
|         6 | b2          |
|         7 | c1          |
|         8 | c2          |
|         9 | lala        |
|        10 | A           |
+-----------+-------------+
10 rows in set

while示例：包含iterate/leave

前面我们说明过了，iterate 和 leave 分别代表跳过本次循环，类似于C#中的continue和break。我们在例子中测试下吧：

遇到studentname=lala时，结束循环，遇到偶数时候跳过单次循环。

/*先清除studentCount表记录*/
truncate table studentcount;
/*存储过程如果存在则删除*/
DROP PROCEDURE IF EXISTS sp_while2;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE sp_while2(varial_count int)
  BEGIN
    DECLARE idx int DEFAULT 0;
    DECLARE uname VARCHAR(30) DEFAULT '';
    loop_label:WHILE idx<=varial_count DO
      SET idx=idx+1;
      select studentname into uname from students where studentid = idx;
      /*如果遇到studentname为lala的同学，结束循环*/
      IF uname='lala' THEN
        LEAVE loop_label;
      /*如果idx为偶数，则跳过本次循环*/
      ELSEIF idx%2=0 THEN
        ITERATE loop_label;
      END IF;
      INSERT into studentCount values (idx,uname);
    END WHILE;
  END $
/*结束符置为;*/
DELIMITER ;

调用存储过程，输出符合要求的数据：

mysql> CALL sp_while2(10);
Query OK, 1 row affected

mysql> select * from studentCount;
+-----------+-------------+
| studentid | studentname |
+-----------+-------------+
|         1 | brand       |
|         3 | lyn         |
|         5 | b1          |
|         7 | c1          |
+-----------+-------------+
4 rows in set

循环语句repeat

repeat语法

[loop_label:]repeat
-- Todo loop body
until condition 
end repeat [loop_label];

可以对比下上面while的语法，while是先判断条件是否成立再执行循环体，repeat循环更像是的do...while循环，就是循环始终都会先执行一次，然后再判断结束循环的条件，不满足结束条件，循环体继续执行。

/*先清除studentCount表记录*/
truncate table studentcount;
/*存储过程如果存在则删除*/
DROP PROCEDURE IF EXISTS sp_repeat;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE sp_repeat(varial_count int)
  BEGIN
    DECLARE idx int DEFAULT 0;
    DECLARE uname VARCHAR(30) DEFAULT '';
    loop_label:REPEAT
      SET idx=idx+1;
      select studentname into uname from students where studentid = idx;
      /*如果遇到studentname为lala的同学，结束循环*/
      IF uname='lala' THEN
        LEAVE loop_label;
      /*如果idx为偶数，则跳过本次循环*/
      ELSEIF idx%2=0 THEN
        ITERATE loop_label;
      END IF;
      INSERT into studentCount values (idx,uname);
    UNTIL idx>varial_count
    END REPEAT;
  END $
/*结束符置为;*/
DELIMITER ;

注意条件的变化，下面是调用存储过程，输出需要的数据：

mysql> CALL sp_repeat(10);
Query OK, 1 row affected

mysql> select * from studentCount;
+-----------+-------------+
| studentid | studentname |
+-----------+-------------+
|         1 | brand       |
|         3 | lyn         |
|         5 | b1          |
|         7 | c1          |
+-----------+-------------+
4 rows in set

循环语句loop

loop语法

[loop_label:]loop
--Todo loop body
end loop [loop label];

loop不像while和repeat那样有控制条件，条件不符合的时候会跳出。所以它实际上是会一直执行的，如果不主动中断或者跳出的话，类似于一个死循环，需要在循环体中使用iterate或者leave来控制循环的执行。

/*先清除studentCount表记录*/
truncate table studentcount;
/*存储过程如果存在则删除*/
DROP PROCEDURE IF EXISTS sp_loop;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE sp_loop(varial_count int)
  BEGIN
    DECLARE idx int DEFAULT 0;
    DECLARE uname VARCHAR(30) DEFAULT '';
    loop_label:LOOP
      SET idx=idx+1;
      select studentname into uname from students where studentid = idx;
      /*如果遇到studentname为lala的同学，结束循环*/
      IF uname='lala' THEN
        LEAVE loop_label;
      /*如果idx为偶数，则跳过本次循环*/
      ELSEIF idx%2<>0 THEN
        ITERATE loop_label;
      /*这边加一个终结计数跳出的条件*/
      ELSEIF idx>varial_count THEN
        LEAVE loop_label;
      END IF;
      INSERT into studentCount values (idx,uname);

    END LOOP;
  END $
/*结束符置为;*/
DELIMITER ;

调用存储过程，并输出你需要的数据：

mysql> CALL sp_loop(6);
Query OK, 1 row affected

mysql> select * from studentCount;
+-----------+-------------+
| studentid | studentname |
+-----------+-------------+
|         2 | helen       |
|         4 | sol         |
|         6 | b2          |
+-----------+-------------+
3 rows in set