第 31 篇存储过程（一）

imodouAbout 9 minMySQL

《MySQL 入门教程》第 31 篇存储过程（一）

接下来的两篇文章我们将会介绍 MySQL 存储过程和函数。本篇主要介绍它们的概念和管理，下一篇将会讨论如何编写存储过程和函数来实现业务需求。

31.1 存储过程简介

MySQL 存储过程（Stored procedure）是一种存储在数据库中的程序。它可以包含多个 SQL 语句，提供许多过程语言的功能，例如变量定义、条件语句、循环语句、游标以及异常处理等。存储过程没有返回值，但是它可以通过输出参数实现数据的返回，同时还可以产生一个查询结果返回到客户端。

在数据库中创建存储过程之后，应用程序或其他存储过程可以通过名称对其进行重复调用。

在介绍存储过程的管理之前，我们还需要了解一下它的优缺点，存储过程的主要优点包括：

实现代码的重用和集中管理。存储过程存储在数据库中，可以在被重复调用，不同的应用可以共享存储过程；
实现业务的封装和隔离。应用程序通过接口访问存储过程；当业务发生变化时，只需要修改存储过程的逻辑，对应用程序毫无影响；
减少了应用与数据库之间的网络流量。调用存储过程时，只需要传递参数，在一定程度上可以减轻网络负担；
存储过程可以提高安全性。应用程序通过存储过程进行数据访问，而不需要直接访问数据表，保证数据的安全。

不过，存储过程也存在一些缺点：

MySQL 存储过程的语法和其他数据库之间不兼容，无法直接移植；
存储过程需要占用数据库服务器的资源，包括 CPU、内存等。MySQL 对于大量逻辑处理的支持不够完善；
存储过程的开发和维护需要专业的技能。MySQL 存储过程不支持调试功能，增加了应用程序的开发和维护难度。

一般来说，对于业务快速变化的互联网应用，倾向于将业务逻辑放在应用层，便于扩展；对于传统行业，或者复杂的报表分析，合理使用存储过程可以提高效率。

31.2 创建存储过程

MySQL 使用CREATE PROCEDURE语句创建存储过程：

CREATE PROCEDURE sp_name (
  [ IN | OUT | INOUT ] param_name data_type,
  ...
)
  routine_body;

其中，sp_name 是存储过程名称；param_name 是参数名称，IN 表示输入参数（默认模式），OUT 表示输出参数，INOUT 表示输入输出参数；data_type 是参数的数据类型；routine_body 是存储过程的具体实现。

下面是一个创建存储过程的示例：

DELIMITER $$

CREATE PROCEDURE GetDevelopers()
BEGIN
  SELECT emp_name, sex, hire_date, salary, bonus, email
  FROM employee
  WHERE dept_id = 4;    
END$$

DELIMITER ;

其中，DELIMITER不属于存储过程的内容。由于很多 MySQL 客户端将分号（;）作为 SQL 语句的终止符，而存储过程中包含多个语句；为了将存储过程的定义整体发送到服务器，需要将终止符临时修改为其他符号（例如 $$），最后再将其改回分号。BEGIN 和 END 表示程序主体的开始和结束。

存储过程 GetDevelopers 没有参数，程序体中包含了一个查询语句，用于返回开发部门的员工信息。MySQL 使用 CALL 语句调用存储过程，例如：

CALL GetDevelopers();

emp_name|sex|hire_date |salary  |bonus|email              |
--------|---|----------|--------|-----|-------------------|
赵云    |男  |2005-12-19|15000.00| 0.00|zhaoyun@shuguo.com |
廖化    |男  |2009-02-17| 6500.00| 0.00|liaohua@shuguo.com |
关平    |男  |2011-07-24| 6800.00| 0.00|guanping@shuguo.com|
赵氏    |女  |2011-11-10| 6600.00| 0.00|zhaoshi@shuguo.net |
关兴    |男  |2011-07-30| 7000.00| 0.00|guanxing@shuguo.com|
张苞    |男  |2012-05-31| 6500.00| 0.00|zhangbao@shuguo.com|
赵统    |男  |2012-05-03| 6000.00| 0.00|zhaotong@shuguo.com|
周仓    |男  |2010-02-20| 8000.00| 0.00|zhoucang@shuguo.com|
马岱    |男  |2014-09-16| 5800.00| 0.00|madai@shuguo.com   |

