赞
踩
使用语言 MySQL
使用工具 Navicat Premium 16
代码能力快速提升小方法,看完代码自己敲一遍,十分有用
目录
在数据库开发过程中,存在一个功能需要多个SQL语句组合在一起实现并被多次调用的情况。在数据库中定义存储过程,可以提升代码的复用率,提高开发效率。
存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数的值(如果该存储过程带有参数)来执行。存储过程是数据库中的一个重要对象。MySQL从5.0版本开始支持存储过程,时数据库引擎更加灵活强大。
存储过程就是数据库SQL语言层面上的代码封装与重用。 (存储过程就跟Java中的方法差不多)
存储过程在提高性能、减少网络流量、减少重复代码和增强安全性等方面具有优势,但在学习成本高、数据库依赖性强、调试困难和维护困难等方面存在一些挑战。因此,在选择是否使用存储过程时,需要根据具体的应用场景和需求权衡其优缺点。
存储过程简称过程(Procedure)。在MySQL中,使用 create procedure语句创建存储过程的语法格式如下:
create procedure 过程名([过程参数[,.....]])
[特性]
存储过程体
过程名一般以pro_开头;
其中,特性为可选项,用于调整存储过程的行为。下面对一些常用的特性进行说明:
在MySQL中,默认使用";"作为分隔符,使用DELIMITER关键字可以改变分隔符。在创建存储过程,首先声明分隔符。将分隔符设置为"$$"或"//"的语法格式如下:
如果没有声明分隔符,编译器就会把存储过程当成SQL语句处理,这样编译过程就会报错。由于我使用的版本是Navicat Premium 16,已经优化了这个问题,所以可以不用声明分隔符。注意,最后要把分隔符还原。语法格式如下:
在MySQL中,存储过程的参数包括3种类型。
[in | out | inout] 参数名 数据类型
如果需要定义多个参数,需要使用','进行分隔。
在定义存储过程的过程体时,需要标识开始和结束。语法格式如下:
end后面必须使用delimiter语句中设置的分隔符为结束。
创建存储过程之后,如何进行调用呢?在MySQL中使用call关键字调用存储过程,语法非常简单。语法格式如下:
存储过程调用类似于Java中的方法调用。括号中根据存储过程的定义包含相应的参数。下面看具体示例:
运行结果
这样会打印出三个结果,分别时存储过程中对应的三个语句,相比执行单独的SQL语句,存储过程最大的优势是将一系列SQL语句集合起来,允许使用参数,使开发过程变得更加灵活。
运行结果
这里使用select into将查询获得的数据放入输出参数。调用存储过程时,必须在过程名后面的括号中包含与定义过程相匹配数目的参数,如@a。这里把@a成为用户变量。加两个@@号的就是全局变量(系统变量)。调用过程结束,就可以通过select查看输出结果。
类似Java等其他编程语言,定义存储过程时可以使用使用变量。声明变量的语法格式如下:
例如,声明交易时间变量trade_time,并设置默认值为2020-07-10
也可以分为多行来一个一个定义,意思都是一样的 ;
在定义存储过程时,所有局部变量的声明一定要放在存储过程体的开始,否则会提示语法错误;
声明变量后,可以给变量赋值。语法格式如下:
例如,设置变量total的值为100:
在MySQL中,变量包含用户自定义变量和系统变量两种。这里重点讲用户自定义变量。
MySQL用户自定义变量包括局部变量和会话变量。
运行结果
定义用户变量
运行结果
存储过程为多条SQL语句的集合,在实际项目开发过程中具有广泛的应用。如果能更便捷地编写和调试存储过程,将大大提升开发效率。navicat提供了良好的开发环境,比MySQL命令行操作更加便捷。
右击数据库下的"函数"节点,在弹出的快捷菜单中执行"新建函数"命令选中以上这个即可。
输入参数
打印结果
navicat提供了可视化的方式创建和执行存储过程,使存储过程的开发和管理变得更加轻松。
create
[definer={user|current_user}] # 定义definer
procedure 存储过程名
[SQL SECURITY{DEFINER | INVOKER}]| ...] # 特性
begin
......
end
例如,创建一个存储过程并指定 DEFINER:
CREATE DEFINER = 'user'@'host' PROCEDURE procedure_name()
BEGIN
-- 存储过程内容
END;
例子分析
sql security特性可以指定为definer或invoker,用以指定是在定义者(definer)或调用者(invoker)上下文中执行。若省略sql security特性,则默认值是definer上下文。
definer和invoder决定存储过程不同的执行方式。(查询账号权限可以去mysql数据库中的user表查看(Y代表可以执行,N代表不能执行))
definer属性值
对于存储过程p1,任何对p1具有执行权限的用户都可以使用call语句调用。但需要注意的是,当执行存储过程的时候,'admin'@'localhost用户必须同时拥有p1的执行权限和对表t1的update权限;否则,存储过程将执行失败如上所示。
invoder属性值
这将在 MySQL 中创建一个名为 p2 的存储过程,它将以调用者的权限执行。请确保用户调用该存储过程时具有足够的权限来更新表 t1,否则就会出现异常。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。