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

文章目录

简介
存储过程的形式
- 定义一个存储过程
- 使用delimiter定义语句结束符
- 存储过程中的三种参数类型
- 流控制语句
存储过程的优缺点
参考文献

简介

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

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

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

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

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

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

存储过程的形式

定义一个存储过程

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

和视图一样，删除存储过程是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 ;

使用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

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

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

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

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

流控制语句

常用的流控制语句有：

begin…end：表示存储过程的范围，有点像编程里的花括号；
declare：声明变量用，变量在使用前必须提前声明，声明方式declare var_name var_type;
set: 赋值语句，用于变量赋值，如set var_name = value;
select…into：把查询结果存到out类变量中，就是利用select来为变量赋值；
if…then…elseif…then…else…endif;

case:

case
	when 表达式1 then...
	when 表达式2 then...
	else...
end

LOOP、leave、iterate：LOOP是循环语句，类似for循环。leave中止本层循环，类似break。iterate中止本次循环，类似continue；
repeat…until…end repeat：有点类似编程里的do while语句。repeat是先执行一次循环，然后until做表达式判断，如果满足条件就退出(这里跟while是不同的)，即走end repeat；若条件不满足，则继续执行循环，直到满足条件；
while…do…end while：这个跟while没有区别，满足条件就循环，不满足就退出；