【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
blog.csdn.net/m0_69908381…
出自【进步*于辰的博客】
存储过程的细节很多,而在实际工作中又未必都能涉及这些细节,工作时间一长,就可能忘记,于是特来写这篇文章,既是为自己做个笔记,也是跟大家分享存储过程的学习和使用方法。
参考笔记三,P34.1、P35、P37.1。
文章目录
- 1、概述
-
- 1、优点
- 2、缺点
- 3、补充说明
- 2、关于存储过程的使用
-
- 2.1 操作
- 2.2 示例
- 2.4 调用
- 3、`cursor`(游标)
-
- 3.1 概述
- 3.2 示例
- 4、最后
1、概述
“存储过程”是一种存储于数据库、封装了SQL语句和流程控制语句、进而通过类似 “方法调用” color{green}{“方法调用”} “方法调用”的形式来调用(如:传参、获取返回值)、从而实现业务功能(即将一定程序业务迁移到数据库内,将业务交由数据库管理)的数据结构。
1、优点
若存储过程在单个连接中被多次调用,调用的就是缓存内的存储过程,进一步提高了查询速度;否则会先对存储过程进行编译,此时存储过程的执行效率相当于查询;
2、缺点
3、补充说明
关于流程控制语句,可查阅博文《[MySQL]关于流程控制语句的简述》。
大都是情况下,存储过程内都会包含流程控制语句。为何?因为使用存储过程的原因无非两种:
2、关于存储过程的使用
2.1 操作
工具:navicat。
所有存储过程存储于数据表information_schema.routines
中。
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的员工的工资,并将值赋予变量 doubleSal
select 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表示删除...)
;empNo、empName
前的in/out
是 参数模式 color{green}{参数模式} 参数模式,用于声明此参数是否可用于传入 / 传出,默认为in
。in
表示输入参数,限制参数只能用于传入,即形参;out
表示输出参数,限制只能用于传出,即返回值。第3种参数模式:
inout
,表示此参数既可传入,也可传出(具体如何使用,后续补充)。begin...end
囊括;;
”(分号)结尾;select 参数
),此存储过程无结果。当然,select
后也可是常量;declare
)必须置于开头,且变量或条件的定义要在游标(cursor
)的定义之前;2.4 调用
call P_admin_EIByENo_Sel(1001, @);
这是固定格式,无论是在navicat命令行、cmd,还是在程序中。
1001
对应传入参数empNo
;@
对应传出参数empName
,也可以是@empName
、@xx
,就目前我所知,@
后的标识任意(存储过程的返回值由select 变量
决定,与@
后的标识无关,但传出参数empName
的位置必须至少有一个@
(相当于占位符)。
存储过程的实参与Java方法实参有一定类似,即赋值类型限制。如示例,可以是1001
,而不能是'1001'
(字符型)。
3、cursor
(游标)
3.1 概述
什么是游标? color{grey}{什么是游标?} 什么是游标?
“游标”是一种能够对结果集中的每一行记录进行定位、并对所指向记录的数据进行操作的数据结构。
如:Java迭代器(iterator
)中的也是游标,也称之为 “光标” color{blue}{“光标”} “光标”,其初始指向第一个元素的前面,即-1
。
游标的用途是什么? color{grey}{游标的用途是什么?} 游标的用途是什么?
迭代器有何用途?遍历。因此,存储过程中的游标是用于控制遍历,或者说用于在循环中获取记录。
3.2 示例
功能:根据用户ID,删除评论和评论回复记录。
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_RRTUByUid_Del`(in userId int)
BEGIN
declare rComId int default 0;// 评论ID
declare rRepN int default 0;// 评论回复数
// 定义游标
declare rComId_cursor cursor for
select comment_id from gd_resource_comment where user_id = userId;// 根据用户ID查询所有评论ID
declare rComId_next int default 0;
declare continue handler for not found set rComId_next = -1;// -- -A
open rComId_cursor;// 打开游标
getRComId:loop
// 从结果集中获取一行记录。结合上下文,此结果集是当前用户旗下的所有评论ID,
// 因此每次获取(fetch)的是其中一个评论ID
fetch rComId_cursor into rComId;
// 查询当前评论ID(rComId)所对应的评论回复数
select count(1) into rRepN from gd_resource_response where comment_id = rComId;
if rRepN > 0 then
delete from gd_resource_response where comment_id = rComId;// 删除评论回复
end if;
delete from gd_resource_comment where comment_id = rComId;// 删除评论
if rComId_next = -1 then// --------------------------------------B
leave getRComId;// 跳出循环,类似 break
end if;
end loop getRComId;
close rComId_cursor;// 关闭游标
END
示例说明 + 注意事项:
declare 游标名 cursor for select_statement
;(其中的select_statement
是查询型SQL语句)fetch
)前需要先打开(open 游标名
),游标打开时如iterator
的游标一般,初始指向第一行的前面。使用完后(循环结束)最好关闭游标(close 游标名
)。其中,游标可多次打开(可用于多个循环);fetch 游标名 into 变量
;大家肯定用过Java迭代器,当调用
next()
时,在底层会先判断是否存在下一个元素,若存在,则返回此元素;否则返回null
,不会出现异常。而在
cursor
中,当fetch
时,同样会先判断是否存在下一行记录,若存在则返回此记录,否则报错。(A 的作用就是避免报错)实现思路: 先判断是否存在下一行记录,若不存在则跳出循环,避免下一次
fetch
。具体实现: 定义 A,格式:
declare continue handler for not found set 变量 = 值
。什么意思呢?就是当fetch
时,A 也会执行,若满足not found
(即不存在下一行记录)时,执行变量 = 值
。那么,就可以使用此变量来控制循环(示例中 B,结束循环)。4、最后
本文中的例子是为了方便大家理解、便于阐述存储过程而简单举出或是我曾用过的,不一定有实用性。
其实存储过程的细节很多,只是我没有那么细致地一一进行阐述。我阐述的原则是“以吾之理解,着重之阐述”。因此,这篇文章可能并不适合 0 基础。
给大家推荐两篇博文(转发),这是我系统学习MySQL存储过程时参考的文章。
如果大家想要快速掌握这个知识点,我的建议是“多测试,学以致用”。
本文完结。