[MySQL]存储过程

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

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

1、介绍

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

优点:

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

缺点:

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

补充说明:

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

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

  1. 封装一条复杂的SQL语句(往往是一个事务)。
  2. 封装多个原子操作(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

说明:

  1. 创建存储过程格式:create procedure 存储过程名(参数列表) begin...end。(示例中definer...语句是指明用户、连接、数据库等)
  2. 存储过程命名规范:P_[前/后台标识]_[模块/功能简称]By[条件名简称]_Sel(Sel表示查询,Del表示删除...)
  3. 在参数empNo、empName前的in/out参数模式,用于声明此参数是否可用于传入 / 传出,默认为inin表示输入参数,限制参数只能用于传入,即形参out表示输出参数,限制只能用于传出,即返回值。第三种参数模式:inout,表示此参数既可传入,也可传出(具体如何使用,后续补充)。

注意

  1. 存储过程名不能包含“-”(连字符)。
  2. 参数最好不要与字段名称相同。
  3. 存储过程体必须用begin...end囊括。
  4. 每行必须以“;”(分号)结尾。
  5. 任何参数,若未初始化(设置默认值),则当将此参数作为返回值时(select 参数),此存储过程无结果。当然,select后也可是常量。
  6. 语句体(SQL语句)不能嵌套流程控制语句,如:ifloop
  7. 所有的定义(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

说明:

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

3.3 示例说明

1:示例中not foundnext是做什么的?

大家用过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存储过程时参考的文章。

  1. MySQL中的存储过程(详细篇)
  2. Mysql存储过程大全

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

本文完结。

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