[MySQL]存储过程

【版权声明】未经博主同意，谢绝转载！（请尊重原创，博主保留追究权）
https://www.cnblogs.com/cnb-yuchen/p/18032044
出自【进步*于辰的博客】

存储过程的细节很多，而在实际工作中又未必都能涉及这些细节，工作时间一长，就可能忘记，于是特来写这篇文章，既是为自己做个笔记，也是跟大家分享存储过程的学习和使用方法。
参考笔记三，P34.1、P35、P37.1、P66.1。

1、介绍
2、存储过程的使用
3、cursor（游标）
最后

1、介绍

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

优点：

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

缺点：

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

补充说明：

关于流程控制语句，可查阅博文《[MySQL]流程控制语句》。

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

封装一条复杂的SQL语句（往往是一个事务）。
封装多个原子操作（SQL语句），而这些原子操作间会进行一些逻辑运算或数据处理。

2、存储过程的使用

2.1 可视化操作（navicat）

工具：navicat。
在这里插入图片描述

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

2.2 示例

数据表：

员工表：emp(emp_no, emp_name, emp_salary, ...)

需求：查询工资是某员工双倍的员工的员工名。
（注：此示例非常简单，仅是向大家展示存储过程的基本使用，关键在于后续对存储过程使用的说明）

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是参数模式，用于声明此参数是否可用于传入 / 传出，默认为in。in表示输入参数，限制参数只能用于传入，即形参；out表示输出参数，限制只能用于传出，即返回值。第三种参数模式：inout，表示此参数既可传入，也可传出（具体如何使用，后续补充）。

注意：

存储过程名不能包含“-”（连字符）。
参数最好不要与字段名称相同。
存储过程体必须用begin...end囊括。
每行必须以“;”（分号）结尾。
任何参数，若未初始化（设置默认值），则当将此参数作为返回值时（select 参数），此存储过程无结果。当然，select后也可是常量。
语句体（SQL语句）不能嵌套流程控制语句，如：if、loop。
所有的定义（declare）必须置于开头，且变量或条件的定义必须在游标（cursor）的定义之前，且游标定义必须在continue handler之前。（注：continue handler见下文）

2.4 调用

call P_admin_EIByENo_Sel(1001, @);

这是固定格式，无论是在navicat命令行、cmd，还是在程序中。

1001对应传入参数empNo；@对应传出参数empName，也可以是@empName、@xx，就目前我所知，@后的标识任意（存储过程的返回值由select 变量决定，与@后的标识无关，但传出参数empName的位置必须至少有一个@（相当于占位符）。

存储过程的实参与Java方法实参有一定类似，即赋值类型限制。如示例，可以是1001，而不能是'1001'（字符型）。

3、cursor（游标）

3.1 介绍

什么是游标？“游标”是一种能够对结果集中的每一行记录进行定位、并对所指向记录的数据进行操作的数据结构。
如：Java迭代器（iterator）中的也是游标，也称之为“光标”，其初始指向第一个元素的前面，即-1。

游标的用途是什么？迭代器有何用途？遍历。因此，存储过程中的游标是用于控制遍历，或者说用于在循环中获取记录。

3.2 示例

数据表：

用户表：gd_user(user_id, ...);
试题收藏表：gd_resource_collect(collect_id, user_id, ...);
试题评论表：gd_resource_comment(comment_id, user_id, ...);
评论回复表：gd_resource_response(response_id, comment_id, ...);

需求：根据用户ID，移除所有用户相关记录。

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_admin_userByUId_Del`(IN `userId` int)
BEGIN
	
	declare result int default 0; /*结果*/
	declare isExp int default 0; /*是否出现异常*/
	declare tempId int; /*临时ID*/
	declare next int default 1; /*是否有下一行*/
	
	declare csCommentId cursor for select comment_id from gd_resource_comment where user_iD = userId; /*查询用户对应收藏ID*/
	declare continue handler for not found set next = 0; /*下一行处理*/------A
	declare continue handler for sqlexception begin set isExp = 1; set result = 0; end; /*异常处理*/
	
	start transaction;/*打开事务*/
	
	update gd_resource_collect set del = 1 where user_id = userId;/*移除用户对应收藏记录*/
	
	/*移除用户对应评论记录*/
	open csCommentId; /*打开游标*/
	loop1: loop
		fetch csCommentId into tempId;
		if next = 0 then
			leave loop1;
		end if;
		
		update gd_resource_response set del = 1 where comment_id = tempId; /*移除评论回复*/
		update gd_resource_comment set del = 1 where comment_id = tempId; /*移除评论*/
	end loop loop1;
	close csCommentId;

	update gd_user set del = 1 where user_id = userId; /*移除用户*/ 
	
	if isExp = 1 then /*判断是否出现异常*/
		rollback;
	else
		commit;
		set result = 1;
	end if;

	select result;

END

说明：

游标定义格式：declare 游标名 cursor for select_statement。（其中的select_statement是查询型SQL语句）
游标使用（fetch）前需要先打开（open 游标名），游标打开时如iterator的游标一般，初始指向第一行的前面。使用完后（循环结束）建议关闭游标（close 游标名）。其中，游标可多次打开。
获取游标值（下一行记录）：fetch 游标名 into 变量。

3.3 示例说明

1：示例中not found与next是做什么的？

大家用过Java迭代器的就知道，当调用next()时，在底层会先判断是否存在下一个元素，若存在，则返回此元素；否则返回null，不会出现异常。

在cursor中，当fetch时，同样会先判断是否存在下一行记录，若存在则返回此记录。不同的是，若不存在，则返回当前记录（死循环）。

因此，示例中 A 的作用是，当fetch时，A 也会执行，若满足not found时（不存在下一行记录），执行set 变量 = 值。那么，就可以使用此变量退出循环，避免“死循环”。

2：“游标可多次打开”是什么意思？

这就关乎游标的定义了（declare...cursor...）。在存储过程的规范中，有这么一条：

所有的定义（declare）必须置于开头，且变量或条件的定义必须在游标（cursor）的定义之前，且游标定义必须在continue handler之前。（上文【存储过程的使用】中说道）

所以，curosr的定义也必须置于开头。不过，select_statement并不是在游标定义时就执行，而是在打开游标时。

因此，在游标再次打开时，其再次指向第一行的前面（可多次遍历）。

3：示例中的sqlexception是做什么的？

在plsql中，默认情况下，DML需要手动提交或回滚。其中，可以使用保存点（savepoint）设置回滚范围。而在navicat中，由于其默认将某一条SQL都作为一个事务，所有DML都自动提交，故无法自定义回滚。

因此，可以使用start transaction;（打开事务）自定义事务范围，关闭自动提交功能。

寻常我们在navicat中执行SQL可能并不会注意这个问题，但在存储过程中则必须如此（自定义事务）。

因此，sqlexception的作用就是当存储过程中的某条SQL出现异常时，执行后面begin...end;内的内容，最后再通过某个变量判断是否出现异常，进而提交或回滚。（注：存储过程中出现异常时不会停止执行）

最后

本文中的例子是为了方便大家理解、便于阐述存储过程而简单举出或是我曾用过的，不一定有实用性，仅是抛砖引玉。

其实存储过程的细节很多，只是我没有那么细致地一一进行阐述。我阐述的原则是“以吾之理解，着重之阐述”。因此，这篇文章可能并不适合 0 基础。

给大家推荐两篇博文（转发），这是我系统学习MySQL存储过程时参考的文章。

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

本文完结。

posted @ 2024-05-20 18:45 进步·于辰阅读(13) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

进步·于辰的Java技术分享栈

一位湖南永州的Java开发工程师。