[MySQL]存储过程

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 本文介绍了存储过程的概念、优点和缺点,并通过具体示例详细讲解了存储过程的创建、使用和调用方法。此外,还介绍了游标的定义和使用,帮助读者更好地理解和应用存储过程。文章旨在为读者提供一个学习和使用存储过程的实用指南。

【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
https://developer.aliyun.com/article/1632621
出自【进步*于辰的博客

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

    需求:查询工资是某员工双倍的员工的员工名。
    (注:此示例非常简单,仅是向大家展示存储过程的基本使用,关键在于后续对存储过程使用的说明)
    ``sql CREATE DEFINER=root@localhostPROCEDUREP_admin_EIByENo_Sel(INempNoint,OUTempName` 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`是**参数模式**,用于声明此参数是否可用于传入 / 传出,默认为`in`。`in`表示输入参数,限制参数只能用于传入,即**形参**;`out`表示输出参数,限制只能用于传出,即**返回值**。第三种参数模式:`inout`,表示此参数既可传入,也可传出(具体如何使用,后续补充)。

**注意**:
 1. 存储过程名不能包含“`-`”(连字符)。
 9. 参数最好不要与字段名称相同。
 8. 存储过程体必须用`begin...end`囊括。
 9. 每行必须以“`;`”(分号)结尾。
 10. 任何参数,若未初始化(设置默认值),则当将此参数作为返回值时(`select 参数`),此存储过程无结果。当然,`select`后也可是常量。
 11. 语句体(SQL语句)不能嵌套流程控制语句,如:`if`、`loop`。
 12. 所有的定义(`declare`)必须置于**开头**,且变量或条件的定义必须在游标(`cursor`)的定义之前,且游标定义必须在`continue handler`之前。(注:`continue handler`见下文)
## 2.4 调用
```sql
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语句)
    1. 游标使用(fetch)前需要先打开(open 游标名),游标打开时如iterator的游标一般,==初始指向第一行的前面==。使用完后(循环结束)建议关闭游标(close 游标名)。其中,游标可多次打开。
    2. 获取游标值(下一行记录):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存储过程大全

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

本文完结。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7月前
|
存储
MySQL-存储过程
MySQL-存储过程
71 1
|
7月前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-1
MySQL 中的存储过程
62 2
|
7月前
|
存储 关系型数据库 MySQL
MySQL 中的存储过程-3
MySQL 中的存储过程
41 0
|
存储 SQL 关系型数据库
MySQL中存储过程使用详解
MySQL中存储过程使用详解
463 0
|
存储 SQL 关系型数据库
认识MySQL的存储过程
认识MySQL的存储过程。
69 4
|
存储 SQL Java
MySql轻功-存储过程
存储过程其实完全可以类比成高级语音里的方法和函数。这样看起来就不会抽象,也有个类比。通常函数体都会定义入参、出参,函数内部会定义该函数的计算过程,这里可能会有加减乘除、循环遍历、等等操作。
125 0
|
存储 SQL 缓存
【MySQL】存储过程
【MySQL】存储过程
201 0
【MySQL】存储过程
|
存储 SQL 缓存
MySQL - 存储过程
MySQL - 存储过程
129 0
|
存储 关系型数据库 MySQL
mysql的存储过程
mysql的存储过程
137 0
mysql的存储过程
|
存储 MySQL 关系型数据库
mysql 存储过程
mysql 存储过程 直接点,代码为主: 1.创建表 CREATE TABLE `t_tree` ( `id` int(11) NOT NULL AUTO_INCREMENT, `park_id` int(11) NOT NULL COMMENT '园区id', `parent_id.
1942 0