📝MySQL 在第一次调用存储过程时查找并编译它的源码，并且将编译结果放入缓存，然后执行该存储过程。如果在同一个会话中再次调用该存储过程，MySQL 直接从缓存中执行，而不需要重新编译。

我们再创建一个存储过程 GetEmpNumBySex，并且为其指定两个参数，按照性别查询员工的数量：

DELIMITER $$

CREATE PROCEDURE GetEmpNumBySex (
  IN  psex VARCHAR(10),
  OUT pnum INT
)
BEGIN
  SELECT COUNT(1)
  INTO pnum
  FROM employee
  WHERE sex = psex;
END$$

DELIMITER ;

其中，psex 是输入参数，类型为 VARCHAR(10)；pnum 是输出参数，类型为 INTEGER；在存储过程内部，通过一个查询返回指定性别的员工数量并赋值给 pnum。调用该存储过程的示例如下：

CALL GetEmpNumBySex('女', @num);
SELECT @num;
@num|
----|
   6|

会话变量 @num 用于接收存储过程返回的参数值。

📝MySQL 存储过程的输入参数使用 IN 模式表示，在存储过程内部使用输入参数的副本进行操作，不会改变外部传入的参数值；输出参数使用 OUT 模式表达，在存储过程内部对其进行赋值并返回给调用程序，存储过程无法使用输出参数的初始值；输入输出参数使用 INOUT 模式组成，它是 IN 和 OUT 参数的组合。

另外，在创建存储过程时还可以指定一些可选的属性：

CREATE PROCEDURE sp_name (
  [ IN | OUT | INOUT ] param_name data_type,
  ...
)
[
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
]
  routine_body;

其中，COMMENT 可以为存储过程添加注释；LANGUAGE表示编写存储过程的语言，目前 SQL 是唯一可用值；DETERMINISTIC 属性表示这是一个确定性的存储函数，对于相同的输入参数一定会返回相同的结果；MySQL 默认创建的是非确定性函数（NOT DETERMINISTIC）。

CONTAINS SQL 表示程序中不包含读取或者写入数据表的语句，这是默认设置。举例来说，SET @x = 1 或者DO RELEASE_LOCK('abc')不会读写任何数据。NO SQL 表示程序不包含任何 SQL 语句。READS SQL DATA 表示程序包含读取操作（例如 SELECT），但不会修改数据表。MODIFIES SQL DATA 表示程序包含写入操作（例如 INSERT 或者DELETE）。这些属性仅供 MySQL 服务器参考使用，不会用于限制程序中实际使用的语句。

SQL SECURITY 属性表示存储过程以定义者权限（DEFINER ）还是调用者权限（INVOKER ）执行，具体参考下一篇文章。

31.3 查看存储过程

使用SHOW PROCEDURE STATUS语句查看存储过程的列表和属性：

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

LIKE 用于匹配存储过程的名称，WHERE 可以指定更多的过滤条件。例如，以下语句返回了存储过程 GetDevelopers 的相关信息：

SHOW PROCEDURE STATUS WHERE name = 'GetDevelopers';

Name                |Value              |
--------------------|-------------------|
Db                  |hrdb               |
Name                |GetDevelopers      |
Type                |PROCEDURE          |
Definer             |root@%             |
Modified            |2020-09-27 05:32:44|
Created             |2020-09-27 05:32:44|
Security_type       |DEFINER            |
Comment             |                   |
character_set_client|utf8mb4            |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation  |utf8mb4_0900_ai_ci |

返回的信息包括数据库、存储过程名称、类型（PROCEDURE）、创建者、创建时间和修改时间、调用权限以及字符集信息。

使用SHOW CREATE PROCEDURE语句查看存储过程的定义。例如：

SHOW CREATE PROCEDURE GetDevelopers;

Name                |Value                                                                                                                                                           |
--------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------|
Procedure           |GetDevelopers                                                                                                                                                   |
sql_mode            |ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION                                           |
Create Procedure    |CREATE DEFINER=`root`@`%` PROCEDURE `GetDevelopers`()
                     BEGIN
                     
                       SELECT emp_name, sex, hire_date, salary, bonus, email
                     
                       FROM employee
                     
                       WHERE dept_id = 4;    
                     
                     END|
