SQL基础理论篇（九）：存储过程

简介

存储过程Stored Procedure，SQL中的另一个重要应用。

前面说的视图，只能勉强跟编程中的函数相似，存储过程更进一步逼近了函数这一概念。 视图仍然局限在SQL语句这个范畴，但是存储过程已经可以进一步使用控制语句了。或许，存储过程可以视为是视图的更进一步

存储过程是由SQL语句和流控制语句组成的语句集合，和函数一样，它可以接收输入参数，也可以把结果返回。一旦存储过程被创建出来，使用它就像使用函数一样简单，直接调用存储过程名就可以。

先简单看了一下存储过程究竟是什么，该怎么用，给我的感觉是：存储过程就是遵循模块化编程的指导思想下的一个比较简陋的产品。

按教程的内容，本节将主要介绍以下部分：

• 存储过程的形式；
• 存储过程中各组成部分的介绍；
• 存储过程的优缺点。

存储过程的形式

定义一个存储过程

create procedure 存储过程名称([参数列表])
begin
	需要执行的语句
end
1
2
3
4

和视图一样，删除存储过程是drop procedure，更新存储过程是alter procedure。

举一个例子，写一个简单的存储过程，计算1+2+3+…+n等于多少。

delimiter //
create procedure `add_num`(IN n INT)
begin
	declare i int;
	declare sum int;
	
	set i=1;
	set sum=0;
	while i <= n do
		set sum = sum + i;
		set i = i+1;
	end while;
	select sum;
end //
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用delimiter将’//‘作为整个存储过程的结束符号，并在最后将结束符重新定义回默认的’;’

然后我们需要计算1到50的累加之和时，只需要调用call add_num(50);即可。

使用delimiter定义语句结束符

以MySQL举例，如果使用Navicat这种图形化工具来编写存储过程的话，是不需要手动定义delimiter的，navicat会自己加。

但是如果你使用的工具没有提供这个功能，或者是你直接在后台手敲的，那么你需要显式用delimiter来定义结束符。

那么，为什么要定义语句结束符呢？

这是因为默认情况下MySQL使用分号，即;来作为结束符。

这样的话，在存储过程中的每一行SQL语句之后加分号，就相当于告诉SQL解释器，这一行已经结束了，可以执行这一句了。

但是有时候我们不希望SQL这样做，存储过程是一个整体，我们更希望存储过程整段一起执行，所以我们需要临时定义新的delimiter，比如说’//'或者’$$‘。

存储过程中的三种参数类型

分别是IN、OUT、和INOUT型。

IN在存储过程中不能返回，即存储过程之外无法调用到in类型的参数，但是out和inout是可以调到的。

create procedure `func`(
	out max_hp float,
	out min_hp float,
    s varchar(255)
)
begin
	select max(hp), min(hp)
	from heros
	where role_category=s
	into max_hp, min_hp;
end
1
2
3
4
5
6
7
8
9
10
11

可以看到定义了两个out类型的参数用来接收返回值，定义了一个参数s用来接收输入，缺省情况下是IN参数。

那怎么读取到存储过程返回的结果呢？

call func(@max_hp, @min_hp, '战士');
select @max_hp, @min_hp;
1
2

就可以把结果打印出来了。

流控制语句

常用的流控制语句有：

1. begin…end：表示存储过程的范围，有点像编程里的花括号；

2. declare：声明变量用，变量在使用前必须提前声明，声明方式declare var_name var_type;

3. set: 赋值语句，用于变量赋值，如set var_name = value;

4. select…into：把查询结果存到out类变量中，就是利用select来为变量赋值；

5. if…then…elseif…then…else…endif;

6. case:

   case
   	when 表达式1 then...
   	when 表达式2 then...
   	else...
   end
   1
   2
   3
   4
   5

7. LOOP、leave、iterate：LOOP是循环语句，类似for循环。leave中止本层循环，类似break。iterate中止本次循环，类似continue；

8. repeat…until…end repeat：有点类似编程里的do while语句。repeat是先执行一次循环，然后until做表达式判断，如果满足条件就退出(这里跟while是不同的)，即走end repeat；若条件不满足，则继续执行循环，直到满足条件；

9. while…do…end while：这个跟while没有区别，满足条件就循环，不满足就退出；

存储过程的优缺点

当前对存储过程的使用一直都有争议。有些公司对大型项目要求使用存储过程，但有些公司却明令禁止使用（如阿里）。

优点：

1. 一次编译多次使用。存储过程只在创建时执行编译，之后都不需要再进行编译；
2. 减少开发工作量。将代码封装成模块，实际上是模块化编程思想。这样，多个模块之间可以重复使用，而且也方便复杂查询的拆解。
3. 安全。可以设定哪些用户可以使用存储过程。
4. 减少网络传输量。连接一次数据库，执行整个存储过程即可，不需要多次连接数据库，一行一行执行代码。

缺点：

1. 可移植性差。无法跨数据库移植。
2. 调试困难。多数DBMS不支持存储过程的调试，所以对于复杂的存储过程，其开发和维护都相当困难。
3. 版本管理困难。存储过程本身没有版本控制，在迭代更新时会比较麻烦。另外，存储过程很容易失效，比如说数据表索引发生变化时，可能会导致失效；
4. 不适合高并发。对于分库分表的并发查询，很难维护。

参考文献

1. 13丨什么是存储过程，在实际项目中用得多么？