概述

SQL 存储过程和视图类似，都是 SQL 中的一个重要概念。
SQL 存储过程的目的也是对 SQL 代码进行封装从而可以反复利用。它和视图有同样的优点：清晰、安全、减少网络传输量。
但是它和视图也有明显的差异，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向表的操作方式，能够实现一些更复杂的数据处理。
存储过程可以说是由 SQL 语句和流控制语句构成的语句集合，它和我们之前学到的函数一样，可以接收输入参数，也可以返回输出参数给调用者，返回计算结果。

存储过程介绍

存储过程的英文是 Stored Procedure，它就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。
我们先来看一下如何创建一个简单的存储过程：

  1. CREATE PROCEDURE 存储过程名称([参数列表])
  2. BEGIN
  3.     需要执行的语句
  4. END
  • 首先，我们使用 CREATE PROCEDURE 创建一个存储过程；
  • 接下来是存储过程的名称和对应的参数列表，可以包括输入参数和输出参数；
  • 最后由 BEGIN 和 END 来定义我们所要执行的语句块。

和视图一样，我们也可以用 ALTER PROCEDURE 和 DROP PROCEDURE 对存储过程进行修改和删除。

下面，我们来以一个简单的存储过程为例进行演示。比如我想做一个累加运算，计算 1+2+…+n 等于多少，我们可以通过参数 n 来表示想要累加的个数，那么如何用存储过程实现呢？

  1. DELIMITER //
  2. CREATE PROCEDURE `add_num`(IN n INT)
  3. BEGIN
  4.        DECLARE i INT;
  5.        DECLARE sum INT;
  7.        SET i = 1;
  8.        SET sum = 0;
  9.        WHILE i <= n DO
  10.               SET sum = sum + i;
  11.               SET i = i +1;
  12.        END WHILE;
  13.        SELECT sum;
  14. END //
  15. DELIMITER ;

其中，我们使用 DELIMITER 定义了存储过程结束的结束符为 // ，并且在 END 之后使用 // 标识存储过程结束。
创建完成存储过程后，我们就可以直接调用该存储过程进行计算了：

  1. CALL add_num(50);

image.png

存储过程中的参数

在刚才的存储过程中，我们使用了 IN 类型的参数，另外还有 OUT 类型和 INOUT 类型，它们的作用如下：
image.png
我们以我们之前讲过的王者荣耀的英雄数据表 heros。假设我想创建一个存储类型 get_hero_scores，用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值，那么该怎么写呢？

  1. CREATE PROCEDURE `get_hero_scores`(
  2.        OUT max_max_hp FLOAT,
  3.        OUT min_max_mp FLOAT,
  4.        OUT avg_max_attack FLOAT,  
  5.        s VARCHAR(255)
  6.        )
  7. BEGIN
  8.        SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
  9. END

然后我们就可以调用存储过程，使用下面这段代码即可：

  1. CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
  2. SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

得到的结果如下：
image.png

流控制语句

流控制语句是用来做流程控制的，在上面的两个例子中，我们使用了下面的流控制语句：

  • BEGIN…END：中间包含了多个语句，每个语句都以（;）号为结束符。
  • DECLARE：用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
  • SET：赋值语句，用于对变量进行赋值。
  • SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
  • WHILE…DO…END WHILE：循环语句，先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

除了上述用到的流控制语句之外，常见的流控制语句还包括：

  • IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

  • CASE：CASE 语句用于多条件的分支判断。

    1. CASE 
    2. WHEN expression1 THEN ...
    3. WHEN expression2 THEN ...
    4. ...
    5.   ELSE 
    6.   --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
    7. END

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

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

    存储过程使用注意事项

    虽然存储过程有一定的价值，但是关于存储过程的使用其实确有着很大的争议。很多大公司的开发手册中会明确禁用存储过程。
    我们先来分析一下存储过程有哪些优缺点吧。
    优点如下：

  • 存储过程可以一次编译多次使用，提供 SQL 的执行效率；

  • 它可以减少开发工作量。将代码封装成模块，在减少开发工作量的同时，还能保证代码的结构清晰。
  • 存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
  • 可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。

当然，存储过程也包含一些缺点，需要的缺点如下：

  • 可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
  • 调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
  • 版本管理困难，比如数据表索引发生变化了，可能会导致存储过程失效。
  • 不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

具体到你们的使用场景，可以结合公司要求和实际需求来进行判断是否需要使用存储过程，不过了解一些总是没有坏处的。