MySQL中的存储过程详解(上篇)

最新推荐文章于 2024-05-20 13:15:00 发布

子逸爱编程

最新推荐文章于 2024-05-20 13:15:00 发布

阅读量1.2k

点赞数 28

分类专栏： Java分享文章标签： mysql 数据库

本文链接：https://blog.csdn.net/a15766649633/article/details/137794122

版权

Java分享专栏收录该内容

32 篇文章 0 订阅

订阅专栏

使用语言 MySQL

使用工具 Navicat Premium 16

代码能力快速提升小方法，看完代码自己敲一遍，十分有用

拖动表名到查询文件中就可以直接把名字拉进来
中括号，就代表可写可不写

2.5.3 sql security的作用

2.5.4 示例

1.认识存储过程

1.1 存储过程的作用

在数据库开发过程中，存在一个功能需要多个SQL语句组合在一起实现并被多次调用的情况。在数据库中定义存储过程，可以提升代码的复用率，提高开发效率。

1.2 存储过程简介

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数的值(如果该存储过程带有参数)来执行。存储过程是数据库中的一个重要对象。MySQL从5.0版本开始支持存储过程，时数据库引擎更加灵活强大。
存储过程就是数据库SQL语言层面上的代码封装与重用。 (存储过程就跟Java中的方法差不多)

1.3 MySQL存储过程的优缺点

1.3.1 优点

存储过程就是数据库SQL语言层面上的代码封装与重用。
提高性能： 存储过程在数据库服务器上编译和存储，可以减少网络流量，提高性能。一旦创建，它们在多次调用中可以重复使用，而不需要每次都重新编译。
减少网络流量： 因为存储过程在数据库服务器上运行，所以只需要传输参数和结果，而不是传输整个 SQL 查询，这可以减少网络流量，提高效率。
减少重复代码： 存储过程可以将一组 SQL 语句封装在一个单独的单元中，使得可以重复使用相同的逻辑，减少了重复编写相同代码的需求，提高了代码的可维护性。
增强安全性： 通过存储过程，可以控制用户对数据库的访问权限，只需授予执行存储过程的权限，而不需要直接操作底层表。
支持事务处理： 存储过程可以包含事务处理逻辑，允许开发者在单个单元内执行多个 SQL 语句，并且可以确保这些操作要么全部成功，要么全部失败。

1.3.2 缺点

学习成本高： 编写存储过程需要熟悉特定的存储过程语言（如 MySQL 的存储过程语言），这可能需要额外的学习成本。
数据库依赖性： 存储过程是与特定数据库管理系统相关的，如果需要切换到另一个数据库管理系统，可能需要重新编写存储过程。
难以调试： 存储过程通常比在应用程序中直接执行 SQL 更难调试。在某些情况下，错误可能会在运行时才被发现，并且难以跟踪。
维护困难： 存储过程中的逻辑可能会分散在数据库中，这可能使得对代码的维护和修改更加困难。
性能优化困难： 在某些情况下，存储过程可能会导致性能问题，特别是当存储过程中包含复杂的逻辑或查询时，需要进行额外的性能优化。

存储过程在提高性能、减少网络流量、减少重复代码和增强安全性等方面具有优势，但在学习成本高、数据库依赖性强、调试困难和维护困难等方面存在一些挑战。因此，在选择是否使用存储过程时，需要根据具体的应用场景和需求权衡其优缺点。

2.创建和调用存储过程

2.1 创建存储过程

存储过程简称过程(Procedure)。在MySQL中，使用 create procedure语句创建存储过程的语法格式如下:

create procedure 过程名([过程参数[,.....]])
[特性]
存储过程体

过程名一般以pro_开头;

其中，特性为可选项，用于调整存储过程的行为。下面对一些常用的特性进行说明:

2.1.1 常用特性

LANGUAGE SQL:存储默认语言，默认值为SQL
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:存储过程要做哪类工作，默认值为CONTAINS SQL。
SQL SECURITY{DEFINER|INVOKER}:用来指定存储过程的执行权限，默认值为DEFINER。
DEFINER:使用创建者的权限执行。
INVOKER:使用执行者的权限执行。
COMMENT'string':存储过程的注释信息。

2.1.2 声明默认语句分隔符

在MySQL中，默认使用";"作为分隔符，使用DELIMITER关键字可以改变分隔符。在创建存储过程，首先声明分隔符。将分隔符设置为"$$"或"//"的语法格式如下:

DELIMITER $$ 或者 DELIMITER //

如果没有声明分隔符，编译器就会把存储过程当成SQL语句处理，这样编译过程就会报错。由于我使用的版本是Navicat Premium 16，已经优化了这个问题，所以可以不用声明分隔符。注意，最后要把分隔符还原。语法格式如下:

delimiter ;

2.1.3 参数的3种类型

在MySQL中，存储过程的参数包括3种类型。

in:输入参数。该参数的值必须在调用存储过程时指定，在存储过程中可以使用该参数，但它不能被返回。
out:输出参数。该参数可以在存储过程中发生改变并可以返回。
inout:输入输出参数。在参数的值在调用存储过程时指定，在存储过程中可以被改变和返回。定义参数的语法如下:

[in | out | inout] 参数名数据类型

如果需要定义多个参数，需要使用','进行分隔。

2.1.4 过程体的标识

在定义存储过程的过程体时，需要标识开始和结束。语法格式如下:

begin ... end 分隔符

end后面必须使用delimiter语句中设置的分隔符为结束。

2.2 调用存储过程

创建存储过程之后，如何进行调用呢?在MySQL中使用call关键字调用存储过程，语法非常简单。语法格式如下:

call 存储过程名(参数列表);

