SQL基础理论篇(九):存储过程

简介

存储过程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;

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

流控制语句

常用的流控制语句有:

  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
    
  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丨什么是存储过程,在实际项目中用得多么?
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值