数据库存储过程是什么 数据库存储过程怎么用 收藏 0

数据库|数据库存储过程相关学习

哈喽，你好啊，我是雷工！

前面学习记录了数据库中，这里接着学习记录数据库中存储过程的相关内容。

以下为学习笔记。

数据库存储过程是一组为了完成特定功能的SQL语句集，它们被编译并存储在数据库中。

存储过程可以接受输入参数、返回输出参数、返回结果集，并且可以在数据库中直接调用。

存储过程的使用在数据库管理和开发中非常普遍，因为它们具有多种优点，如提高性能、代码重用、安全性和减少网络流量等。

注意：

如果仅仅是查询，建议使用视图，特别是针对不同的角色调用不同权限的数据时，使用视图是非常方便的。

存储过程的主要特点有以下几个：

2.1、预编译和存储

存储过程在第一次调用时被编译并存储在数据库中，之后的调用可以直接执行预编译的代码，从而减少编译时间。

2.2、参数化

存储过程可以接受输入参数和返回输出参数，这使得存储过程非常灵活，可以处理不同的输入情况。

2.3、返回值

除了返回输出参数外，存储过程还可以返回结果集（类似于SQL查询的结果），这使得它们非常适用于检索数据。

2.4、事务控制

存储过程中可以包含事务控制语句确保数据库操作的原子性和一致性。

2.5、安全性

存储过程可以限制直接访问数据库表的权限，用户只能通过存储过程来操作数据，从而提高了数据库的安全性。

2.6、代码重用

存储过程可以被多次调用，避免了重复编写相同的SQL代码，提高了代码的重用性和可维护性。

2.7、减少网络流量

由于存储过程在服务器端执行，只传递调用存储过程的请求和结果，减少了客户端和服务器之间的数据传输量，从而节约网络流量。

03 后记

关于数据库存储过程这里这是学习概念，看视频教学操作，未做实际练习，后续在实际项目中再实际应用。

面试官突然问我MySQL存储过程，我竟然连基础都不会！（详细）

MySQL存储过程

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升

当我们了了解存储过程是什么之后，就需要了解数据库中存在的这三种类型的数据库存储类型程序，如下：

• 存储过程： 存储过程是最常见的存储程序，存储过程是能够接受输入和输出参数并且能够在请求时被执行的程序单元。
• 存储函数： 存储函数和存储过程很相像，但是它的执行结果会返回一个值。最重要的是存储函数可以被用来充当标准的 SQL 语句，允许程序员有效的扩展 SQL 语言的能力。
• 触发器： 触发器是用来响应激活或者触发数据库行为事件的存储程序。通常，触发器用来作为数据库操作语言的响应而被调用，触发器可以被用来作为数据校验和自动反向格式化。

注意： 其他的数据库提供了别的数据存储程序，包括包和类。目前MySQL不提供这种结构。

虽然目前的开发中存储程序我们使用的并不是很多，但是不一定就否认它。其实存储程序会为我们使用和管理数据库带来了很多优势：

• 使用存储程序更加安全。
• 存储程序提供了一种数据访问的抽象机制，它能够极大的改善你的代码在底层数据结构演化过程中的易维护性。
• 存储程序可以降低网络拥阻，因为属于数据库服务器的内部数据，这相比在网上传输数据要快的多。
• 存储程序可以替多种使用不同构架的外围应用实现共享的访问例程，无论这些构架是基于数据库服务器外部还是内部。
• 以数据为中心的逻辑可以被独立的放置于存储程序中，这样可以为程序员带来更高、更为独特的数据库编程体验。
• 在某些情况下，使用存储程序可以改善应用程序的可移植性。（在另外某些情况下，可移植性也会很差！）

我们要知道在Java语言中，我们使用数据库与Java代码结合持久化存储需要引入JDBC来完成。会想到JDBC，我们是否还能想起SQL注入问题呢？虽然使用PreparedStatement解决SQL注入问题，那就真的是绝对安全吗？不，它不是绝对安全的。

这时候分析一下数据库与Java代码的连接操作流程。在BS结构中，一般都是浏览器访问服务器的，再由服务器发送SQL语句到数据库，在数据库中对SQL语句进行编译运行，最后把结果通过服务器处理再返回浏览器。在此操作过程中，浏览器对服务器每发送一次对数据库操作的请求就会调用对应的SQL语句编译和执行，这是一件十分浪费性能的事情， 性能下降 了就说明对数据库的操作 效率低 了。

还有一种可能是，在这个过程中进行发送传输的SQL语句是对真实的库表进行操作的SQL语句，如果在发送传输的过程中被拦截了，一些不法分子会根据他所拦截的SQL语句推断出我们数据库中的库表结构，这是一个很大的 安全隐患 。

