MySQL分析与整理 — 存储过程

文章中所有操作均是在 MySQL 5.7 版本下进行的

存储过程是具有特定功能的多个 SQL 语句集组成的，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且可以随时对存储过程进行修改，对应用程序源代码毫无影响。存储过程是预编译的，它可以包含大量的 SQL 代码，首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。数据库管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，可以保证数据安全。

1 使用存储过程

1.1 创建存储过程

-- 创建语法
delimiter $
create procedure procedure_name(parameters)
begin
	procedure_body
end
$
/*
procedure_name：存储过程名
parameters：参数列表，参数可以没有，也可以多个，详细如下：
参数形式为三部分组成：参数传输类型，参数名，参数数据类型
参数传输类型包括：
	输入参数in，传统的传值参数，默认值，可省略不写
	输出参数out，该值可在存储过程内部被改变，并可返回，如果希望存储过程有返回值可以用输出参数解决
	输入输出参数inout，调用时指定，并且可被改变和返回
begin ... end：存储过程代码块开始结束
	begin ... end可嵌套（每个嵌套块及其中的每条语句，必须以分号结束，最外层begin-end存储过程块不需要分号结束）：
		 begin
		 	begin
		 		begin
		 			...
		 		end;
		 	end;
		 end
	begin ... end可设置标签（leave和iterate语句可能用到）：
        label1: begin
			label2: begin
				label3: begin
                      	...
				end label3;
			end label2;
		end label1
procedure_body：多个sql语句组成，包括语句控制、声明变量等
delimiter $，自定义语句结束符为$
不用了，记得改回来，用“delimiter ;”命令恢复为英文分号
*/

1.2 查看存储过程

-- 查看存储过程
select name from mysql.proc where db = '需要查看哪个数据库' and type = 'PROCEDURE';
/*
show procedure status;是查看所有的存储过程
*/
-- 查看存储过程的创建代码
show create procedure 存储过程名;

1.3 调用存储过程

-- 存储过程名后必须有括号，即使存储过程没有参数传递也得有括号
call 存储过程名(参数);

1.4 删除存储过程

drop procedure 存储过程名;

2 存储过程应用示例

2.1 增加示例表和示例数据

-- 示例数据库和示例数据
drop table if exists tbl_account;
create table tbl_account(
	id int primary key,
    aname varchar(10),
    balance double
)ENGINE=InnoDB default charset=utf8;
insert into tbl_account values 
(1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);
-- 再创建一个简单示例表
drop table if exists tbl_account_bak;
create table tbl_account_bak(
	id int primary key,
    info varchar(10)
)ENGINE=InnoDB default charset=utf8;

2.2 无参数的存储过程

/*
获取tbl_account最高的balance
将id和balance存入tbl_account_bak表
*/
drop procedure if exists proc_test1;
delimiter $
create procedure proc_test1()
begin
	-- 定义两个变量接收id和balance
	declare rs_b double;
	declare rs_id int;
	-- select into语句查询到数据赋值给id和balance
	select id, max(balance) into rs_id, rs_b from tbl_account;
	-- 将rs_id和rs_binsert到tbl_account_bak
	insert into tbl_account_bak values (rs_id, rs_b);
end
$
delimiter ;

-- 调用测试
call proc_test1();
-- 查询tbl_account_bak
select * from tbl_account_bak;
+------+-------+
| id   | info  |
+------+-------+
| 1001 | 20000 |
+------+-------+

2.3 带输入参数的存储过程

/*
通过存储过程proc_test2进行insert数据
输入参数可以省略in关键字
*/
drop procedure if exists proc_test2;
delimiter $
create procedure proc_test2(id int, aname varchar(10), in balance double)
begin
	insert into tbl_account values (id, aname, balance);
end
$
delimiter ;

-- 调用测试
call proc_test2(1004, '老沙', 12345);
-- 查询tbl_account
select * from tbl_account ;
+------+--------+---------+
| id   | aname  | balance |
+------+--------+---------+
| 1001 | 老孙   |    1000 |
| 1002 | 老唐   |    2300 |
| 1003 | 老猪   |   20000 |
| 1004 | 老沙   |   12345 |
+------+--------+---------+

2.4 带输入参数和输出参数的存储过程

/*
通过存储过程proc_test3两个参数
add_balance所有人都增加的余额
max_balance返回增加后余额最多的人名字
输出参数会自动返回数据，无需处理
*/
drop procedure if exists proc_test3;
delimiter $
create procedure proc_test3(in add_balance double, out max_balance double)
begin
	-- 所有人增加余额
	update tbl_account set balance = balance + add_balance;
	-- 查询余额最多的人名赋值给输出参数
	select max(balance) into max_balance from tbl_account;
end
$
delimiter ;

-- 调用测试，按照mysql规定，直接用一个@变量接收即可
call proc_test3(1000, @balance);
select @balance;
+----------+
| @balance |
+----------+
|    21000 |
+----------+

结语

以上的示例非常简单，并没有太复杂的流程控制等复杂操作，目的就是为了能让朋友们一下子就能看明白。其实平时的开发中，存储过程是相当复杂的一个代码块，代码块里除了简单的 crud 操作还会有各种流程控制语句，游标，预处理等等复杂的操作（游标和预处理可以在作者其它文章查看到）。

大部分关系型数据库都支持存储过程，不同的数据库环境语法结构有所区别。当前不管是项目还是产品开发，存储过程的应用非常广泛。