MySQL全面瓦解20:可编程性之流程控制语句

背景

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

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

下面我们一个一个来看。

流程语句分解

数据基础

 1 mysql> select *  from students;
 2 +-----------+-------------+-------+---------+-----+
 3 | studentid | studentname | score | classid | sex |
 4 +-----------+-------------+-------+---------+-----+
 5 |         1 | brand       | 105.5 |       1 |   1 |
 6 |         2 | helen       | 98.5  |       1 |   0 |
 7 |         3 | lyn         | 97    |       1 |   0 |
 8 |         4 | sol         | 97    |       1 |   1 |
 9 |         5 | b1          | 89    |       2 |   1 |
10 |         6 | b2          | 90    |       2 |   1 |
11 |         7 | c1          | 76    |       3 |   0 |
12 |         8 | c2          | 73.5  |       3 |   0 |
13 |         9 | lala        | 73    |       0 |   0 |
14 |        10 | A           | 100   |       3 |   1 |
15 |        16 | test1       | 100   |       0 |   1 |
16 |        17 | trigger2    | 107   |       0 |   1 |
17 |        22 | trigger1    | 100   |       0 |   0 |
18 +-----------+-------------+-------+---------+-----+
19 13 rows in set
20 
21 mysql> select *  from scores;
22 +-----------+---------+-------+
23 | scoregrad | downset | upset |
24 +-----------+---------+-------+
25 | A         |      81 |    90 |
26 | B         |      71 |    80 |
27 | C         |      61 |    70 |
28 | D         |      51 |    60 |
29 | S         |      91 |   100 |
30 | S+        |     101 |   120 |
31 +-----------+---------+-------+
32 6 rows in set 

IF函数

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

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

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

 1 mysql> select studentname,if(sex=0,'','') from students where classid<>0;
 2 +-------------+---------------------+
 3 | studentname | if(sex=0,'','') |
 4 +-------------+---------------------+
 5 | brand       ||
 6 | helen       ||
 7 | lyn         ||
 8 | sol         ||
 9 | b1          ||
10 | b2          ||
11 | c1          ||
12 | c2          ||
13 | A           ||
14 +-------------+---------------------+
15 9 rows in set 

IF条件语句

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

1 IF condition THEN
2     ...
3 ELSEIF condition THEN
4     ...
5 ELSE
6     ...
7 END IF 

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

 1 mysql> 
 2 /*如果存在函数func_test2,则删除*/
 3 DROP FUNCTION IF EXISTS fun_if;
 4 /*声明结束符为$*/
 5 DELIMITER $
 6 /*创建函数*/
 7 CREATE FUNCTION fun_if(score DECIMAL(10,2))
 8   RETURNS CHAR
 9 BEGIN
10 DECLARE score_grad VARCHAR(5) DEFAULT '';
11 IF score>100 THEN SET score_grad='S'; 
12 ELSEIF (score BETWEEN 91 AND 100) THEN SET score_grad='A';
13 ELSEIF (score BETWEEN 81 AND 90) THEN SET score_grad='B';
14 ELSEIF (score BETWEEN 71 AND 80) THEN SET score_grad='C' ;
15 ELSE set score_grad='D';
16 END IF;
17 return score_grad;
18 END $
19 /*重置结束符为;*/
20 DELIMITER ;
21 Query OK, 0 rows affected 

执行结果

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

CASE语句

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

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

在select中使用示例

 1 mysql> select studentname,case sex WHEN 0 THEN '' WHEN 1 THEN '' end as sex
 2 from students where classid<>0;
 3 +-------------+-----+
 4 | studentname | sex |
 5 +-------------+-----+
 6 | brand       ||
 7 | helen       ||
 8 | lyn         ||
 9 | sol         ||
10 | b1          ||
11 | b2          ||
12 | c1          ||
13 | c2          ||
14 | A           ||
15 +-------------+-----+
16 9 rows in set 

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

 1 mysql> 
 2 /*如果存在函数func_test2,则删除*/
 3 DROP FUNCTION IF EXISTS fun_case;
 4 /*声明结束符为$*/
 5 DELIMITER $
 6 /*创建函数*/
 7 CREATE FUNCTION fun_case(sex INT)
 8 RETURNS VARCHAR(20)
 9 BEGIN
10 DECLARE sexStr VARCHAR(20) DEFAULT '';
11 CASE sex 
12 WHEN 0 then set sexStr='';
13 WHEN 1 then set sexStr='';
14 ELSE set sexStr='不确定';
15 END CASE;
16 return sexStr;
17 END $
18 /*重置结束符为;*/
19 DELIMITER ;
20 
21 Query OK, 0 rows affected 

函数执行结果

 1 mysql> select studentname,fun_case(sex) from students where classid<>0;
 2 +-------------+---------------+
 3 | studentname | fun_case(sex) |
 4 +-------------+---------------+
 5 | brand       ||
 6 | helen       ||
 7 | lyn         ||
 8 | sol         ||
 9 | b1          ||
10 | b2          ||
11 | c1          ||
12 | c2          ||
13 | A           ||
14 +-------------+---------------+
15 9 rows in set 

循环语句while

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

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

1  ITERATE  loop_label;   --跳过当前循环
1 LEAVE  loop_label;   --结束循环
while 语法
1 [loop_label:]while condition do
2  --Todo:loop body
3 end while [loop_label]

loop_label:循环标签iterateleave结合用于在循环内部对循环进行控制:如:跳过本次循环、结束循环。

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

