[MySQL]存储过程

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: [MySQL]存储过程

1、概述

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

1、优点

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

2、缺点

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

3、补充说明

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

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

  1. 封装一条复杂的SQL语句(往往是一个事务);
  2. 封装多个原子操作(SQL语句),而这些原子操作间会进行一些逻辑运算或数据处理。

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

示例说明 + 注意事项:

  1. 创建存储过程格式:create procedure 存储过程名(参数列表) begin...end;(示例中definer...语句是指明用户、连接、数据库等)
  2. 存储过程名不能包含“-”(连字符);
  3. 存储过程命名规范:P_[前/后台标识]_[模块/功能简称]By[条件名简称]_Sel(Sel表示查询,Del表示删除...)
  4. 在参数empNo、empName前的in/out参数模式 \color{green}{参数模式}参数模式,用于声明此参数是否可用于传入 / 传出,默认为in
    in表示输入参数,限制参数只能用于传入,即形参out表示输出参数,限制只能用于传出,即返回值
    第3种参数模式:inout,表示此参数既可传入,也可传出(具体如何使用,后续补充)。
  5. 参数最好不要与字段名称相同;
  6. 存储过程体必须用begin...end囊括;
  7. 每行必须以“;”(分号)结尾;
  8. 任何参数,若未初始化(设置默认值),则当将此参数作为返回值时(即select 参数),此存储过程无结果。当然,select后也可是常量;
  9. 语句体(SQL语句)不能嵌套流程控制语句,如:if、loop;
  10. 所有的定义(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

示例说明 + 注意事项:

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

4、最后

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

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

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

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

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

本文完结。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
13天前
|
存储 SQL NoSQL
mysql存储过程和存储函数
mysql存储过程和存储函数
|
13天前
|
存储 SQL 关系型数据库
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
|
13天前
|
存储 关系型数据库 MySQL
MYSQL存储过程基础入门教程
MYSQL存储过程基础入门教程
14 0
|
13天前
|
存储 关系型数据库 MySQL
Mysql基础第二十六天,使用存储过程
Mysql基础第二十六天,使用存储过程
29 0
Mysql基础第二十六天,使用存储过程
|
13天前
|
存储 关系型数据库 MySQL
mysql存储过程
mysql存储过程
17 3
|
1天前
|
存储 SQL 关系型数据库
mysql 存储过程返回更新前记录
MySQL存储过程帮助我们在更新数据后获取旧记录,适用于审计和回滚操作。通过`BEFORE UPDATE`触发器和临时表,可以捕获并保存更新前的记录。示例展示了如何创建存储过程,当更新`employees`表时,将旧记录保存至临时表`old_records`。此外,还探讨了存储过程在数据版本控制、审计和回滚操作中的应用,以及如何与事务结合确保数据一致性。通过学习和使用存储过程,可以更有效地管理和保护数据库中的数据。
15 0
|
5天前
|
存储 SQL 关系型数据库
【MySQL进阶之路 | 基础篇】存储过程
【MySQL进阶之路 | 基础篇】存储过程
|
13天前
|
存储 SQL 关系型数据库
mysql存储过程示例
mysql存储过程示例
19 0
|
13天前
|
存储 SQL 关系型数据库
【MySQL 数据库】9、存储过程
【MySQL 数据库】9、存储过程
220 0
|
13天前
|
存储 关系型数据库 MySQL
MySQL-调用存储过程
MySQL-调用存储过程
105 2