【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
https://blog.csdn.net/m0_69908381/article/details/130857854
出自【进步*于辰的博客】
【存储过程】这个知识点,我在大二下期学习【mysql】时就接触过。第一次接触,觉得新奇,就上网查资料。只是,那时基础一般,就有些晦涩难懂;然后,那时的课程实训项目都比较简单,业务比较浅显、单一,数据表也比较少,也就未必需要存储过程。因此,可以说那时我对存储过程是一知半解。
第一次实习,尽管项目不是很大,但由于客户量大,对性能要求较高,因此很多业务都使用了存储过程,我才重新学习,并在工作中总结了一些经验。
存储过程的难度一般,但毕竟细节很多,而在实际工作中又未必都能涉及这些细节,工作时间一长,就可能忘记,于是我特来写这篇文章,既是为自己做个笔记,也是跟大家分享【存储过程】的学习和使用方法,望对大家有帮助!!
参考笔记三,P34.1、P35、P37.1。
注:为了方便大家理解以及便于阐述,我会直接在示例中注释(
//
),这是java的注释格式,在SQL中不适用。因此,如果大家需要复制代码进行测试,运行前先将注释全部删除。
文章目录
- 1、概述
- 1、优点
- 2、缺点
- 3、补充说明
- 2、关于存储过程的使用
- 2.1 创建、修改、删除
- 2.2 细节说明
- 2.3 查询
- 2.4 调用
- 3、关于`cursor`(游标)
- 3.1 概述
- 3.2 示例
- 4、最后
1、概述
存储过程是一种存储于数据库、封装了sql语句和流程控制语句、进而通过类如调用方法的形式来调用(如:传参、获取返回值)、从而实现业务功能(即将一定程序业务迁移到数据库内,将业务交由数据库管理)的数据结构。
1、优点
- 存储过程对复杂sql语句进行了封装,而调用简便,故简化了一些复杂的操作;
- 若数据表变动(如:表名修改、字段名修改)或业务变动,不需要变动代码,故简化了对变动的管理;
- 提高了程序性能。因为存储过程存于数据库,减少了sql传输的流量。并且,数据库会对存储过程进行编译(调用时),其中,mysql存储过程是按需编译。大多数数据库(如:oracle、mysql),编译后的存储过程都存于数据库缓存,即若存储过程在单个连接中被多次调用,调用的就是缓存内的存储过程;否则调用的是数据库内的(未编译),此时存储过程的执行效率相当于查询;
- 存储过程提供了一个接口供开发人员调用,这使得开发人员不必考虑其内部功能。同时,只需向访问存储过程的应用程序授权,而不必向其提供基础数据表权限,故提高了安全性,且可重用和透明。
2、缺点
- 存储过程会占用当前连接内存(因为存储过程会经过编译存储于缓存中,而缓存是内存的一部分)。其中,由于mysql设计的初衷是高效的查询,非逻辑运算,故若存储过程中使用了大量的逻辑操作则会占用大量的CPU;
- 存储过程的构造使得开发复杂的存储过程变得困难;
- 存储过程难以调试(仅有很少的工具可以调试存储过程),且开发和维护都不容易;
- 对数据库的依赖性高,难以移植(存储过程的内部就是sql语句,自然对数据库依赖性高)。
3、补充说明
大都是情况下,存储过程内都会包含流程控制语句。为何?因为使用存储过程的原因无非两种:
- 封装一条复杂的sql语句;
- 封装一个包含多个原子操作(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
示例说明 + 注意事项:
- 存储过程格式:
create procedure 存储过程名(参数列表) begin...end
;(示例definer
那些是指明“用户、连接、数据库等”) - 存储过程命名规范:
P_[前/后台标识]_[模块/功能简称]By[条件名简称]_Sel(Sel表示查询,Del表示删除...)
; - 存储过程体必须用
begin...end
包括; - 在参数
empNo、empName
前的in/out
是参数模式。in
表示输入参数,限制参数只能用于传入,即形参;out
表示输出参数,限制只能用于传出,即返回值。参数模式用于声明此参数是否可用于传入/传出。
第3种参数模式:inout
,表示此参数既可传入,也可传出(关于如何使用,后续补充)。
参数默认模式是in
; out
传出参数等同于变量。示例中empName
的声明等同于declare empName varchar(20)
。不同的是,前者没有“赋值类型限制”,如:
前者:set empName = 2023;// 不报错
后者:set empName = 2023;// 报错
- 任何参数,若未初始化(设置默认值),则当将此参数作为返回值时(即:
select 参数
),无结果; - 所有已用参数,需初始化。否则,当返回值是
select 参数
时,此存储过程无结果;(注:这1点不是虚言,因为select
后可以是一个常量,即以常量作为返回值) - 参数名最好不要与字段名相同;
- 存储过程名不能包含“-”(连字符);
- 语句体(sql语句)不能嵌套流程控制语句,如:if、loop;
- 所有的定义(
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
示例说明 + 注意事项:
- 游标使用(
fetch
)前需要先打开(open 游标名
),游标打开时如iterator
的游标一般,初始指向第一行的前面;使用完后(循环结束)最好关闭游标(close 游标名
)。其中,游标可多次打开(可用于多个循环); - 游标定义格式:
declare 游标名 cursor for select_statement
;(其中的select_statement
是查询型sql语句) - 获取游标值(一行记录):
fetch 游标名 into 变量
; - 示例中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存储过程时参考的文章。
- MySQL中的存储过程(详细篇);(转发)
- Mysql存储过程大全。(转发)
如果大家想要快速掌握这个知识点,我的建议是“多测试,学以致用”。
本文完结。