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

最新推荐文章于 2024-07-25 16:06:56 发布

smart哥

最新推荐文章于 2024-07-25 16:06:56 发布

阅读量584

点赞数 20

分类专栏： mysql专题文章标签： mysql

本文链接：https://blog.csdn.net/smart_an/article/details/138434880

版权

mysql专题专栏收录该内容

30 篇文章 0 订阅

订阅专栏

作者简介：大家好，我是smart哥，前中兴通讯、美团架构师，现某互联网公司CTO

联系qq：184480602，加我进群，大家一起学习，一起进步，一起对抗互联网寒冬

学习必须往深处挖，挖的越深，基础越扎实！

阶段1、深入多线程

 阶段2、深入多线程设计模式

 阶段3、深入juc源码解析

阶段4、深入jdk其余源码解析

阶段5、深入jvm源码解析

码哥源码部分

码哥讲源码-原理源码篇【2024年最新大厂关于线程池使用的场景题】

码哥讲源码【炸雷啦！炸雷啦！黄光头他终于跑路啦！】

码哥讲源码-【jvm课程前置知识及c/c++调试环境搭建】

码哥讲源码-原理源码篇【揭秘join方法的唤醒本质上决定于jvm的底层析构函数】

码哥源码-原理源码篇【Doug Lea为什么要将成员变量赋值给局部变量后再操作？】

码哥讲源码【你水不是你的错,但是你胡说八道就是你不对了！】

码哥讲源码【谁再说Spring不支持多线程事务，你给我抽他！】

终结B站没人能讲清楚红黑树的历史，不服等你来踢馆！

打脸系列【020-3小时讲解MESI协议和volatile之间的关系，那些将x86下的验证结果当作最终结果的水货们请闭嘴】

背景

说到流程控制语句，我们在程序语法中用的比较多，比如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：循环标签，和iterate、leave结合用于在循环内部对循环进行控制：如：跳过本次循环、结束循环。

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，即跳过本次循环。

smart哥

关注

20
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
MySQL全面瓦解20：可编程性之流程控制语句

1、了解了IF函数，它常用在SELECT语句中，类似于C#中的三元表达式。2、IF条件表达式，类似于C#中的IF... ELSE...，多用于函数或存储过程中的判断选择逻辑。3、了解CASE语句的两种用法，一种用在SELECT中使用，一种用在函数和存储过程中。4、了解了三种循环体的使用，while、repeat分别对应C#中的while 和 do while循环，loop类似于一个while(true)的死循环。
复制链接

扫一扫

专栏目录