存储过程

定义

视图是 SQL 中的一个重要应用，使用视图对 SQL 查询进行封装，可以让 SQL 的代码结构更加清晰，让用户权限管理更安全。

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

存储过程（Stored Procedure）是 SQL 语句的封装，一旦存储过程被创建出来，使用它就像使用函数一样简单，直接通过调用存储过程名即可。

创建存储过程

存储过程由 SQL 语句和流程控制语句共同组成，定义一个存储过程的语句如下：

CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
需要执行的 SQL 语句;
END

在这里，使用 CREATE PROCEDURE 创建存储过程，后面是存储过程的名称，以及过程携带的参数，可以包含输入参数和输出参数。最后由 BEGIN 和 END 来定义所要执行的语句块。

和视图一样，可以删除已经创建的存储过程，使用的是 DROP PROCEDURE 。如果要更新存储过程，需要使用 ALTER PROCEDURE ，下面是一个具体的例子：

存储过程示例
-- 做一个累加运算，计算 1+2+...+n = ? ,通过参数 n 来表示想要累加的个数。
CREATE PROCEDURE add_sum(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

call add_sum(3);

当再次需要使用这个存储过程的时候，直接使用 CALL add_sum(50); 就可以了。

call add_sum(50);
+--------+
| sum    |
+--------+
| 1275   |
+--------+
1 row in set (0.00 sec)

上面是一个简单的存储过程，输入参数为 n ，输出参数为 sum 。需要除了实现过程，需要理解两点，一个是 DELIMITER 定义语句的结束符，另一个是存储过程的三种参数类型。

如果使用 Navicat 这种工具来管理 MySQL 执行存储过程，那么直接使用上面代码就可以了。如果是 MySQL ，还需要用 DELIMITER 来临时定义新的结束符。

因为默认情况下 SQL 采用 ; 作为结束符，这样当存储过程每一句 SQL 结束之后，采用 ; 作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此就需要临时定义新的 DELIMITER ，新的结束符可以用（//）或者（$$）。如果使用的是 MySQL ,那么上面这段代码，应该写成下面这样：

存储过程示例
DELIMITER //
CREATE PROCEDURE `add_num_two`(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 ;

首先使用 // 作为结束符，又在整个存储过程结束后采用了 // 作为结束符号，告诉 SQL 可以执行了，然后再将默结束符还原成默认的 ;

存储过程的三种参数类型

在刚才的存储过程中，是用来 IN 类型的参数，另外还有 OUT 类型和 INOUT 类型，作用如下：

IN 和 OUT 的组合，及用于存储过程的传入参数，同时又可以把计算结果放到参数中，调用者可以得到返回值。

IN 参数必须在调用存储过程时指定，而在存储过程中修改该参数的值不能被返回。而 OUT 参数和 INOUT 参数可以在存储过程中被改变，并可返回。

比如，要创建一个存储过程 get_hero_scores, 用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值、以及平均最大攻击值，存储过程如下：

存储过程的参数
CREATE PROCEDURE `get_hero_scores` (
  OUT max_max_hp FLOAT,
  OUT min_max_mp FLOAT,
  OUT avg_max_attack FLOAT,
  s VARCHAR(255)
)
BEGIN
  SELECT MAX(hp_max),MIN(max_mp),AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp,min_max_mp,avg_max_attack;
END

这里定义了 4 个参数类型，3 个是 OUT 类型，一个 IN 类型。

这里从 heros 数据表中筛选主要英雄定位为 s 的英雄数据，即筛选条件为 role_main = s, 提取这些数据中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值，分别赋值给变量 max_max_hp, min_max_mp, avg_max_attack 。

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

CALL get_hero_scores(@max_max_hp,@min_max_mp,@avg_max_attack,'Tank');
SELECT @max_max_hp,@min_max_mp,@avg_max_

+----------------+----------------+----------------+
| @max_max_hp    | @min_max_mp    | @avg_max_attack |
+----------------+----------------+----------------+
| 8050.000000000 | 0.0000000000 | 100.0000000000 |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

流程控制语句

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

BEGIN...END: 中间可以定义多个语句，每个语句都以 ; 号为结束符。
DECLARE: DECLARE 用来声明变量，使用的位置在于 BEGIN...END 语句中，而且需要在其它语句使用之前进行变量声明。
SET 赋值语句，用于对变量进行赋值。
SELECT...INTO : 把从结果表中查询到的结果存放到变量中，也就是为变量赋值。

除了上面这些，还有一些常用的流程控制语句：

IF...THEN...ENDIF: 条件判断语句，还可以在 IF...THEN...ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
CASE: CASE 语句用与多条件的分支判断，使用的语法是下面这样的。

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

LOOK、LEAVE、ITERATE: LOOP 循环语句，LEAVE 跳出循环，ITERATE 循环一次。可以把 LEAVE 理解为 BREAK，ITERATE 理解为 CONTINUE。
REPEAT...UNTIL...END REPEAT: 这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT ；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
WHILE...DO...END WHILE: 这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果条件满足就进行循环，如果条件不满足就退出循环。

SQL 是声明型语言，存储过程中使用到的流程语言，属于过程性语言，类似 C++ 中的函数。

存储过程使用的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直存在着很多争议，比如很多公司明确禁止使用存储过程，有些公司对于大型项目要求使用存储过程。

这些问题需要从存储过程的特点来找答案。

存储过程有很多好处。

首先存储过程可以一次编译多次使用。存储过程只是在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解为不同的模块，然后模块之间可以重复使用，在减少开发量的同时，还能保证代码的结构清晰。

还有一点是，存储过程的安全性强，设定存储过程的时候可以设置用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在编程类一次存储过程，只需要连接一次即可。

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM等公司，但是国内的阿里并不推荐开发人员使用存储过程。

存储过程虽然有上述优点，但是它的缺点也很明显。

它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写，其次调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂存储过程来说，开发和维护都不容易。

此外，存储过程的版本管理也很困难，比如数据库表的索引发生变化了，可能会导致存储过程时小。在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本管理，版本迭代更新的时候很麻烦。最后，它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对于扩展性的要求很高，在这种场景，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

定义​

创建存储过程​

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

流程控制语句​

存储过程使用的争议​

定义

创建存储过程

存储过程的三种参数类型

流程控制语句

存储过程使用的争议