MySQL-SQL存储过程/触发器详解（下）

本文介绍: MySQL-SQL存储过程/触发器详解（下）

♥️作者：小刘在C站

♥️个人主页： 小刘主页

♥️努力不一定有回报，但一定会有收获加油！一起努力，共赴美好人生！

♥️学习两年总结出的运维经验，以及思科模拟器全套网络实验教程。专栏：云计算技术

♥️小刘私信可以随便问，只要会绝不吝啬，感谢CSDN让你我相遇！

前言

上章讲到MySQL-SQL存储过程/触发器详解（上）本章继续

1.4 i f

1). 介绍

if
用于做条件判断，具体的语法结构为：

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

在
if
条件判断的结构中，
ELSE IF
结构可以有多个，也可以没有。
ELSE
结构可以有，也可以没有。

2). 案例

根据定义的分数
score
变量，判定当前分数对应的分数等级。

score &g t;= 85
分，等级为优秀。

score &g t;= 60
分且
s core < 85
分，等级为及格。

s co re < 60
分，等级为不及格。

create procedure p3()
begin
declare score int default 58;
declare result varchar(10);
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
select result;
end;
call p3();

上述的需求我们虽然已经实现了，但是也存在一些问题，比如：
sco r e
分数我们是在存储过程中定义死的，而且最终计算出来的分数等级，我们也仅仅是最终查询展示出来而已。

那么我们能不能，把
sco r e
分数动态的传递进来，计算出来的分数等级是否可以作为返回值返回呢？答案是肯定的，我们可以通过接下来所讲解的参数来解决上述的问题。、

1.4.1参数

1). 介绍

参数的类型，主要分为以下三种：
IN
、
OUT
、
INOUT
。具体的含义如下：

用法：

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

2). 案例一

根据传入参数
sco r e
，判定当前分数对应的分数等级，并返回。

sc o r e >= 85
分，等级为优秀。

scor e >= 60
分且
score < 85
分，等级为及格。

score < 60
分，等级为不及格。

create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := '优秀';
elseif score >= 60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;

3). 案例二

将传入的200分制的分数，进行换算，换算成百分制，然后返回。

create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 198;
call p5(@score);
select @score;

1.4.2 case

1). 介绍

case
结构及作用，和我们在基础篇中所讲解的流程控制函数很类似。有两种语法格式：

语法
1
：

-- 含义： 当case_value的值为 when_value1时，执行statement_list1，当值为 when_value2时，
执行statement_list2， 否则就执行 statement_list
CASE case_value
WHEN when_value1 THEN statement_list1
[ WHEN when_value2 THEN statement_list2] ...
[ ELSE statement_list ]
END CASE;

语法2：

-- 含义： 当条件search_condition1成立时，执行statement_list1，当条件search_condition2成
立时，执行statement_list2， 否则就执行 statement_list
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_list2] ...
[ELSE statement_list]
END CASE;

2). 案例

根据传入的月份，判定月份所属的季节（要求采用
case
结构）。

1-3
月份，为第一季度

4-6
月份，为第二季度

7-9
月份，为第三季度

10-12
月份，为第四季度

create procedure p6(in month int)
begin
declare result varchar(10);
case
when month >= 1 and month <= 3 then
set result := '第一季度';
when month >= 4 and month <= 6 then
set result := '第二季度';
when month >= 7 and month <= 9 then
set result := '第三季度'; 
when month >= 10 and month <= 12 then
set result := '第四季度';
else
set result := '非法参数';
end case ;
select concat('您输入的月份为: ',month, ', 所属的季度为: ',result);
end;
call p6(16);

如果判定条件有多个，多个条件之间，可以使用
and
或
or
进行连接。

1.4.3while

1). 介绍

while
循环是有条件的循环控制语句。满足条件后，再执行循环体中的
SQL
语句。具体语法为：

-- 先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;

2). 案例

计算从
1
累加到
n
的值，
n
为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行减1 , 如果n减到0, 则退出循环
create procedure p7(in n int)
begin
declare total int default 0;
while n>0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;
call p7(100);

1.4.4repeat

1). 介绍

repeat
是有条件的循环控制语句
,
当满足
until
声明的条件的时候，则退出循环。具体语法为：