存储过程调用类似于Java中的方法调用。括号中根据存储过程的定义包含相应的参数。下面看具体示例:

2.2.1 无参示例

运行结果

这样会打印出三个结果，分别时存储过程中对应的三个语句，相比执行单独的SQL语句，存储过程最大的优势是将一系列SQL语句集合起来，允许使用参数，使开发过程变得更加灵活。

2.2.2 带参示例

运行结果

这里使用select into将查询获得的数据放入输出参数。调用存储过程时，必须在过程名后面的括号中包含与定义过程相匹配数目的参数，如@a。这里把@a成为用户变量。加两个@@号的就是全局变量(系统变量)。调用过程结束，就可以通过select查看输出结果。

2.3 存储过程中的变量

类似Java等其他编程语言，定义存储过程时可以使用使用变量。声明变量的语法格式如下:

declare 变量名[,变量名....] 数据类型 [default 值];

例如，声明交易时间变量trade_time，并设置默认值为2020-07-10

declare trade_time date default '2020-07-10';

2.3.1 一行定义多个变量

DECLARE var1 INT, var2 VARCHAR(255), var3 DECIMAL(10,2);-- 这里的一行中定义了三个变量：var1 是一个整数类型的变量，var2 是一个长度为 255 的字符串类型的变量，var3 是一个十进制数类型的变量，精度为 10，小数点后保留 2 位。

也可以分为多行来一个一个定义，意思都是一样的 ;

2.3.2 变量赋值

在定义存储过程时，所有局部变量的声明一定要放在存储过程体的开始，否则会提示语法错误；
声明变量后，可以给变量赋值。语法格式如下:

set 变量名=表达式值[,变量名=表达式...];

例如，设置变量total的值为100：

set total=100；

2.3.3 用户自定义变量和系统变量

在MySQL中，变量包含用户自定义变量和系统变量两种。这里重点讲用户自定义变量。

MySQL用户自定义变量包括局部变量和会话变量。

局部变量一般用于SQL的语句块中，如存储过程中的begin和end语句块，其作用域仅限于于定义该变量的语句块内，生命周期也仅限于该存储过程的调用期间。在存储过程执行到end时，局部变量就会被释放。
会话变量也被称为用户变量，是服务器为每个客户端连接维护的变量，与MySQL客户端时绑定的。用户变量可以暂存值，并传递给同一连接中其他SQL语句使用。在MySQL客户端连接退出时，用户变量就会被释放。用户变量创建时一般"@"开头，形式为"@变量名"。

2.3.4 示例

使用select into语句可以一次给多个变量赋值，用逗号隔开然后有对应的查询列即可；
使用into打印数据不能有多个数据返回，就是一次只能存一个值，不能同时返回多个值

运行结果

定义用户变量

运行结果

2.4 使用navicat创建存储过程

存储过程为多条SQL语句的集合，在实际项目开发过程中具有广泛的应用。如果能更便捷地编写和调试存储过程，将大大提升开发效率。navicat提供了良好的开发环境，比MySQL命令行操作更加便捷。

2.4.1 创建存储过程

右击数据库下的"函数"节点，在弹出的快捷菜单中执行"新建函数"命令选中以上这个即可。

在程序模版中完成存储过程的编写。因为navicat默认指定用户目前创建的是存储过程，所以在navicat中编写存储过程时，不需要再使用delimiter声明新的分隔符。
在默认情况下，系统在新建函数时会自动打开函数向导。该向导通过可视化界面输入存储过程名称、参数列表完成存储过程模版的创建。可以按照以下步骤关闭函数向导:"工具"-"选项"-"常规"-“显示函数向导”复选框；

2.4.2 执行存储过程

存储过程编写完毕后，保存之后，存储过程将自动保存在当前所在数据库的函数节点下。
保存后，单击运行按钮调用存储过程。根据存储过程的定义，有两个输入参数，在打开的"输入参数"对话框中输入设定的用户参数值点击确定按钮，执行存储过程并输出结果。

2.4.3 示例

输入参数

打印结果

navicat提供了可视化的方式创建和执行存储过程，使存储过程的开发和管理变得更加轻松。

2.5 设置用户权限

2.5.1 基本概念

创建的存储模版自动增加DEFINER赋值语句，他作为一个可选项，用于规定对存储过程访问的安全控制。在MySQL中，通过DEFINER和SQL SECURITY特性控制存储过程的执行权限的语法格式如下:

create
[definer={user|current_user}] # 定义definer
procedure 存储过程名
[SQL SECURITY{DEFINER | INVOKER}]| ...] # 特性
begin
......
end

definer默认为当前用户。如果创建者有super权限，也可以指定definer值为其他用户。能否访问该存储过程取决于该用户是否有调用该存储过程的权限(INVOKER权限)，以及是否有存储过程中SQL语句的select权限。

2.5.2 definer特性

在MySQL中，DEFINER 是用于定义存储过程、函数、视图等对象的创建者（或拥有者）的关键字。这在 SQL 安全性方面具有重要意义，特别是在多用户环境中。
当你创建一个存储过程、函数或视图时，MySQL 默认会将当前执行该创建语句的用户作为该对象的创建者，这意味着只有该用户拥有对该对象的执行权限。然而，在某些情况下，你可能希望将对象的执行权限授予其他用户或角色，而不是创建者本身。
这时，你可以使用 DEFINER 关键字指定一个特定的用户或角色作为对象的创建者。这样，无论哪个用户执行该对象，实际上都是以 DEFINER 指定的用户的权限来执行。这有助于在数据库中实现更细粒度的权限控制和安全性。

例如，创建一个存储过程并指定 DEFINER：

CREATE DEFINER = 'user'@'host' PROCEDURE procedure_name()
BEGIN
-- 存储过程内容
END;

例子分析