一、流程控制
1、SQL执行的流程分为三大类
- 顺序结构:程序从上往下依次执行
- 分支结构:程序按照条件进行选择执行,从两条或者多条路径中选择一条进行执行
- 循环结构:程序在满足一定条件时,重复执行一组语句
2、针对于MySQL的流程控制语句有三类,但只能用于存储过程、存储函数、触发器和事件
中
- 条件判断语句:
IF
和CASE
语句 - 循环语句:
LOOP
、WHILE
和REPEAT
语句 - 跳转语句:
ITERATE
和LEAVE
语句
二、分支结构
2.1 分支结构之IF
1、语法
- 使用
IF
开头,END IF
结尾,满足哪个条件就执行对应条件THEN
关键字后面的操作语句 ELSEIF
和ELSE
不是必须要有的,你有多个条件要判断才需要考虑加不加- 注意:
ELSE
后面不需要条件和THEN
IF 条件1 THEN 操作1
[ELSEIF 条件2 THEN 操作2]...
[ELSE 操作N]
END IF;
2、举例
1)判断的变量不需要查询
DELIMITER //
CREATE PROCEDURE testIf()
BEGIN
# 情况1:定义一个局部变量测试简单IF
DECLARE var1 VARCHAR(10);
# 情况2:定义一个局部变量测试IF和ELSE
DECLARE var2 VARCHAR(10);
# 情况3:定义一个局部变量测试IF..ELSEIF和ELSE
DECLARE age_var int(10) DEFAULT 25;
IF var1 is NULL
THEN SELECT 'var1 is null';
END IF;
IF var2 is NULL
THEN SELECT 'var2 is null';
ELSE
SELECT 'var2 is not null';
END IF;
IF age_var < 18
THEN SELECT 'var3 is child';
ELSEIF age_var < 30
THEN SELECT 'var3 is teenagers';
ELSE
SELECT 'var3 is older adults';
END IF;
END //
DELIMITER ;
# 调用存储过程
call testIf();
# 删除存储过程
drop PROCEDURE testIf;
2)判断的变量需要查询
我们就新建一个表,内容是博客名字、作者和浏览量
CREATE TABLE `t_blog_view` (
`blog_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客名称',
`blog_author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客作者',
`blog_views` int(20) NOT NULL COMMENT '博客浏览量'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `t_blog_view` VALUES ( 'Mybatis系列', 'Decade0712', 2000);
INSERT INTO `t_blog_view` VALUES ( 'Spring系列', 'Decade0712', 4000);
INSERT INTO `t_blog_view` VALUES ( '设计模式系列', 'Decade0712', 6000);
INSERT INTO `t_blog_view` VALUES ( 'JVM系列', '十年', 8000);
INSERT INTO `t_blog_view` VALUES ( 'MySQL基础', 'Decade0712', 4000);
INSERT INTO `t_blog_view` VALUES ( 'Java8新特性', '十年', 5500);
表t_blog_view的内容如下
现在我们做出如下规定
如果博客浏览量小于或等于3500,而且是Decade0712写的,就是低阅读量
博客浏览量大于3500小于5500,就是中等阅读量
否则就是高阅读量
DELIMITER //
CREATE PROCEDURE testIf2(IN blogName VARCHAR(50),OUT res VARCHAR(10))
BEGIN
# 定义变量,用来存储查询出来的字段
DECLARE author VARCHAR(50);
DECLARE views int(20);
SELECT blog_author INTO author FROM t_blog_view WHERE blog_name = blogName;
SELECT blog_views INTO views FROM t_blog_view WHERE blog_name = blogName;
IF views <= 3500 AND author = 'Decade0712'
THEN SET res = '低浏览量';
ELSEIF 3500 < views AND views < 5500
THEN SET res = '中浏览量';
ELSE
SET res = '高浏览量';
END IF;
END //
DELIMITER ;
# 声明变量
SET @blogName = 'Spring系列';
SET @blogName2 = 'Mybatis系列';
SET @blogName3 = 'JVM系列';
# 调用存储过程
call testIf2(@blogName, @res);
call testIf2(@blogName2, @res2);
call testIf2(@blogName3, @res3);
# 查询结果
select @res,@res2,@res3;
# 删除存储过程
drop PROCEDURE testIf2;
结果如下
2.2 分支结构之CASE
1、语法
- 情况一:类似于Java的switch
CASE 表达式
WHEN 值1 THEN 结果1或者语句1(如果是语句需要加分号)
WHEN 值2 THEN 结果2或者语句2(如果是语句需要加分号)
...
ELSE 结果n或者语句n(如果是语句需要加分号)
END [CASE](如果放在begin...end中需要加CASE,如果是放在SELECT后面,就不需要)
- 情况二:类似于Java的多重if
CASE
WHEN 条件1 THEN 结果1或者语句1(如果是语句需要加分号)
WHEN 条件2 THEN 结果2或者语句2(如果是语句需要加分号)
...
ELSE 结果n或者语句n(如果是语句需要加分号)
END [CASE](如果放在begin...end中需要加CASE,如果是放在SELECT后面,就不需要)
2、简单案例
DELIMITER //
CREATE PROCEDURE tesCase()
BEGIN
# 定义变量
DECLARE var1 INT DEFAULT 2;
DECLARE var2 INT DEFAULT 20;
CASE var1
WHEN 1 THEN SELECT 'var1 is 1';
WHEN 2 THEN SELECT 'var1 is 2';
WHEN 3 THEN SELECT 'var1 is 3';
ELSE SELECT 'var1 is other value';
END CASE;
CASE
WHEN var2 > 100 THEN SELECT 'var2 is 百位数';
WHEN var2 > 10 THEN SELECT 'var2 is 十位数';
ELSE SELECT 'var2 is 个位数';
END CASE;
END //
DELIMITER ;
# 调用存储过程
CALL tesCase();
# 删除存储过程
DROP PROCEDURE tesCase;
执行结果如下
3、复杂案例
- 测试语法1:我们还是使用上面的t_blog_view表
如果博客浏览量小于或等于3500,而且是Decade0712写的,就是低阅读量
博客浏览量大于3500小于5500,就是中等阅读量
否则就是高阅读量
因为这里不是直接判断值,而是要做范围的条件判断,所以使用语法二
DELIMITER //
CREATE PROCEDURE tesCase2(IN blogName VARCHAR(50),OUT res VARCHAR(10))
BEGIN
# 定义变量,用来存储查询出来的字段
DECLARE author VARCHAR(50);
DECLARE views int(20);
SELECT blog_author INTO author FROM t_blog_view WHERE blog_name = blogName;
SELECT blog_views INTO views FROM t_blog_view WHERE blog_name = blogName;
CASE
WHEN views <= 3500 AND author = 'Decade0712' THEN SET res = '低浏览量';
WHEN 3500 < views AND views < 5500 THEN SET res = '中浏览量';
ELSE SET res = '高浏览量';
END CASE;
END //
DELIMITER ;
# 声明变量
SET @blogName = 'Spring系列';
SET @blogName2 = 'Mybatis系列';
SET @blogName3 = 'JVM系列';
# 调用存储过程
call tesCase2(@blogName, @res);
call tesCase2(@blogName2, @res2);
call tesCase2(@blogName3, @res3);
# 查询结果
select @res,@res2,@res3;
# 删除存储过程
drop PROCEDURE tesCase2;
结果如下
- 测试语法2:我们还是使用上面的t_blog_view表
如果博客作者是Decade0712,就输出作者是Decade0712
如果博客作者是十年,就输出作者是十年
否则输出是其他大佬写的
DELIMITER //
CREATE PROCEDURE tesCase3(IN blogName VARCHAR(50),OUT res VARCHAR(100))
BEGIN
# 定义变量,用来存储查询出来的字段
DECLARE author VARCHAR(50);
SELECT blog_author INTO author FROM t_blog_view WHERE blog_name = blogName;
CASE author
WHEN 'Decade0712' THEN SET res = '作者是Decade0712';
WHEN '十年' THEN SET res = '作者是十年';
ELSE SET res = '其他大佬写的';
END CASE;
END //
DELIMITER ;
# 声明变量
SET @blogName = 'Spring系列';
SET @blogName2 = 'JVM系列';
# 调用存储过程
call tesCase3(@blogName, @res);
call tesCase3(@blogName2, @res2);
# 查询结果
select @res,@res2;
# 删除存储过程
drop PROCEDURE tesCase3;
结果如下
三、循环结构
凡是循环条件,一定具备四个要素 :
- 初始化条件:初始化循环条件中需要用到的变量
- 循环条件:满足此条件,就执行循环体
- 循环体:满足循环条件就会被重复执行的语句
- 迭代条件:每完成一次循环,就要刷新循环条件相关的变量,防止出现死循环
3.1、循环结构之LOOP
1、语法
[loop_label:] LOOP
循环体
END LOOP [loop_label:];
loop_label表示LOOP语句的标注名称,该参数可省略
LOOP语句里可以使用IF语句来进行判断,如果满足IF语句的条件,就使用LEAVE离开循环,使用leave
2、简单案例
我们定义一个变量num的初始值是1,每循环一次就让其自增1,当它大于等于10的之后终止循环
DELIMITER //
CREATE PROCEDURE testLoop()
BEGIN
# 声明变量num(初始化条件)
DECLARE num INT DEFAULT 1;
# 定义loop
loop_name:LOOP
# 循环体(和迭代条件)
SET num = num + 1;
# 什么时候结束循环,使用leave,后面跟离开哪个循环(循环条件)
IF num >= 10 THEN LEAVE loop_name;
END IF;
END LOOP loop_name;
# 查看num是否大于等于10
SELECT num;
END //
DELIMITER ;
# 调用
CALL testLoop();
# 删除存储过程
DROP PROCEDURE testLoop;
结果如下
3、复杂案例
我们把上面的表进行一个copy,命名为t_blog_view_for_loop
我们使用SELECT AVG(blog_views) FROM t_blog_view_for_loop;
查询一下博客的平均浏览量
然后我们假设从今天开始,每天都有人来翻阅博客,每天博客的浏览量会在原来的基础上乘以1.5
判断一下,需要多少天平均浏览量能超过10000
DELIMITER //
CREATE PROCEDURE testLoop2(OUT res INT)
BEGIN
# 定义变量用来存储平均浏览量
DECLARE avg_view DOUBLE;
# 定义变量用来存储需要多少天才能满足平均浏览量大于10000(初始化条件)
DECLARE view_days INT DEFAULT 0;
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_loop;
loop_name2:LOOP
# 结束循环的条件(循环条件)
IF avg_view > 10000 THEN LEAVE loop_name2;
END IF;
# 循环体
UPDATE t_blog_view_for_loop SET blog_views = blog_views * 1.5;
# 刷新判断条件,防止死循环(迭代条件)
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_loop;
# 刷新天数
SET view_days = view_days + 1;
END LOOP loop_name2;
# 把结果赋值给输出变量
SET res = view_days;
END //
DELIMITER ;
# 调用存储过程
CALL testLoop2(@res);
SELECT @res;
SELECT AVG(blog_views) FROM t_blog_view_for_loop;
# 删除存储过程
DROP PROCEDURE testLoop2;
执行结果如下
3.2、循环结构之WHILE
1、语法
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label:];
while_label表示WHILE语句的标注名称,该参数可省略
循环条件为真,就执行循环体语句,直到循环条件为假时,退出循环
2、简单案例
我们使用和上面LOOP一样的简单案例
定义一个变量num的初始值是1,每循环一次就让其自增1,当它小于等于10的时候执行循环体
DELIMITER //
CREATE PROCEDURE testWhile()
BEGIN
# 声明变量num(初始化条件)
DECLARE num INT DEFAULT 1;
# 什么时候结束循环,使用leave,后面跟离开哪个循环(循环条件)
while_name:WHILE num<= 10 DO
# 循环体(和迭代条件)
SET num = num + 1;
END WHILE while_name;
# 查看num是否大于等于10
SELECT num;
END //
DELIMITER ;
# 调用
CALL testWhile();
# 删除存储过程
DROP PROCEDURE testWhile;
结果如下
3、复杂案例
我们把上面的表进行一个copy,命名为t_blog_view_for_while
我们使用SELECT AVG(blog_views) FROM t_blog_view_for_while;
查询一下博客的平均浏览量
然后我们假设从今天开始,每天都有人来翻阅博客,每天博客的浏览量会在原来的基础上乘以1.5
判断一下,需要多少天平均浏览量能超过10000
DELIMITER //
CREATE PROCEDURE testWhile2(OUT res INT)
BEGIN
# 定义变量用来存储平均浏览量
DECLARE avg_view DOUBLE;
# 定义变量用来存储需要多少天才能满足平均浏览量大于10000(初始化条件)
DECLARE view_days INT DEFAULT 0;
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_while;
# 结束循环的条件(循环条件)
while_name2:WHILE avg_view <= 10000 DO
# 循环体
UPDATE t_blog_view_for_while SET blog_views = blog_views * 1.5;
# 刷新判断条件,防止死循环(迭代条件)
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_while;
# 刷新天数
SET view_days = view_days + 1;
END WHILE while_name2;
# 把结果赋值给输出变量
SET res = view_days;
END //
DELIMITER ;
# 调用存储过程
CALL testWhile2(@res);
SELECT @res;
SELECT AVG(blog_views) FROM t_blog_view_for_while;
# 删除存储过程
DROP PROCEDURE testWhile2;
执行结果如下
3.3、循环结构之REPEAT
1、语法
[repeat_label:] REPEAT
循环体
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label:];
repeat_label表示REPEAT语句的标注名称,该参数可省略
与前面WHILE不同的是,REPEAT语句上来就会执行一次循环体
(类似于Java中的while和do…while的区别,但是Java中的do…while是条件为真时继续执行循环,repeat中条件为真就退出循环了)
然后在UNTIL中进行条件判断,如果满足就退出,否则就继续执行循环体
2、简单案例
我们使用和上面LOOP一样的简单案例
定义一个变量num的初始值是1,每循环一次就让其自增1,当它小于等于10的时候执行循环体
DELIMITER //
CREATE PROCEDURE testRepeat()
BEGIN
# 声明变量num(初始化条件)
DECLARE num INT DEFAULT 1;
repeat_name:REPEAT
# 循环体(和迭代条件)
SET num = num + 1;
UNTIL num > 10
END REPEAT repeat_name;
# 查看num是否大于等于10
SELECT num;
END //
DELIMITER ;
# 调用存储过程
CALL testRepeat();
# 删除存储过程
DROP PROCEDURE testRepeat;
执行结果如下
3、复杂案例
我们把上面的表进行一个copy,命名为t_blog_view_for_repeat
我们使用SELECT AVG(blog_views) FROM t_blog_view_for_repeat;
查询一下博客的平均浏览量
然后我们假设从今天开始,每天都有人来翻阅博客,每天博客的浏览量会在原来的基础上乘以1.5
判断一下,需要多少天平均浏览量能超过10000
DELIMITER //
CREATE PROCEDURE testRepeat2(OUT res INT)
BEGIN
# 定义变量用来存储平均浏览量
DECLARE avg_view DOUBLE;
# 定义变量用来存储需要多少天才能满足平均浏览量大于10000(初始化条件)
DECLARE view_days INT DEFAULT 0;
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_repeat;
# 结束循环的条件(循环条件)
repeat_name2:REPEAT
# 循环体
UPDATE t_blog_view_for_repeat SET blog_views = blog_views * 1.5;
# 刷新判断条件,防止死循环(迭代条件)
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_repeat;
# 刷新天数
SET view_days = view_days + 1;
UNTIL avg_view > 10000
END REPEAT repeat_name2;
# 把结果赋值给输出变量
SET res = view_days;
END //
DELIMITER ;
# 调用存储过程
CALL testRepeat2(@res);
SELECT @res;
SELECT AVG(blog_views) FROM t_blog_view_for_repeat;
# 删除存储过程
DROP PROCEDURE testRepeat2;
执行结果如下
3.4、总结
- 要使用leave结束循环,就必须给循环结构取名,不管是LOOP、WHILE还是REPEAT
- 三种循环结构的对比:
- LOOP:一般用于实现简单的死循环
- WHILE:先判断再执行
- REPEAT:先执行,再进行判断,无条件执行一次循环体
四、跳转语句
4.1 LEAVE
1、使用在循环语句
或者BEGIN...END包裹的程序体
内,表示跳出循环或者跳出程序体
可以把它理解为Java里的break
语法为
LEAVE 标记名
所以不管是循环语句
或者BEGIN...END包裹的程序体
,在创建的时候都需要声明一个标记名
2、使用在BEGIN…END包裹的程序体中
创建存储过程test_leave(),声明一个INT的入参
如果num<=0,那么就使用leave语句退出begin…end程序
如果num=1,那么就查询最开始t_blog_view表中的平均浏览量
如果num=2,那么就查询最开始t_blog_view表中的最低浏览量
如果num>2,那么就查询最开始t_blog_view表中的最高浏览量
IF语句结束后,查询最开始t_blog_view表中的博客总数
DELIMITER //
CREATE PROCEDURE testLeave(IN num INT)
leave_label:BEGIN
IF num <= 0
THEN LEAVE leave_label;
ELSEIF num = 1
THEN select AVG(blog_views) from t_blog_view;
ELSEIF num = 2
THEN select MIN(blog_views) from t_blog_view;
ELSE
select MIN(blog_views) from t_blog_view;
END IF;
select count(*) from t_blog_view;
END //
DELIMITER ;
# 调用存储过程
CALL testLeave(1);
# 删除存储过程
DROP PROCEDURE testLeave;
执行结果如下
3、在循环语句中使用
我们把上面的表进行一个copy,命名为t_blog_view_for_leave
我们使用SELECT AVG(blog_views) FROM t_blog_view_for_repeat;
查询一下博客的平均浏览量
然后我们假设从今天开始,每天都有人来翻阅博客,每天博客的浏览量会在原来的基础上乘以1.5
判断一下,需要多少天平均浏览量能超过10000
DELIMITER //
CREATE PROCEDURE testLeave2(OUT res INT)
BEGIN
# 定义变量用来存储平均浏览量
DECLARE avg_view DOUBLE;
# 定义变量用来存储需要多少天才能满足平均浏览量大于10000(初始化条件)
DECLARE view_days INT DEFAULT 0;
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_leave;
while_lable:WHILE TRUE DO
# 条件判断,什么时候跳出循环
IF avg_view > 10000
THEN LEAVE while_lable;
END IF;
# 循环体
UPDATE t_blog_view_for_leave SET blog_views = blog_views * 1.5;
# 刷新判断条件,防止死循环(迭代条件)
SELECT AVG(blog_views) INTO avg_view FROM t_blog_view_for_leave;
# 刷新天数
SET view_days = view_days + 1;
END WHILE;
# 把结果赋值给输出变量
SET res = view_days;
END //
DELIMITER ;
# 调用存储过程
CALL testLeave2(@res);
SELECT @res;
SELECT AVG(blog_views) FROM t_blog_view_for_leave;
# 删除存储过程
DROP PROCEDURE testLeave2;
执行结果如下
4.2 ITERATE
1、只能
使用在循环语句
内,表示重新开始循环
可以把它理解为Java里的continue
语法为
ITERATE label
label表示循环的标记名
2、举例
定义一个变量num,初始值为0,循环结构中执行num+1的操作
当num<10的时候,继续执行循环体
当num>15的时候,退出循环体
DELIMITER //
CREATE PROCEDURE testIterate()
BEGIN
DECLARE num INT DEFAULT 0;
loop_label:LOOP
# num自增
SET num = num + 1;
# 当num小于10的时候,当前循环执行到这里就结束了,最后面的SELECT语句不会执行
IF num < 10
THEN ITERATE loop_label;
# 当num大于15的时候,结束LOOP循环
ELSEIF num > 15
THEN LEAVE loop_label;
END IF;
# 如果既不执行iterate,也不执行leave,那么每循环一次,就会输出一次这个语句
SELECT CONCAT('变量:',num,'在10到15之间');
END LOOP;
END //
DELIMITER ;
# 调用存储过程
CALL testIterate();
# 删除存储过程
DROP PROCEDURE testIterate;
执行结果如下
我们发现,满足iterate的判断条件时,本次循环结束,后面的语句都不会执行
如有错误,欢迎指正!!!