while示例

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

 1 /*先清除studentCount表记录*/
 2 truncate table studentcount;
 3 /*存储过程如果存在则删除*/
 4 DROP PROCEDURE IF EXISTS sp_while1;
 5 /*声明结束符为$*/
 6 DELIMITER $
 7 /*创建存储过程*/
 8 CREATE PROCEDURE sp_while1(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 1;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:WHILE idx<=varial_count DO
13       select studentname into uname from students where studentid = idx;
14       INSERT into studentCount values (idx,uname);
15       SET idx=idx+1;
16     END WHILE;
17   END $
18 /*结束符置为;*/
19 DELIMITER ; 

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

 1 mysql> CALL sp_while1(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         2 | helen       |
10 |         3 | lyn         |
11 |         4 | sol         |
12 |         5 | b1          |
13 |         6 | b2          |
14 |         7 | c1          |
15 |         8 | c2          |
16 |         9 | lala        |
17 |        10 | A           |
18 +-----------+-------------+
19 10 rows in set 
while示例:包含iterate/leave

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

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

 1 /*先清除studentCount表记录*/
 2 truncate table studentcount;
 3 /*存储过程如果存在则删除*/
 4 DROP PROCEDURE IF EXISTS sp_while2;
 5 /*声明结束符为$*/
 6 DELIMITER $
 7 /*创建存储过程*/
 8 CREATE PROCEDURE sp_while2(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:WHILE idx<=varial_count DO      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname为lala的同学,结束循环*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx为偶数,则跳过本次循环*/
19       ELSEIF idx%2=0 THEN
20         ITERATE loop_label;
21       END IF;      
22       INSERT into studentCount values (idx,uname);      
23     END WHILE;
24   END $
25 /*结束符置为;*/
26 DELIMITER ; 

调用存储过程,输出符合要求的数据:

 1 mysql> CALL sp_while2(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         3 | lyn         |
10 |         5 | b1          |
11 |         7 | c1          |
12 +-----------+-------------+
13 4 rows in set

循环语句repeat

repeat语法
1 [loop_label:]repeat
2 -- Todo loop body
3 until condition 
4 end repeat [loop_label]

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

 1 /*先清除studentCount表记录*/
 2 truncate table studentcount;
 3 /*存储过程如果存在则删除*/
 4 DROP PROCEDURE IF EXISTS sp_repeat;
 5 /*声明结束符为$*/
 6 DELIMITER $
 7 /*创建存储过程*/
 8 CREATE PROCEDURE sp_repeat(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:REPEAT      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname为lala的同学,结束循环*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx为偶数,则跳过本次循环*/
19       ELSEIF idx%2=0 THEN
20         ITERATE loop_label;
21       END IF;      
22       INSERT into studentCount values (idx,uname);  
23     UNTIL idx>varial_count   
24     END REPEAT;
25   END $
26 /*结束符置为;*/
27 DELIMITER ; 

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

 1 mysql> CALL sp_repeat(10);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         1 | brand       |
 9 |         3 | lyn         |
10 |         5 | b1          |
11 |         7 | c1          |
12 +-----------+-------------+
13 4 rows in set 

循环语句loop

loop语法
1 [loop_label:]loop
2 --Todo loop body
3 end loop [loop label]

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

 1 /*先清除studentCount表记录*/
 2 truncate table studentcount;
 3 /*存储过程如果存在则删除*/
 4 DROP PROCEDURE IF EXISTS sp_loop;
 5 /*声明结束符为$*/
 6 DELIMITER $
 7 /*创建存储过程*/
 8 CREATE PROCEDURE sp_loop(varial_count int)
 9   BEGIN
10     DECLARE idx int DEFAULT 0;
11     DECLARE uname VARCHAR(30) DEFAULT '';
12     loop_label:LOOP      
13       SET idx=idx+1;
14       select studentname into uname from students where studentid = idx;
15       /*如果遇到studentname为lala的同学,结束循环*/
16       IF uname='lala' THEN
17         LEAVE loop_label;
18       /*如果idx为偶数,则跳过本次循环*/
19       ELSEIF idx%2<>0 THEN
20         ITERATE loop_label;
21       /*这边加一个终结计数跳出的条件*/
22       ELSEIF idx>varial_count THEN
23         LEAVE loop_label;
24       END IF;      
25       INSERT into studentCount values (idx,uname); 
26       
27     END LOOP;
28   END $
29 /*结束符置为;*/
30 DELIMITER ; 

调用存储过程,并输出你需要的数据:

 1 mysql> CALL sp_loop(6);
 2 Query OK, 1 row affected
 3 
 4 mysql> select * from studentCount;
 5 +-----------+-------------+
 6 | studentid | studentname |
 7 +-----------+-------------+
 8 |         2 | helen       |
 9 |         4 | sol         |
10 |         6 | b2          |
11 +-----------+-------------+
12 3 rows in set

总结

1、了解了IF函数,它常用在SELECT语句中,类似于C#中的三元表达式。
2、IF条件表达式,类似于C#中的IF... ELSE...,多用于函数或存储过程中的判断选择逻辑。
3、了解CASE语句的两种用法,一种用在SELECT中使用,一种用在函数和存储过程中。
4、了解了三种循环体的使用,while、repeat分别对应C#中的while 和 do while循环,loop类似于一个while(true)的死循环。
5、循环体都包含在begin end中,循环体的控制依靠leave和iterate,leave相当于break,即退出整个循环体,iterate类似于continue,即跳过本次循环。

发表评论

评论已关闭。

相关文章