SQL存储过程

存储过程是程序化的 SQL,可以直接操作底层数据表,相比于面向集合的操作方式,能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合,它和我们之前学到的函数一样,可以接收输入参数,也可以返回输出参数给调用者,返回计算结果。

什么是存储过程,如何创建一个存储过程

存储过程即SQL语句的封装,存储过程实际上由 SQL 语句和流控制语句共同组成。

存储过程的定义:

CREATE PROCEDURE 存储过程名称([参数列表])

BEGIN

            控制流语句

END

和视图一样,我们可以删除已经创建的存储过程,使用的是 DROP PROCEDURE。如果要更新存储过程,我们需要使用 ALTER PROCEDURE。

DELIMITER 的作用

DELIMITER 来临时定义新的结束符。因为默认情况下 SQL 采用(;)作为结束符,这样当存

过程中的每一句 SQL 结束之后,采用(;)作为结束符,就相当于告诉 SQL 可以执行这一句了

但是存储过程是一个整体,我们不希望 SQL 逐条执行,而是采用存储过程整段执行的方式,因

我们就需要临时定义新的 DELIMITER,新的结束符可以用(//)或者($$)。

eg:


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 ;

如果用的是 Navicat 工具,那么在编写存储过程的时候,Navicat 会自动设置 DELIMITER 为其他符号,我们不需要再进行 DELIMITER 的操作。

存储过程的3种类型

in 是参数的默认模式。

out模式定义的参数只能在过程体内部赋值。

控制流语句

1. BEGIN…END:BEGIN…END 中间包含了多个语句,每个语句都以(;)号为结束符。

2. DECLARE:DECLARE 用来声明变量,使用的位置在于 BEGIN…END 语句中间,而且需要在其他语句使用之前进行变量的声明。

3. SET:赋值语句,用于对变量进行赋值。

4. SELECT…INTO:把从数据表中查询的结果存放到变量中,也就是为变量赋值。

5. IF…THEN…ENDIF:条件判断语句,还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

6. CASE:CASE 语句用于多条件的分支判断,使用的语法是下面这样的。


CASE 
  WHEN expression1 THEN ...
  WHEN expression2 THEN ...
  ...
    ELSE 
    --ELSE语句可以加,也可以不加。加的话代表的所有条件都不满足时采用的方式。
END

7. LOOP、LEAVE 和 ITERATE:LOOP 是循环语句,使用 LEAVE 可以跳出循环,使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验,你可以把 LEAVE 理解为 BREAK,把 ITERATE 理解为 CONTINUE。

8. REPEAT…UNTIL…END REPEAT:这是一个循环语句,首先会执行一次循环,然后在 UNTIL 中进行表达式的判断,如果满足条件就退出,即 END REPEAT;如果条件不满足,则会就继续执行循环,直到满足退出条件为止。

9. WHILE…DO…END WHILE:这也是循环语句,和 REPEAT 循环不同的是,这个语句需要先进行条件判断,如果满足条件就进行循环,如果不满足条件就退出循环。

存储过程的优缺点

存储过程可以一次编译多次使用。存储过程只在创造时进行编译,之后的使用都不需要重新编译,这就提升了 SQL 的执行效率。其次它可以减少开发工作量。

将代码封装成模块,实际上是编程的核心思想之一,这样可以把复杂的问题拆解成不同的模块,然后模块之间可以重复使用,在减少开发工作量的同时,还能保证代码的结构清晰。

存储过程的安全性强,我们在设定存储过程的时候可以设置对用户的使用权限,这样就和视图一样具有较强的安全性。

可以减少网络传输量,因为代码封装到存储过程中,每次使用只需要调用存储过程即可,这样就减少了网络传输量。同时在进行相对复杂的数据库操作时,原本需要使用一条一条的 SQL 语句,可能要连接多次数据库才能完成的操作,现在变成了一次存储过程,只需要连接一次即可。

它的可移植性差,存储过程不能跨数据库移植,比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程,在换成其他数据库时都需要重新编写。

调试困难,只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说,开发和维护都不容易。

存储过程的版本管理也很困难,比如数据表索引发生变化了,可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理,但是存储过程本身没有版本控制,版本迭代更新的时候很麻烦。

不适合高并发的场景,高并发的场景需要减少数据库的压力,有时数据库会采用分库分表的方式,而且对可扩展性要求很高,在这种情况下,存储过程会变得难以维护,增加数据库的压力,显然就不适用了。

  • 1
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值