MySQL中的存储过程详解(上篇)
使用语言 MySQL
使用工具 Navicat Premium 16
代码能力快速提升小方法,看完代码自己敲一遍,十分有用
- 拖动表名到查询文件中就可以直接把名字拉进来
- 中括号,就代表可写可不写
目录
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;
例子分析
- 在这个例子中,存储过程 procedure_name 的创建者被指定为 user 用户,而不是当前执行该语句的用户。
- 需要注意的是,使用 DEFINER 关键字可能存在一些安全风险,特别是当指定的用户拥有高权限时。因此,在使用时需要谨慎考虑,并确保适当的安全措施已经实施,以防止潜在的安全漏洞。
2.5.3 sql security的作用
sql security特性可以指定为definer或invoker,用以指定是在定义者(definer)或调用者(invoker)上下文中执行。若省略sql security特性,则默认值是definer上下文。
definer和invoder决定存储过程不同的执行方式。(查询账号权限可以去mysql数据库中的user表查看(Y代表可以执行,N代表不能执行))
2.5.4 示例
definer属性值
对于存储过程p1,任何对p1具有执行权限的用户都可以使用call语句调用。但需要注意的是,当执行存储过程的时候,'admin'@'localhost用户必须同时拥有p1的执行权限和对表t1的update权限;否则,存储过程将执行失败如上所示。
invoder属性值
这将在 MySQL 中创建一个名为 p2 的存储过程,它将以调用者的权限执行。请确保用户调用该存储过程时具有足够的权限来更新表 t1,否则就会出现异常。
原文地址:https://blog.csdn.net/a15766649633/article/details/137794122
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!