MySQL数据库存储过程与存储函数详解

MySQL数据库存储过程与存储函数详解

引用

CSDN

1.

https://blog.csdn.net/Crayon26/article/details/145608320

本文将深入解析MySQL存储过程与存储函数的本质区别，手把手教你如何通过参数传递、流程控制打造高效数据库操作模板！详解创建/调用秘籍，分享事务处理与性能优化实战经验，更有常见陷阱避坑指南。学会这些黑科技，让你的数据库开发效率翻倍，轻松应对高并发场景！

一、存储过程

1.1 简介

在编写应用程序时往往需要我们在应用程序当中去操作MySQL，而在业务逻辑当中一个业务就可能需要操作多次数据库。比如需要先去查询数据库当中的数据，查询结束后根据查询结果再来决定是否需要更新数据，需要更新哪些表的数据。此时在这一逻辑当中需要去操作多次数据库，也就意味着涉及多次网络请求，那么就需要一些优化手段。

我们可以在数据库层面将多条SQL语句封装到一个集合当中，比如将SELECT与UPDATE语句封装为一个SQL集合，接下来在应用程序当中需要去执行这一块逻辑时只需要去调用该SQL集合就可以了。当然，在MySQL服务器当中可以定义很多这样的SQL集合，这就是存储过程的思想。

1.2 基本语法

1.2.1 创建与调用

CREATE PROCEDURE procedure_name [in/out/inout parameter type]
BEGIN
    -- SQL语句
END;

1. 指定参数列表代表存储过程在定义时可以指定它需要输入的参数以及返回的参数信息
2. 固定BEGIN与END内部封装的就是这个存储过程内要封装的SQL语句，可以是一条或多条
3. 同时可以看见创建好的存储过程p1存放在了routines包下

调用：

CALL procedure_name(parameter);

1. 调用存储过程，并且成功统计出student表的总记录数

1.2.2 查看与删除

查看：

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '***';
SHOW CREATE PROCEDURE procedure_name;

1. 查询指定数据库的存储过程及状态信息
   ROUTINE_SCHEMA参数代表数据库名称
   查询到itcast数据库内的存储过程
2. 查看存储过程p1定义的SQL语句
   在查询返回的创建存储过程的SQL语句中有一块参数 DEFINER='root'@'%' ，它指的是创建用户是我们当前登录的root用户。如果我们在创建存储过程时没有指定，那么它会自动加上该默认参数。
   直接点开左侧的p1文件也可以查询到存储过程内定义的SQL语句

删除：

DROP PROCEDURE [if exists] procedure_name;

1.2.3 delimiter设置结束符

delimiter **;

1. 在命令行中创建存储过程并执行SQL语句时会报错，因为在命令行中执行SQL语句时只要遇到分号，MySQL就会认为你当前的SQL语句已经结束了，但是此时的语法并不符合创建存储过程的语法，说明问题出现在分号上
2. 此时我们可以重新设置一个结束符
   执行了delimiter $$语句后，就代表现在SQL语句是以 ‘$$’ 符号来结束的，如果你执行时传递了分号就不会被认为SQL结束了。
   可以成功重新执行创建存储过程语句，并查询
3. 可以通过delimiter选项将结束符重新设置回来

1.3 变量

1.3.1 系统变量

全局变量与系统变量

1. 全局变量：根据所有会话都有效的变量
   例如每一个查询工作台都是一个会话

新建一个查询控制台就是一个新的会话

2. 系统变量：仅在当前会话内有效的变量

查看系统变量

1. 查看数据库中所有的系统变量
   若不加GLOBAL/SESSION参数，那么默认是SESSION会话级别的系统变量
2. 通过模糊匹配查找系统变量
   如果想要去查看跟事务自动提交的变量，但是记不清变量名称，只记得关键字’auto’，那么就可以使用模糊查询
3. 准确查看某个系统环境变量的值
   '1’代表该系统变量的开关已开启，'0’则代表已关闭