关于可维护性的提高，这里模拟一个场景。通常数据库在公司中是由DBA来管理的，如果管理数据库多年的DBA辞职了，此时数据库会被下一任DBA来管理。这里时候问题来了，数据库中这么多的数据和SQL语句显然对下一任管理者不太友好。就算管理多年的DBA长时间不操作查看数据库也会忘记点什么东西。所以，我们在需要引入存储程序来进行SQL语句的统一编写和编译， 为维护提供了便利 。（其实我觉得这个例子并不生动合理，但是为了大家能理解，请体谅！）

讲了很多存储程序的优势演变过程，其核心就是：需要将编译好的一段或多段SQL语句放置在数据库端的存储程序中，以便解决以上问题并方便开发者直接调用。

存储过程时数据库的一个重要的对象，可以封装SQL语句集，可以用来完成一些较复杂的业务逻辑，并且可以入参（传参）、出参（返回参数），这里与Java中封装方式十分相似。

而且创建时会预先编译后保存，开发者后续的调用都不需要再次编译。

存储过程使用的优缺点其实在1.3中的优势中说到了。这里我简单罗列一下存储过程的优点与缺点。

• 优点：
• 在生产环境下，可以通过直接修改存储过程的方式修改业务逻辑或bug，而不用重启服务器。
• 执行速度快，存储过程经过编译之后会比单独一条一条编译执行要快很多。
• 减少网络传输流量。
• 便于开发者或DBA使用和维护。
• 在相同数据库语法的情况下，改善了可移植性。
• 缺点：
• 过程化编程，复杂业务处理的维护成本高。
• 调试不便。
• 因为不同数据库语法不一致，不同数据库之间可移植性差。

英语好或者有能力的小伙伴可以去参考一下官方文档。如果不参考官方文档，没关系，我在下面也会详细讲述MySQL存储过程的各个知识点。

in 是定义传入参数的关键字。 out 是定义出参的关键字。 inout 是定义一个出入参数都可以的参数。如果括号内什么都不定义，就说明该存储过程时一个无参的函数。在后面会有详细的案例分析。

注意：SQL语句默认的结束符为 ; ，所以在使用以上存储过程时，会报1064的语法错误。我们可以使用 DELIMITER 关键字临时声明修改SQL语句的结束符为 // ，如下：

例如：使用存储过程来查询员工的工资（无参）

注意：如果在特殊的必要情况下，我们还可以通过 delimiter 关键字将 ; 结束符声明回来使用，在以下案例中我并没有这样将结束符声明回原来的 ; ，在此请大家注意~

为什么我在这里提供了drop（删除）呢？

是因为我们在使用的时候如果需要修改存储过程中的内容，我们需要先删除现有的存储过程后，再creat重新创建。

声明局部变量语法： declare var_name type [default var_value];

注意：局部变量的定义，在begin/end块中有效。

使用set为参数赋值

使用into接收参数

用户自定义用户变量，当前会话（连接）有效。与Java中的成员变量相似。

• 语法： @val_name
• 注意： 该用户变量不需要提前声明，使用即为声明。

会话变量是由系统提供的，只在当前会话（连接）中有效。

语法： @@session.val_name

这里我获取了一下所有的会话变量，大概有500条会话变量的记录。等我们深入学习MySQL后，了解了各个会话变量值的作用，可以根据需求和场景来修改会话变量值。

image-20200610112512964

全局变量由系统提供，整个MySQL服务器内有效。

语法： @@global.val_name

入参出参的语法我们在文章开头已经提过了，但是没有演示，在这里我将演示一下入参出参的使用。

语法： in|out|inout 参数名 数据类型 , …

in 定义出参； out 定义入参； inout 定义出参和入参。

出参in

使用出参in时，就是需要我们传入参数，在这里可以对参入的参数加以改变。简单来说in只负责传入参数到存储过程中，类似Java中的形参。

入参out

在使用out时，需要传入一个参数。而这个参数相当于是返回值，可以通过调用、接收来获取这个参数的内容。简单来说out只负责作返回值。

入参出参inout

inout关键字，就是把in和out合并成了一个关键字使用。被关键字修饰的参数既可以出参也可以入参。

扩展： timestampdiff(unit, exp1, exp2) 为exp2 – exp1得到的差值，而单位是unit。（常用于日期）

扩展例子： select timestampdiff(year,’2020-6-6‘,now()) from emp e where id = 1;

解释扩展例子：查询员工表中id为1员工的年龄，exp2就可以为该员工的出生年月日，并以年为单位计算。

举例：传入所查询的id参数查询工资标准（s<=6000为低工资标准；6000 <=10000为中工资标准；10000 <=15000为中上工资标准；s style=\”font-size: inherit; color: inherit; line-height: inherit; margin: 0px; padding: 0px;\”>=15000为高工资标准）

关于case语句，不仅仅在存储过程中可以使用，MySQL基础查询语句中也有用到过。相当于是Java中的switch语句。

