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

1、基础语法

创建

-- 创建存储过程
create procedure 存储过程名称([参数列表])
begin
	SQL 语句;
end;

-- 范例
create procedure p1()
begin
	select * 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()
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;

4、参数

存储过程的参数有三种：

语法：

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))
begin
	if score >= 85 then
		set result := '优秀';
	elseif score >= 60 then
		set result := '及格';
	else
		set result := '不及格';
	end if;
end;

-- 调用该存储过程
-- 输出参数需要用户自定义变量接收
call p1(68, @result);
-- 展示结果
select @result;

案例2：成绩转换

-- 将传入的 200 分制的分数，进行换算，换算成百分制，然后返回
create procedure p2(inout score double)
begin
	set score := score * 0.5;
end;

-- 由于是 inout 类型的参数，所以在调用该存储过程前需要准备一个自定义变量并赋值
set @score := 78;
-- 调用该存储过程
call p2(@score);
-- 展示转换结果
select @score;

5、case 流程控制

语法：

-- 语法一
case case_value
	when 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))
begin
	case 
		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 条件 do
	SQL 逻辑...
end while;

案例：

-- 计算从 1 累加到 n 的值，n 为传入的参数值。
create procedure p4(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;

6.2 repeat 循环

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

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

案例：

-- 计算从 1 累加到 n 的值，n 为传入的参数值。
create procedure p5(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;

6.3 loop 循环

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

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

语法：

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

-- 退出指定标记的循环体
leave label;

-- 直接进入下一次循环
iterate label;

案例1：

-- 计算从 1 累加到 n 的值，n 为传入的参数值
create procedure p6(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;

案例2：

-- 计算从 1 到 n 之间的偶数累加的值，n 为传入的参数值
create procedure p7(in n int)
begin
	declare total int default 0;
	sum:loop
		if n = 0 then
			leave sum;
		else if mod(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;

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)
begin
	declare 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 do
		fetch 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 do
		fetch u_cursor into uname, ujob;
		insert into user_job values(null, uname, ujob);
	end while; 
	
	close u_cursor;
end;

-- 调用存储过程 p8()
call p8(40);

8、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是 in 类型。
语法：

create function 存储函数名称([参数列表])
returns type [characteristic...]
begin 
	-- SQL 语句
	return ...;
end;

-- type 说明
-- 指定返回参数类型：int、varchar等

-- characteristic 说明
-- deterministic：相同的输入参数总是产生相同的结果
-- no sql：不包含 SQL 语句
-- reads sql data：包含读取数据语句，但不包含写入数据的语句

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

create function fun1(in n int)
returns int deterministic
begin
	declare total int default 0;
	while n > 0 do
		set total := total + n;
		set n := n - 1;
	end while;
	return total;
end;

-- 调用存储函数
select fun1(100);

存储函数一般不常用，因为存储过程可以替代存储函数。