关于对【mysql存储过程】的理解与简述

【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://blog.csdn.net/m0_69908381/article/details/130857854
出自【进步*于辰的博客】

【存储过程】这个知识点，我在大二下期学习【mysql】时就接触过。第一次接触，觉得新奇，就上网查资料。只是，那时基础一般，就有些晦涩难懂；然后，那时的课程实训项目都比较简单，业务比较浅显、单一，数据表也比较少，也就未必需要存储过程。因此，可以说那时我对存储过程是一知半解。
第一次实习，尽管项目不是很大，但由于客户量大，对性能要求较高，因此很多业务都使用了存储过程，我才重新学习，并在工作中总结了一些经验。
存储过程的难度一般，但毕竟细节很多，而在实际工作中又未必都能涉及这些细节，工作时间一长，就可能忘记，于是我特来写这篇文章，既是为自己做个笔记，也是跟大家分享【存储过程】的学习和使用方法，望对大家有帮助！！
参考笔记三，P34.1、P35、P37.1。

注：为了方便大家理解以及便于阐述，我会直接在示例中注释（//），这是java的注释格式，在SQL中不适用。因此，如果大家需要复制代码进行测试，运行前先将注释全部删除。

1、概述

存储过程是一种存储于数据库、封装了sql语句和流程控制语句、进而通过类如调用方法的形式来调用（如：传参、获取返回值）、从而实现业务功能（即将一定程序业务迁移到数据库内，将业务交由数据库管理）的数据结构。

1、优点

1. 存储过程对复杂sql语句进行了封装，而调用简便，故简化了一些复杂的操作；
2. 若数据表变动（如：表名修改、字段名修改）或业务变动，不需要变动代码，故简化了对变动的管理；
3. 提高了程序性能。因为存储过程存于数据库，减少了sql传输的流量。并且，数据库会对存储过程进行编译（调用时），其中，mysql存储过程是按需编译。大多数数据库（如：oracle、mysql），编译后的存储过程都存于数据库缓存，即若存储过程在单个连接中被多次调用，调用的就是缓存内的存储过程；否则调用的是数据库内的（未编译），此时存储过程的执行效率相当于查询；
4. 存储过程提供了一个接口供开发人员调用，这使得开发人员不必考虑其内部功能。同时，只需向访问存储过程的应用程序授权，而不必向其提供基础数据表权限，故提高了安全性，且可重用和透明。

2、缺点

1. 存储过程会占用当前连接内存（因为存储过程会经过编译存储于缓存中，而缓存是内存的一部分）。其中，由于mysql设计的初衷是高效的查询，非逻辑运算，故若存储过程中使用了大量的逻辑操作则会占用大量的CPU；
2. 存储过程的构造使得开发复杂的存储过程变得困难；
3. 存储过程难以调试（仅有很少的工具可以调试存储过程），且开发和维护都不容易；
4. 对数据库的依赖性高，难以移植（存储过程的内部就是sql语句，自然对数据库依赖性高）。

3、补充说明

大都是情况下，存储过程内都会包含流程控制语句。为何？因为使用存储过程的原因无非两种：

1. 封装一条复杂的sql语句；
2. 封装一个包含多个原子操作（sql语句），而这些原子操作间会进行一些逻辑运算或数据处理的事务。

哪些是流程控制语句？
比较常用的如：条件语句、循环语句。
我曾为mysql流程控制语句单独写过一篇文章，因此本文中不再赘述，大家可以参考博文【关于mysql流程控制语句的简述】，下文示例中就有用到。

2、关于存储过程的使用

2.1 创建、修改、删除

那篇博文中阐述了如何使用navicat创建、修改、删除存储过程的方法。因此，在此不再赘述。

2.2 细节说明

员工表：emp

字段名	类型	说明
emp_no	smallint	员工号
emp_name	varchar(20)	员工名
emp_salary	decimal(5,2)	员工工资

