mysql储存过程写法(mysql存储过程语法)-昊阳知识网

接下来的两篇文章我们将会介绍 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 的相关信息：

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

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

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

31.4 修改存储过程

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

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

ALTER PROCEDURE proc_nameCOMMENT ‘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 intDETERMINISTICBEGIN RETURN p1 p2;END $$DELIMITER ;

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

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

SELECT add2(2, 3);add2(2, 3)|———-| 5|

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

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

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

ALTER FUNCTION proc_nameCOMMENT ‘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;