《MySQL 入门教程》第 31 篇 存储过程(一)

文章目录
31.1 存储过程简介31.2 创建存储过程31.3 查看存储过程31.4 修改存储过程31.5 删除存储过程31.6 管理存储函数

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

31.1 存储过程简介

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

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

《MySQL 入门教程》第 31 篇 存储过程(一)

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

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

不过,存储过程也存在一些缺点:

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;

《MySQL 入门教程》第 31 篇 存储过程(一)


不剪发的Tony老师

《MySQL 入门教程》第 31 篇 存储过程(一)
CSDN认证博客专家


数据库架构师

毕业于北京航空航天大学,十多年数据库管理与开发经验,拥有OCP以及RHCE证书。目前在一家全球性的游戏公司从事数据库架构设计和开发,CSDN学院签约讲师以及GitChat专栏作者。

原创:https://www.panoramacn.com
源码网提供WordPress源码,帝国CMS源码discuz源码,微信小程序,小说源码,杰奇源码,thinkphp源码,ecshop模板源码,微擎模板源码,dede源码,织梦源码等。

专业搭建小说网站,小说程序,杰奇系列,微信小说系列,app系列小说

《MySQL 入门教程》第 31 篇 存储过程(一)

免责声明,若由于商用引起版权纠纷,一切责任均由使用者承担。

您必须遵守我们的协议,如果您下载了该资源行为将被视为对《免责声明》全部内容的认可-> 联系客服 投诉资源
www.panoramacn.com资源全部来自互联网收集,仅供用于学习和交流,请勿用于商业用途。如有侵权、不妥之处,请联系站长并出示版权证明以便删除。 敬请谅解! 侵权删帖/违法举报/投稿等事物联系邮箱:2640602276@qq.com
未经允许不得转载:书荒源码源码网每日更新网站源码模板! » 《MySQL 入门教程》第 31 篇 存储过程(一)
关注我们小说电影免费看
关注我们,获取更多的全网素材资源,有趣有料!
120000+人已关注
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

您的打赏就是我分享的动力!

支付宝扫一扫打赏

微信扫一扫打赏