loop为死循环，需要手动退出循环，我们可以使用 leave 来退出循环

可以把leave看成Java中的break；与之对应的，就有 iterate （继续循环）也可以看成Java的continue

注意：别名和别名控制的是同一个标签。

示例1：循环打印1~10（leave控制循环的退出）

注意：该loop循环为死循环，我们查的1~10数字是i，在死循环中设置了当大于等于10时停止循环，也就是说先后执行了10次该循环内的内容，结果查询了10次，生成了10个结果（1~10）。

image-20200610191639524

示例2：循环打印1~10（iterate和leave控制循环）

注意：这里我们使用字符串拼接计数器结果，而条件如果用iterate就必须时 i < 10 了！

image-20200610193153512

repeat循环类似Java中的do while循环，直到条件不满足才会结束循环。

示例：循环打印1~10

while循环就与Java中的while循环很相似了。

示例：循环打印1~10

至于流程控制的继续和结束，我们在前面已经使用过了。这里再列举一下。

游标是可以得到某一个结果集并逐行处理数据。游标的逐行操作，导致了游标很少被使用！

了解了游标的语法，我们开始使用游标。如下：

示例：使用游标查询id、name和salary。

image-20200610203622749

因为游标逐行操作的特点，导致我们只能使用游标来查询一行记录。怎么改善代码才可以实现查询所有记录呢？聪明的小伙伴想到了使用循环。对，我们试试使用一下循环。

image-20200610204034224

我们使用循环之后，发现有一个问题，因为循环是死循环，我们不加结束循环的条件，游标会一直查询记录，当查到没有的记录的时候，就会抛出异常 1329：未获取到选择处理的行数 。

如果我们想办法指定结束循环的条件该怎么做呢？

这时候可以声明一个boolean类型的标记。如果为true时则查询结果集，为false时则结束循环。

上述代码你会发现并没有写完，它留下了一个很严肃的问题。当flag = false时候可以结束循环。但是什么时候才让flag为false啊？

于是，MySQL为我们提供了一个 handler 句柄。它可以帮我们解决此疑惑。

handler句柄语法： declare continue handler for 异常 set flag = false;

handler句柄可以用来捕获异常，也就是说在这个场景中当捕获到 1329：未获取到选择处理的行数 时，就将flag标记的值改为false。这样使用handler句柄就解决了结束循环的难题。让我们来试试吧！

终极版示例：解决了多行查询以及结束循环问题。

image-20200610210925964

在执行结果中，可以看出查询结果以多次查询的形式，分布显示到了每一个查询结果窗口中。

注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

注意：异常情况可以写异常错误码、异常别名或SQLSTATE码。

• CONTINUE： 继续
• EXIT： 退出
• UNDO： 撤销

• mysql_error_code
• SQLSTATE [VALUE] sqlstate_value
• condition_name
• SQLWARNING
• NOT FOUND
• SQLEXCEPTION

注意：MySQL中各种异常情况代码、错误码、别名和SQLSTATEM码可参考官方文档：

https://dev.mysql.com/doc/refman/5.6/en/server-error-reference.html

需求：创建下个月的每天对应的表，创建的表格式为： comp_2020_06_01、comp_2020_06_02、…

描述：我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为（注意，此处是假设用数据库保存），当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！

预编译： PREPARE 数据库对象名 FROM 参数名

执行： EXECUTE 数据库对象名 [USING @var_name [, @var_name] …]

通过数据库对象创建或删除表： {DEALLOCATE | DROP} PREPARE 数据库对象名

在MySQL存储过程中，如果没有显示的定义characteristic，它会隐式的定义一系列特性的默认值来创建存储过程。

• LANGUAGE SQL
• 存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言
• NOT DETERMINISTIC
• 是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用
• CONTAINS SQL
• 提供子程序使用数据的内在信息，这些特征值目前提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。有以下选择：CONTAINS SQL表示子程序不包含读或者写数据的语句NO SQL 表示子程序不包含sqlREADS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句MODIFIES SQL DATA 表示子程序包含写数据的语句。
• SQL SECURITY DEFINER
• MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户。所以次值用来指定存储过程是使用创建者的许可来执行，还是执行者的许可来执行，默认值是DEFINERDEFINER 创建者的身份来调用，对于当前用户来说：如果执行存储过程的权限，且创建者有访问表的权限，当前用户可以成功执行过程的调用的INVOKER 调用者的身份来执行，对于当前用户来说：如果执行存储过程的权限，以当前身份去访问表，如果当前身份没有访问表的权限，即便是有执行过程的权限，仍然是无法成功执行过程的调用的。
• COMMENT \’\’
• 存储过程的注释性信息写在COMMENT里面，这里只能是单行文本，多行文本会被移除到回车换行等

如有死循环处理，可以通过下面的命令查看并杀死（结束）

原文链接：https://www.tuicool.com/articles/INf2qe3

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com