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

使用语言 MySQL

使用工具 Navicat Premium 16

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

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

目录

1.认识存储过程

1.1 存储过程的作用 

1.2 存储过程简介

1.3 MySQL存储过程的优缺点

1.3.1 优点

1.3.2 缺点

2.创建和调用存储过程

2.1 创建存储过程 

2.1.1 常用特性

2.1.2 声明默认语句分隔符 

2.1.3 参数的3种类型 

2.1.4 过程体的标识 

2.2 调用存储过程

2.2.1 无参示例

2.2.2 带参示例 

2.3 存储过程中的变量

2.3.1 一行定义多个变量 

2.3.2 变量赋值

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

2.3.4 示例 

2.4 使用navicat创建存储过程 

2.4.1 创建存储过程

2.4.2 执行存储过程 

2.4.3 示例 

2.5 设置用户权限

2.5.1 基本概念 

2.5.2 definer特性 

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; 

例子分析

• 在这个例子中，存储过程 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，否则就会出现异常。