设置系统变量

1. 在关闭事务自动提交的开关后执行插入数据操作的SQL
   发现数据并没有更新
   即数据没有插入成功，因为现在事务自动提交的开关关闭了，意味着需要我们手动提交
   COMMIT提交以后数据就能正常插入，代表我们设置的该系统变量生效了
2. 虽然我们执行的是全局修改变量，但是当服务器重启之后所有的参数又会初始化为默认值
   想要永久修改系统变量的值仅仅靠SET GLOBAL指令是做不到的，此时就需要去修改MySQL当中的系统配置文件

1.3.2 用户自定义变量

注意：@@符指的是系统变量，@符指的是用户自定义变量
用户自定义变量是一种会话变量，无法被其他会话使用

赋值与使用

1. 赋值
   在MySQL当中的比较运算符是’=‘，而不是’==‘，也就是说等号既可以做为赋值运算符，也可以做为比较运算符。为了以示区分，推荐在赋值运算符中使用’:='冒号等于。
   "SELECT 字段名 INTO var FROM table表"代表把从table表中所查询出来的数据赋值给这个用户自定义变量。

使用

用户自定义变量不需要进行声明或初始化
当直接查询一个未进行声明或初始化的用户自定义变量时，只会拿到NULL而并不会报错

1.3.3 局部变量

变量类型指的是数据库表结构在创建时所指定的类型(int，bigint，char，varchar等等)，后面可以跟上可选参数DEFAULT，即给变量指定默认值
调用存储过程并查看执行结果，发现赋值成功

1.4 IF条件判断

这块语法代表的含义是：如果条件1成立，则执行 THEN 之后的SQL逻辑，否则就要去判断ELSEIF后面的条件，若条件成立则再执行 THEN 之后的SQL逻辑，以此类推；若没有需要判断的条件了那么还需要跟一个ELSE，并编写默认逻辑；最后通过END IF来结束。

1.4.1 例题

当前这种语法存在的两大主要问题：

1. 我们定义变量并给他赋值，但是这个值是在我们存储过程定义的时候就写死了的，那么对于这一块逻辑最好的解决办法是通过参数来传递进来；
2. 我们所判定的这个结果最终只是在存储过程当中利于 SELECT 来查询展示出来而已，而我们最终想要的效果是要将值返回回来。
   解决办法将在下一部分参数类型中介绍。

1.5 参数

需要在括号中指定参数列表

1.5.1 例题

第一题

1. 编写创建存储过程的SQL语句
   不需要再在存储过程中声明score与result局部变量，因为他们都已经在参数列表当中被指定出来了
2. 执行该段语句，查看存储过程文件是否更新
   可以发现routines内就只有p4是带参的，其他都为无参
3. 编写调用SQL
   第一个字段score是传入值很好传递(直接传入数字即可)，但是第二个字段result代表要将执行的返回值返回，那么此处就需要用一个变量来进行接收，而这个定义出来的变量是用户自定义变量。执行结束后也就意味着存储过程会将返回的结果赋值给这个用户自定义变量@result，我们只需要使用SELECT就可以查看到这个变量的值了
4. 运行结果

第二题

1. 编写创建存储过程的SQL语句
2. 编写调用存储过程的SQL语句
   如果我们此处直接传递一个值给存储过程而不是变量，那么我们就没有办法取到返回值，所以在这一块也需要定义一个变量，并且需要给该变量先赋一个基础值，然后再去调用存储过程并将计算结果再次赋值给该变量，最后再来查看该变量的值
3. 运行结果

1.6 CASE条件判断

此处所讲解的流程控制语句当中的CASE与MySQL基础篇的CASE函数在语法结构上基本是一致的

1.6.1 逻辑解析

1. 语法一
   若VALUE = VALUE1，那么执行VALUE1后面THEN的SQL语句；若VALUE = VALUE2，那么执行VALUE2后面THEN的SQL语句，并以此类推；若与上述条件都不符合，那么执行最后可选参数ELSE后的逻辑。通过判定VALUE的值到底等于哪一个，从而决定将回去执行哪一个分支。
