【MySQL学习笔记】MySQL存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程就是数据库 SQL 语言层面的代码封装与重用。
特点：
1- 封装、复用
2- 可以接收参数，也可以返回数据
3- 减少网络交互，效率提升

1、基础语法

创建

-- 创建存储过程
create procedure 存储过程名称([参数列表])
beginSQL 语句;
end;-- 范例
create procedure p1()
beginselect * from user;select id, name from emp where id < 10;
end;

在命令行中，执行创建存储过程的 SQL 时，需要通过关键字 delimiter 指定 SQL 语句的结束符。

-- 例如指定 && 为 SQL 语句的结束符
delimiter &&

调用

-- 调用存储过程
call 存储过程名称([参数列表]);-- 范例
call p1();

查看

-- 查询指定数据库的存储过程及状态信息
-- xxx：指定数据库
select * from information_schema.routines where routine_schema = 'xxx';-- 查询某个存储过程的定义
show create procedure 存储过程名称;

删除

-- 删除存储过程
drop procedure [if exists] 存储过程名称;

2、变量

变量有三种：系统变量，用户自定义变量，局部变量。

2.1 系统变量

系统变量是 MySQL 服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）。默认是 session 级别。
查看系统变量语法：

-- 查看所有系统变量
show [global | session] variables;-- 可以通过 like 模糊匹配方式查找变量
show [global | session] variables like '...';-- 查看指定变量的值，注意global，session后面需要加'.'
select @@[global. | session.]系统变量名;

设置系统变量语法：

set [global | session] 系统变量名 = 值;set @@[global | session]系统变量名 = 值;

2.2 用户自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 “@变量名” 使用就可以了。其作用域为当前连接。
给用户自定义变量赋值语法：

-- 使用 set 方式赋值
set @var_name = expr [, @var_name = expr...];
-- 推荐使用 ':=' 进行赋值。因为 MySQL 中的赋值运算符和比较运算符都是 '='，为了区分，推荐使用 ':='
set @var_name := expr [, @var_name := expr...];

-- 使用 select 方式赋值
select @var_name := expr [, @var_name := expr...];-- 将 table_name 表中的 column_name 字段的值赋值给 @var_name 变量
select column_name into @var_name from table_name;

使用自定义变量语法：

select @var_name [, @var_name...];

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为 NULL。

2.3 局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要 declare 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在存储过程内声明的 begin … end 块内。
局部变量声明语法：

-- 声明局部变量需要指定变量类型
-- 变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等。
-- 可以使用 default 指定默认值
declare 变量名 变量类型 [default ...];

局部变量赋值语法：

set 变量名 = 值;set 变量名 := 值;select 字段名 into 变量名 from 表名...;

3、if 流程控制

语法：

if 条件1 then...
elseif 条件2 then  -- 可选...
else  			   -- 可选...
end if;

案例：

-- 根据定义的分数 score 变量，判定当前分数对应的分数等级
-- score >= 85，等级为优秀
-- score >= 60，且 score < 85，等级为及格
-- score < 60，等级为不及格create procedure p1()
begindeclare score int default 58;declare result varchar(10);if score >= 85 thenset result := '优秀';elseif score >= 60 thenset result := '及格';else set result := '不及格';end if;select result;
end;

4、参数

存储过程的参数有三种：

类型	含义
in	该类参数作为输入，也就是需要调用时传入值（默认）
out	该类参数作为输出，也就是该参数可以作为返回值
inout	既可以作为输入参数，也可以作为输出参数

语法：

create procedure 存储过程名称([in/out/inout  参数名 参数类型])
begin-- sql 语句
end;

案例1：成绩等级评定

-- 根据传入的分数 score 变量，判定当前分数对应的分数等级，并返回
-- score >= 85，等级为优秀
-- score >= 60，且 score < 85，等级为及格
-- score < 60，等级为不及格create procedure p1(in score int, out result varchar(10))
beginif score >= 85 thenset result := '优秀';elseif score >= 60 thenset result := '及格';elseset result := '不及格';end if;
end;-- 调用该存储过程
-- 输出参数需要用户自定义变量接收
call p1(68, @result);
-- 展示结果
select @result;

案例2：成绩转换

-- 将传入的 200 分制的分数，进行换算，换算成百分制，然后返回
create procedure p2(inout score double)
beginset score := score * 0.5;
end;-- 由于是 inout 类型的参数，所以在调用该存储过程前需要准备一个自定义变量并赋值
set @score := 78;
-- 调用该存储过程
call p2(@score);
-- 展示转换结果
select @score;

5、case 流程控制

语法：

-- 语法一
case case_valuewhen when_value1 then statement_list1[when when_value2 then statement_list2]...[else statement_list]
end case;-- 语法二
case when search_condition1 then statement_list1[when search_condition2 then statement_list2]...[else statement_list]
end case;

案例：