先看示例：

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_EIByENo_Sel`(IN `empNo` int,OUT `empName` varchar(20))
BEGIN// 定义变量 doubleSal，表示“双倍工资”，默认值为0，定义默认值也可以是 default(0)declare doubleSal int default 0;// 查询员工号为empNo的员工的工资，并将值赋予变量 doubleSalselect emp_salary into doubleSal from emp where emp_no = empNo;set doubleSal = doubleSal * 2;// 赋值，必须使用 set。注意：此处不兼容：*=/+=// 查询工资是此员工工资双倍的员工名select emp_name into empName from emp where emp_salary = doubleSal;select empName;// 这是固定格式，相当于”result 变量“END

示例说明 + 注意事项：

1. 存储过程格式：create procedure 存储过程名(参数列表) begin...end；（示例definer那些是指明“用户、连接、数据库等”）
2. 存储过程命名规范：P_[前/后台标识]_[模块/功能简称]By[条件名简称]_Sel（Sel表示查询，Del表示删除...）；
3. 存储过程体必须用begin...end包括；
4. 在参数empNo、empName前的in/out是参数模式。in表示输入参数，限制参数只能用于传入，即形参；out表示输出参数，限制只能用于传出，即返回值。参数模式用于声明此参数是否可用于传入/传出。
   第3种参数模式：inout，表示此参数既可传入，也可传出（关于如何使用，后续补充）。
   参数默认模式是in；
5. out传出参数等同于变量。示例中empName的声明等同于declare empName varchar(20)。不同的是，前者没有“赋值类型限制”，如：

前者：set empName = 2023;// 不报错
后者：set empName = 2023;// 报错

6. 任何参数，若未初始化（设置默认值），则当将此参数作为返回值时（即：select 参数），无结果；
7. 所有已用参数，需初始化。否则，当返回值是select 参数时，此存储过程无结果；（注：这1点不是虚言，因为select后可以是一个常量，即以常量作为返回值）
8. 参数名最好不要与字段名相同；
9. 存储过程名不能包含“-”（连字符）；
10. 语句体（sql语句）不能嵌套流程控制语句，如：if、loop；
11. 所有的定义（declare）必须置于开头，且变量或条件的定义要在游标（cursor，下文说明）的定义之前；

2.3 查询

所有存储过程存于数据表information_schema.routines中。

2.4 调用

call P_admin_EIByENo_Sel(1001, @);

这是固定格式，无论是在navicat命令行、cmd，还是在框架（如：mybatis）中。
1001对应传入参数empNo；@对应传出参数empName，也可以是@empName、@xx，就目前我所知，@后的标识任意（存储过程的返回值由select 变量决定，与@后的标识无关，但传出参数empName的位置必须至少有一个@（相当于占位符）。
注意一点： 存储过程的实参与java方法实参有一定类似，即赋值类型限制。如示例，可以是1001，而不能是'1001'（字符型）。

3、关于cursor（游标）

3.1 概述

什么是游标？
游标是一种能够对结果集中的每一行记录进行定位、并对所指向记录的数据进行操作的数据结构。
如：java迭代器（iterator）中的也是游标，也称之为光标，其初始指向第一个元素的前面。
游标的用途是什么？
迭代器是何用途？遍历。因此，存储过程中的游标是用于控制遍历的（直白而言，游标用于在循环语句中获取记录）。

3.2 示例

功能：根据用户ID，删除评论和评论回复记录。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_RRTUByUid_Del`(in userId int)
BEGINdeclare rComId int default 0;// 评论IDdeclare rRepN int default 0;// 评论回复数// 定义游标declare rComId_cursor cursor for select comment_id from gd_resource_comment where user_id = userId;// 根据用户ID查询所有评论IDdeclare rComId_next int default 0;declare continue handler for not found set rComId_next = -1;// -- -Aopen rComId_cursor;// 打开游标getRComId:loop// 从结果集中获取一行记录。结合上下文，此结果集是当前用户旗下的所有评论ID，// 因此每次获取（fetch）的是其中一个评论IDfetch rComId_cursor into rComId;// 查询当前评论ID（rComId）所对应的评论回复数select count(1) into rRepN from gd_resource_response where comment_id = rComId;if rRepN > 0 thendelete from gd_resource_response where comment_id = rComId;// 删除评论回复end if;delete from gd_resource_comment where comment_id = rComId;// 删除评论if rComId_next = -1 then// --------------------------------------Bleave getRComId;// 跳出循环，类似 breakend if;end loop getRComId;close rComId_cursor;// 关闭游标END

示例说明 + 注意事项：

1. 游标使用（fetch）前需要先打开（open 游标名），游标打开时如iterator的游标一般，初始指向第一行的前面；使用完后（循环结束）最好关闭游标（close 游标名）。其中，游标可多次打开（可用于多个循环）；
2. 游标定义格式：declare 游标名 cursor for select_statement；（其中的select_statement是查询型sql语句）
3. 获取游标值（一行记录）：fetch 游标名 into 变量；
4. 示例中A的作用：
   大家肯定用过java迭代器，当调用next()时，在底层会先判断是否存在下一个元素，若存在，则返回此元素；否则返回null，不会出现异常。而在mysql的游标中，当fetch时，同样会先判断是否存在下一行记录，若存在则返回此记录；否则报错。那如何避免报错？ 这就是A的作用。
   实现思路： 先判断是否存在下一行记录，若不存在则跳出循环，避免下一次fetch。
   具体实现： 定义A，格式：declare continue handler for not found set 变量 = 值。什么意思呢？就是当fetch时，A也会执行，若满足not found（即不存在下一行记录）时，执行变量 = 值。那么，就可以使用此变量来控制循环（示例中B，结束循环）。

4、最后

本文中的例子是为了方便大家理解、便于阐述mysql存储过程而简单举出或是我曾用过的，不一定有实用性。
其实mysql存储过程的细节很多，只是我没有那么细致地进行阐述。我阐述的原则是“以吾之理解，着重之阐述”。因此，这篇文章可能并不适合初学者。
给大家推荐2篇博文，也是我较为系统学习mysql存储过程时参考的文章。

1. MySQL中的存储过程（详细篇）；（转发）
2. Mysql存储过程大全。（转发）

如果大家想要快速掌握这个知识点，我的建议是“多测试，学以致用”。

本文完结。