character_set_client|utf8mb4                                                                                                                                                         |
collation_connection|utf8mb4_0900_ai_ci                                                                                                                                              |
Database Collation  |utf8mb4_0900_ai_ci                                                                                                                                              |

另外，我们也可以通过系统表 information_schema.routines 查看存储过程和函数的信息。

31.4 修改存储过程

如果想要为存储过程增加或删除某个参数，或者修改存储过程的定义，只能通过下文中的DROP PROCEDURE语句删除该存储过程，然后再次使用CREATE PROCEDURE语句创建存储过程。

MySQL 中的ALTER PROCEDURE语句只能用于修改存储过程的某些属性：

ALTER PROCEDURE proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

这些属性和 CREATE PROCEDURE 语句中的属性一样。

31.5 删除存储过程

MySQL 使用DROP PROCEDURE语句删除存储过程：

DROP PROCEDURE [IF EXISTS] sp_name;

如果删除的存储过程不存在，将会返回一个错误信息；使用 IF EXISTS 可以避免该错误。例如：

DROP PROCEDURE IF EXISTS GetDevelopers;

31.6 管理存储函数

MySQL 存储函数（Stored function）和存储过程类似，也是存储在数据库中的程序，但是它会返回一个计算结果。存储函数可以和内置函数或者表达式一样用于 SQL 语句，可以提高代码的可读性以及可维护性。

📝MySQL 存储过程和存储函数统称为存储例程（Stored routine）。MySQL 还支持一种创建函数的方式，就是通过编写 C++ 程序实现扩展的自定义函数（User-Defined Function），这一功能不在我们的专栏范围之内。

存储函数的管理和存储过程类似，创建存储函数使用CREATE FUNCTION语句：

CREATE FUNCTION sp_name (
  param_name data_type,
  ...
)
RETURNS data_type
  routine_body;

其中，sp_name 是存储函数名称；param_name 是参数名称，所有的参数都是输入参数；data_type 是参数或者返回值的数据类型；RETURNS 定义了返回值的类型；routine_body 是存储过程的具体实现。

例如，以下语句创建了一个函数 add2：

DELIMITER $$

CREATE FUNCTION add2(
  p1 int,
  p2 int
)
RETURNS int
DETERMINISTIC
BEGIN
 RETURN p1 + p2;
END $$

DELIMITER ;

函数 add2 用于计算两个整数的和；DETERMINISTIC 属性表示这是一个确定性函数，对于相同的输入参数一定会返回相同的结果；MySQL 默认创建的是非确定性函数（NOT DETERMINISTIC）。

使用以下语句测试一下 add2 函数：

SELECT add2(2, 3);

add2(2, 3)|
----------|
         5|

查看存储函数的方式和存储过程类似，只需要将 PROCEDURE 替换成 FUNCTION 即可：

SHOW FUNCTION STATUS WHERE name = 'add2';

Name                |Value              |
--------------------|-------------------|
Db                  |hrdb               |
Name                |add2               |
Type                |FUNCTION           |
Definer             |root@%             |
Modified            |2020-09-27 07:01:38|
Created             |2020-09-27 07:01:38|
Security_type       |DEFINER            |
Comment             |                   |
character_set_client|utf8mb4            |
collation_connection|utf8mb4_0900_ai_ci |
Database Collation  |utf8mb4_0900_ai_ci |

其他查看方式可以参考上文中的 31.3 小节。

修改存储函数的定义也只能通过DROP FUNCTION语句先删除，然后再次使用CREATE FUNCTION语句创建存储函数。MySQL 中的ALTER FUNCTION语句只能用于修改存储函数的某些属性：

ALTER FUNCTION proc_name
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

这些属性也可以在 CREATE FUNCTION 语句中指定，具体介绍可以参考上面的存储过程。

MySQL 使用DROP FUNCTION语句删除存储函数：

DROP FUNCTION [IF EXISTS] sp_name;

如果删除的存储函数不存在，将会返回一个错误信息；使用 IF EXISTS 可以避免该错误。例如：

DROP FUNCTION IF EXISTS add2;

第 31 篇 存储过程（一）

《MySQL 入门教程》第 31 篇 存储过程（一）