未来做哪些网站能致富,没有营业执照网站备案,网站建设公司兴田德润实惠,电力通信网网络架构使用语言 MySQL
使用工具 Navicat Premium 16
代码能力快速提升小方法#xff0c;看完代码自己敲一遍#xff0c;十分有用
拖动表名到查询文件中就可以直接把名字拉进来中括号#xff0c;就代表可写可不写 目录
1.认识存储过程
1.1 存储过程的作用
1.2 存储过程简介…使用语言 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:使用执行者的权限执行。COMMENTstring:存储过程的注释信息。
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 total100
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 userhost 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语句调用。但需要注意的是当执行存储过程的时候adminlocalhost用户必须同时拥有p1的执行权限和对表t1的update权限否则存储过程将执行失败如上所示。
invoder属性值 这将在 MySQL 中创建一个名为 p2 的存储过程它将以调用者的权限执行。请确保用户调用该存储过程时具有足够的权限来更新表 t1否则就会出现异常。