2. 语法二
   条件表达式写在 WHEN 之后，若条件表达式成立则执行 THEN 之后的SQL逻辑，否则就执行ELSE之后的SQL语句

1.6.2 例题

1.7 循环控制

1.7.1 WHILE

1.7.2 REPEAT

语法结构：对于REPEAT来说，不管这个循环结构是什么样子，内部的SQL逻辑都会先执行一次，然后再判断条件是否满足，若满足则退出，若不满足则进行下一次循环

1.7.3 LOOP

如果在LOOP的语法逻辑中不加入退出循环的条件，就会类似于是WHILE TRUE一直死循环，而LEAVE则是用于指定退出循环的条件。
LEAVE与ITERATE类似于BREAK与CONTINUE。
LOOP循环本身是没有退出条件的，如果想要退出循环就要借助LEAVE语句，后面需指定标记的循环体；若想要直接跳入到下一次循环则就要借助ITERATE语句，后面跟上循环体的标记。
对于LOOP循环我们可以自行定义开始标记与结束标记，开始标记用于标识当前这次循环(注意：在该标记末尾须有一个冒号’😂。

例题

1. 第一题
   
2. 第二题

1.8 游标(也称光标)

游标对应的数据类型为CURSOR

1.8.1 游标的使用情况

假设先前已经定义好了一个存储过程，内部定义了一个INT类型变量与一段利用COUNT聚合函数进行计数并赋值给变量的业务逻辑。但是现在我们要对该存储过程的功能进行升级，根据业务逻辑对其中的SQL语句进行改造，不想再去统计总记录数，而是直接查询出表中所有数据并赋值
此时再去调用该存储过程会出现报错，告诉我们当前存储过程当中的SELECT语句存在不同的列
也就是说从表中查询到的所有数据并不只有一列字段，无法赋值在存储过程中定义好的只有单行单列的INT类型变量
如果我们要想接收的是一张表或结果集，那么就需要使用游标

1.8.2 逻辑解析

下面将以一道例题来讲解游标的实际用法及逻辑解析

1. 游标的声明与普通变量的声明是有顺序的，需要先声明普通变量再声明游标变量
   若普通变量声明在游标变量之后，那么此时运行代码将会报错
2. CURSOR FOR
   CURSOR FOR关键词后面SQL语句的查询结果集将会存储在游标之中，而此处SELECT查询表中数据并返回的结果会包含两个字段(NAME、PROFESSION)。在游标中获取到数据之后，我们要把每一条记录赋值给多个变量，也就是说要把NAME赋值给一个变量，PROFESSION赋值给另外一个变量，此时就需要去声明另外两个变量(UNAME、UPROFESSION)。最后只需要把声明出来的UNAME与UPROFESSION字段的值插入表结构当中即可。
3. 在存储过程内建表
   因为局部变量在接收到数据后，最终是要存储到一个表结构中的。若该表结构不存在，那么我们可以直接在存储过程中编写创建表结构的SQL语句。
   但是在执行存储过程语句之后，该表结构并不会立刻被创建出来，而是在被CALL调用并传入参数之后才会运行存储过程内的SQL语句并创建表格。
4. 开启游标，创建循环体，从游标中获取数据并存入表格
   因为需要去开启游标并获取游标当中的数据，该结果集可以简单理解为一个集合，并且进行循环遍历该集合去获取数据。而循环体之中需要设置退出条件，可以简单理解为游标遍历完毕即退出。最后则把一条条数据插入新表当中。
   因为插入的ID是默认自增的，所以在INSERT插入数据到表格的SQL中直接传NULL值即可。
5. 退出循环体的条件逻辑
   因为需要监测游标中是否已经遍历完毕的逻辑较为复杂，此处我们先将退出条件设置为TRUE(即无限执行)，并执行测试
   虽然表格成功创建，数据都符合条件并且已经成功插入，但是发现报错了
   因为循环体的退出条件应为游标内的数据被获取完毕，也就是提取出为空值，所以我们要去借助MySQL提供的条件处理程序HANDLER。
   也就是我们定义一个条件处理程序HANDLER时可以去指定什么时候该程序会被触发，触发了以后会执行什么动作

1.9 条件处理程序

1.9.1 关键词解析

1. HANDLER_ACTION指的是条件处理程序的类型
2. CONDITION_VALUE指的是条件：当满足什么条件时才会去执行HANDLER动作
3. STATEMENT指的是执行HANDLER动作后还可以去执行具体的SQL逻辑
4. SQLSTATE指的是执行SQL时会抛出的SQL语句状态码，来决定到底执行CONTINUE还是EXIT

1.9.2 逻辑解析

1. 声明条件状态语句
   因为现在的循环体需要满足的条件为：如果游标内没有数据了，那么就直接退出循环体并且把游标关闭，所以此时类型应该为EXIT。而SQLSTATE后面需要跟状态码，因为前面根据我们的测试，当游标内被取出空值时会报错，也就代表游标内没有数据了，符合要求，所以使用报错提示的状态码’02000’。此时若满足该状态码，那么就会执行退出处理。而退出时也要顺便把游标关闭了，所以后面可以跟上关闭游标的SQL语句。
   所以即使我们进行WHILE TRUE循环时游标抓取不到数据了并报错，报’02000’错误后就会进入到条件处理程序，再在条件处理程序当中关闭游标，然后执行退出操作。

1.9.3 重新执行该存储过程

成功创建表格

1.9.4 SQLSTATE的另外三种写法

在MySQL的官方文档详细描述了每一种状态码的含义
也就是说除了填写具体的状态码，还可以直接使用简写的形式(SQLWARNING与NOT FOUND)。也就意味着对于刚才的存储过程来说，可以直接写NOT FOUND来替代具体的状态码’02000’。

二、存储函数

存储过程与存储函数整体的语法结构、流程控制都是一样的，只是里面的基础语法结构略有不同

2.1 语法解析

1. RETURNS指定返回值的类型，后面TYPR指的就是类型，后面跟上可选参数CHARACTERISTIC用于指代当前存储参数的特性，特性值主要包含以下三个(DETERMINISTIC、NO SQL与READS SQL DATA)
2. CHARACTERISTIC说明
   DETERMINISTIC代表传入的参数一样，返回值也一样；NO SQL指的是在当前存储函数当中不包含SQL语句
3. 因为存储函数必须要有返回值，所以必须要在BEGIN与END逻辑执行完成之后，需要RETURN把结果返回回去

2.2 业务示例

1. 编写基础执行逻辑并运行
   直接运行后发现报错：该存储函数需要去指定特性(DETERMINISTIC、NO SQL与READS SQL DATA之一)，因为在当前8.几版本的MySQL是默认开启BINARY LOG二进制日志的，一旦二进制日志开启了，那么他就要求我们必须要去指定当前存储函数的具体特性
2. 加上CHARACTERISTIC选项
   此处我们直接选择DETERMINISTIC或NO SQL都可以
   运行存储函数，可以看到存储过程是P开头的，而存储函数则是F
3. 调用存储函数
   而在存储函数不能通过存储函数名加参数的方式进行调用，而是通过SELECT语句直接查询存储函数的返回值即可

2.3 存储函数的弊端

存储函数的使用比存储过程来说相对更少一些，因为存储函数能做到的事存储过程同样能做到。而且存储函数还存在一个弊端：必须得有返回值，但是同样需要有返回值的逻辑我们却可以同样使用存储过程来实现。因为存储过程中可以指定参数类型为OUT，就可以接收存储过程执行的结果。所以可以使用存储函数的地方我们同样可以使用存储过程来替代