-- 先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑...
UNTIL 条件
END REPEAT;

2). 案例

计算从
1
累加到
n
的值，
n
为传入的参数值。
(
使用
repeat
实现
)

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0
end repeat;
select total;
end;
call p8(10);
call p8(100);

1.4.5 loop

1). 介绍

LOOP
实现简单的循环，如果不在
SQL
逻辑中增加退出循环的条件，可以用其来实现简单的死循环。
OOP
可以配合一下两个语句使用：
LEAVE
：配合循环使用，退出循环。

ITERATE
：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。


[begin_label:] LOOP
SQL逻辑...
END LOOP [end_label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

上述语法中出现的
begin_label
，
end_label
，
label
指的都是我们所自定义的标记。

2). 案例一

计算从
1
累加到
n
的值，
n
为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
create procedure p9(in n int)
begin

declare total int default 0;

sum:loop

if n<=0 then
leave sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p9(100);

3). 案例二

计算从
1
到
n
之间的偶数累加的值，
n
为传入的参数值。

-- A. 定义局部变量, 记录累加之后的值;
-- B. 每循环一次, 就会对n进行-1 , 如果n减到0, 则退出循环 ----> leave xx
-- C. 如果当次累加的数据是奇数, 则直接进入下一次循环. --------> iterate xx
create procedure p10(in n int)
begin
declare total int default 0;
sum:loop
if n<=0 then
leave sum;
end if;
if n%2 = 1 then
set n := n - 1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p10(100);

1.4.6

1). 介绍

游标（
CURSOR
）是用来存储查询结果集的数据类型
,
在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、
OPEN
、
FETCH
和
CLOSE
，其语法分别如下。

A. 声明游标

DECLARE 游标名称 CURSOR FOR 查询语句 ;

B. 打开 游标

OPEN 游标名称 ;

C. 获取 游标 记录

FETCH 游标名称 INTO 变量 [, 变量 ] ;

D. 关闭游标

CLOSE 游标名称 ;

2). 案例

根据传入的参数
uage
，来查询用户表
tb_user
中，所有的用户年龄小于等于
uage
的用户姓名

（
name
）和专业（
pro fe ss ion
），并将用户的姓名和专业插入到所创建的一张新表

(id,name,pro fe ss ion)
中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);

上述的存储过程，最终我们在调用的过程中，会报错，之所以报错是因为上面的
w hile
循环中，并没有退出条件。当游标的数据集获取完毕之后，再次获取数据，就会报错，从而终止了程序的执行。

但是此时，
tb_user_pro
表结构及其数据都已经插入成功了，我们可以直接刷新表结构，检查表结构中的数据。

上述的功能，虽然我们实现了，但是逻辑并不完善，而且程序执行完毕，获取不到数据，数据库还报错。接下来，我们就需要来完成这个存储过程，并且解决这个问题。

要想解决这个问题，就需要通过
MySQL
中提供的条件处理程序
Handler
来解决。

1.4.7条件处理程序

1). 介绍

条件处理程序（
Handl er
）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE handler_action HANDLER FOR condition_value [, condition_value]
... statement ;
handler_action 的取值：
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
condition_value 的取值：
SQLSTATE sqlstate_value: 状态码，如 02000
SQLWARNING: 所有以01开头的SQLSTATE代码的简写
NOT FOUND: 所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

2). 案例

我们继续来完成在上一小节提出的这个需求，并解决其中的问题。

根据传入的参数
uage
，来查询用户表
tb_user
中，所有的用户年龄小于等于
uage
的用户姓名

（
name
）和专业（
profession
），并将用户的姓名和专业插入到所创建的一张新表

(id,name,profession)
中。

A.
通过
SQLSTATE
指定具体的状态码

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标
create procedure p11(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p11(30);

B.
通过
SQLSTATE
的代码简写方式
NOT FOUND

02
开头的状态码，代码简写为
NOT FOUND

create procedure p12(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name,profession from tb_user where age <=
uage;
-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02开头时，将关闭游标u_cursor，并退出
declare exit handler for not found close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into uname,upro;
insert into tb_user_pro values (null, uname, upro);
end while;
close u_cursor;
end;
call p12(30);