-- 根据传入的月份，判定月份所属的季节（要求采用 case 结构）
-- 1. 1-3月份，为第一季度
-- 2. 4-6月份，为第二季度
-- 3. 7-9月份，为第三季度
-- 4. 10-12月份，为第四季度
create procedure p3(in month int, out quarter varchar(10))
begincase when month >= 1 and month <= 3 then set quarter := '第一季度';when month >= 4 and month <= 6 then set quarter := '第二季度';when month >= 7 and month <= 9 then set quarter := '第三季度';when month >= 10 and month <= 12 then set quarter := '第四季度';else set quarter := '非法参数';end case;
end;

6、循环结构

6.1 while 循环

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

-- 先判定条件，如果条件为 true，则执行逻辑，否则，不执行逻辑
while 条件 doSQL 逻辑...
end while;

案例：

-- 计算从 1 累加到 n 的值，n 为传入的参数值。
create procedure p4(in n int)
begindeclare total int default 0;	while n > 0 doset total := total + n;set n := n - 1;end while;select total;
end;

6.2 repeat 循环

repeat 是有条件的循环控制语句，当满足条件的时候退出循环。
语法：

-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。
-- 注意 until 后面没有结束符
repeat SQL 逻辑...
until 条件
end repeat;

案例：

-- 计算从 1 累加到 n 的值，n 为传入的参数值。
create procedure p5(in n int)
begindeclare total int default 0;repeatset total := total + n;set n := n - 1;until n = 0end repeat;select total;
end;

6.3 loop 循环

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

• leave：配合循环使用，退出循环。（功能相当于 break）
• iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。（功能相当于 continue）

语法：

-- 对于 loop 循环可以设置一个标记。
-- begin_label 就是自定义的标记，来标识当前的 loop 循环
[begin_label:] loopSQL 逻辑...
end loop [end_label];

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

案例1：

-- 计算从 1 累加到 n 的值，n 为传入的参数值
create procedure p6(in n int)
begindeclare total int default 0;sum:loopif n = 0 thenleave sum;end if;set total := total + n;set n := n - 1;end loop sum;select total;
end;

案例2：

-- 计算从 1 到 n 之间的偶数累加的值，n 为传入的参数值
create procedure p7(in n int)
begindeclare total int default 0;sum:loopif n = 0 thenleave sum;else if mod(n, 2) = 1 thenset n := n - 1;iterate sum;end if;set total := total + n;set n := n - 1;end loop sum;select total;
end;

7、游标

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

-- 声明游标
declare 游标名称 cursor for 查询语句;-- 打开游标
open 游标名称;-- 获取游标记录
fetch 游标名称 into 变量 [, 变量];-- 关闭游标
close 游标名称;

案例：
根据传入的参数 uage，来查询用户表 tb_user 中，所有的用户年龄小于等于 uage 的用户姓名（name）和职业（job），并将用户的姓名和职业插入到所创建的一张新表（id, name, job）中。

-- 逻辑：
-- A. 声明游标，存储查询结果集
-- B. 准备：创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标create procedure p8(in uage int)
begindeclare uname varchar(10); -- 注意：需要先声明变量，再声明游标declare ujob varchar(10);declare u_cursor cursor for select name, job from user where age <= uage;drop table if exists user_job;create table if not exists user_job(id int primary auto_increment commit '主键ID',name varchar(10) unique commit '姓名',job varchar(10) commit '职业') commit '用户职业表';open u_cursor;while true dofetch u_cursor into uname, ujob;insert into user_job values(null, uname, ujob);end while; close u_cursor;
end;-- 调用存储过程 p8()
call p8(40);

注意：需要先声明变量，再声明游标。
上面的代码执行会报错，这是因为 while 的条件为 true，为死循环，而循环体中，u_cursor 游标遍历完后，就拿不到数据了，再向 user_job 表插入数据就会报错。报错信息为：

[02000][1329] No data -zero rows fetched, selected, or procedded

可以通过条件处理程序解决这个问题。条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
条件处理程序语法：

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 代码的简写-- condition_value 可以不指定具体的状态码，若要捕获所有以 02 开头的状态码，可以直接写 not found。

完善后的 sql 如下;

create procedure p8(in uage int)
begin-- 注意：需要先声明变量，再声明游标declare uname varchar(10); declare ujob varchar(10);declare u_cursor cursor for select name, job from user where age <= uage;-- 声明条件处理程序-- 指定处理行为是 exit 退出循环-- 指定条件为 sql 状态码为 02000 时触发-- 退出循环后运行的语句为关闭游标 close u_cursor;declare exit handler for sqlstate '02000' close u_cursor;drop table if exists user_job;create table if not exists user_job(id int primary auto_increment commit '主键ID',name varchar(10) unique commit '姓名',job varchar(10) commit '职业') commit '用户职业表';open u_cursor;while true dofetch u_cursor into uname, ujob;insert into user_job values(null, uname, ujob);end while; close u_cursor;
end;-- 调用存储过程 p8()
call p8(40);