数据库存储过程总结

1、介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点：

1. 封装，复用
2. 可以接收参数，也可以返回数据
3. 减少网络交互，效率提升

2、创建

create procedure 存储过程名称([参数列表])

begin
		-- SQL语句
end;
1
2
3
4
5

3、调用

call 名称 ([参数]);

4、查看

#查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema = 'xxx';

#查询某个存储过程的定义
show create procedure 存储过程名称;
1
2
3
4
5

5、删除

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

6、变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量：

#查看所有系统变量
show [ session | global ] varibales;

#可以通过like模糊匹配方式查找变量
show [ session | global ] variables like ...;

#查看指定变量的值
select @@[session|global] 系统变量名;
1
2
3
4
5
6
7
8

设置系统变量

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

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

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

赋值：

set @var_name = expr [@var_name = expr]…;

set @var_name := expr [,@var_name := expr]…;

select @var_name := expr [,@var_name := expr]…;

select 字段名 into @var_name from 表名;

使用：

select @var_name;

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

声明：

declare 变量名 变量类型 [default …];

变量类型就是数据库字段类型：int、bigint、char、varchar、date、time等。

赋值：

set 变量名 = 值;

set 变量名 := 值;

select 字段名 into 变量名 from 表名 …;

7、流程控制语句

1、if

语法：

if 条件1 then
	...
elseif 条件2 then  -- 可选
	...
else			   -- 可选 
	...
end if;
1
2
3
4
5
6
7

2、参数

​ 类型 含义

​ in 该类参数作为输入，也就是需要调用时传入值

​ out 该类参数作为输出，也就是该参数可以作为返回值

​ inout 既可以作为输入参数，也可以作为输出参数

in为默认值。

用法：

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

3、case

语法一：

case case_value
		when when_value1 then statement_list1
		[when when_value2 then statement_list2]...
		[else statement_list]
end case;
1
2
3
4
5

语法二：

case
		when search_condition1 then statement_list1
		[when search_condition2 then statement_list2]...
		[else statement_list]
end case;
1
2
3
4
5

4、while

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

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
		SQL逻辑...
end while;
1
2
3
4

5、repeat

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

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
		SQL逻辑...
		until 条件
end repeat;
1
2
3
4
5

6、loop

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

1. LEAVE：配合循环使用，退出循环。
2. ITERATE：必须用在循环中，作用是跳过当前村换剩下的语句，直接进入下一次循环。

[begin_lable:] loop
		SQL逻辑...
end loop [end_lable];
1
2
3

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

7、游标

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

#声明游标
declare 游标名称 cursor for 查询语句;

#打开游标
open 游标名称;

#获取游标记录
fetch 游标名称 into 变量[,变量];

#关闭游标
close 游标名称;
1
2
3
4
5
6
7
8
9
10
11

8、条件处理程序

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

declare handler_action HANDLER FOR condition_value [,condition_value]... statement;

handler_action
		continue:继续执行当前程序
		exit:终止执行当前程序
condition_value
		sqlstate sqstate_value:状态码，如02000
		sqlwarning:所有以01开头的sqlstate代码的简写
		not found:所有以02开头的sqlstate代码的简写
		sqlexception:所有没有被sqlwarning或notfound捕获的sqlstate代码的简写
1
2
3
4
5
6
7
8
9
10

官方文档：https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

9、存储函数

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

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

characteristic说明：
	deterministic：相同的输入参数总是产生相同的结果
	no sql：不包含sql语句
	reads sql data：包含读取数据的语句，但不包含写入数据的语句
1
2
3
4
5
6
7
8
9
10
11