变量、流程控制、游标
变量
1、在 MySQL 数据库的存储过程和函数中,使用变量来存储查询或计算的中间结果数据,或者输出最终的结果数据
2、分类:系统变量、用户自定义变量
系统变量
1、变量由系统定义,不是用户定义,属于服务器层面
2、启动 MySQL 服务,生成 MySQL 服务实例期间,MySQL 将为 MySQL 服务器内存中的系统变量赋值,这些系统变量定义当前 MySQL 服务实例的属性、特征
(1)编译 MySQL 时参数的默认值
(2)配置文件 (如 my.ini 等)中的参数值
3、分类
(1)全局系统变量 / 全局变量:需要添加 global 关键字
(2)会话系统变量 / local 变量:需要添加 session 关键字
4、默认会话级别
5、静态变量:在 MySQL 服务实例运行期间,其值不能使用 set 动态修改,属于特殊的全局系统变量
6、每个 MySQL 客户端成功连接 MySQL 服务器后,都会产生与之对应的会话
(1)会话期间,MySQL 服务实例会在 MySQL 服务器内存中,生成与该会话对应的会话系统变量
(2)这些会话系统变量的初始值,是全局系统变量值的复制
(3)全局系统变量针对于所有会话(连接)有效,但不能跨重启
(4)会话系统变量仅针对于当前会话(连接)有效,会话期间,当前会话对某个会话系统变量值的修改,不会影响其他会话同一个会话系统变量的值
(5)会话 1 对某个全局系统变量值的修改,会导致会话 2 中同一个全局系统变量值的修改
7、范围
(1)有些系统变量只能是全局的,例如:max_connections 用于限制服务器的最大连接数
(2)有些系统变量作用域,既可以是全局又可以是会话,例如:character_set_client 用于设置客户端的字符集
(3)有些系统变量的作用域只能是当前会话,例如:pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID
查看系统变量
1、查看所有系统变量
(1)查看所有全局变量
SHOW GLOBAL VARIABLES;
(2)查看所有会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;
2、查看部分系统变量
(1)查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%标识符%';
(2)查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
3、查看指定系统变量
(1)作为 MySQL 编码规范,MySQL 中的系统变量以两个 @ 开头
(2)@@global:仅用于标记全局系统变量
(3)@@session:仅用于标记会话系统变量
(4)@@:首先标记会话系统变量,如果会话系统变量不存在,则标记全局系统变量
(5)查看指定的系统变量的值
SELECT @@global.变量名;
(6)查看指定的会话变量的值
SELECT @@session.变量名;
SELECT @@变量名;
修改系统变量的值
1、方式一:修改 MySQL 配置文件 ,进而修改 MySQL 系统变量的值(该方法需要重启 MySQL 服务)
2、方式二:在 MySQL 服务运行期间,使用 SET 命令重新设置系统变量的值
3、为某个系统变量赋值
#方式一
SET @@global.变量名=变量值;
#方式二
SET GLOBAL 变量名=变量值;
4、为某个会话变量赋值
#方式一
SET @@session.变量名=变量值;
#方式二
SET SESSION 变量名=变量值;
用户变量
1、由用户自定义
2、作为 MySQL 编码规范,MySQL 中的用户变量以一个 @ 开头
3、根据作用范围不同
(1)会话用户变量:作用域和会话变量一样,只对当前连接会话有效
(2)局部变量:只在 BEGIN 和 END 语句块中有效,局部变量只能在存储过程和函数中使用
会话用户变量
1、定义
(1)方式一:= 或 :=
SET @用户变量=值;
SET @用户变量:=值;
(2)方式二:INTO 或 :=
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];
2、查看用户变量的值 (查看、比较、运算等)
SELECT @用户变量;
(1)查看某个未声明的变量时,将得到 NULL 值
局部变量
1、定义:使用 DECLARE 语句定义一个局部变量
2、作用域:仅仅在定义它的 BEGIN ... END 中有效
3、位置:只能放在 BEGIN ... END 中,且只能放在第一句
4、定义变量
DECLARE 变量名 类型 [DEFAULT 值];
(1)如果没有 DEFAULT 子句,初始值为 NULL
5、变量赋值
(1)方式一:一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
(2)方式二:一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;
3、使用变量(查看、比较、运算等)
SELECT 局部变量名;
4、对比
用户变量 | 作用域 | 定义位置 | 语法 |
会话用户变量 | 当前会话 | 会话的任何地方 | 加 @ 符号,不用指定类型 |
局部变量 | 定义它的 BEGIN END 中 | BEGIN END 第一句话 | 一般不用加 @,需要指定类型 |
定义条件
1、事先定义程序执行过程中可能遇到的问题
2、存储过程、存储函数中都是支持定义条件
3、声明
DECLARE 错误名称 CONDITION FOR 错误码(或错误条件);
(1)给 MySQL 中的错误码命名,这有助于存储的程序代码更清晰
(2)将一个错误名字和指定的错误条件关联起来,该名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中
4、错误码
(1)MySQL_error_code:数值类型错误代码
DECLARE 错误名称 CONDITION FOR 错误码;
(2)sqlstate_value:长度为 5 的字符串类型错误代码
DECLARE 错误名称 CONDITION FOR SQLSTATE '5位字符串';
处理程序
1、定义在遇到问题时,应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时,能继续执行,可以增强存储程序处理问题的能力,避免程序异常停止运行
2、存储过程、存储函数中都是支持处理程序
3、定义处理程序
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;
4、处理方式
(1)CONTINUE :表示遇到错误不处理,继续执行
(2)EXIT :表示遇到错误马上退出
(3)UNDO :表示遇到错误后撤回之前的操作,MySQL 中暂时不支持
5、错误类型(即条件)
(1)SQLSTATE '字符串错误码':表示长度为 5 的 sqlstate_value 类型的错误代码
(2)MySQL_error_code:匹配数值类型错误代码
(3)错误名称:表示 DECLARE ... CONDITION 定义的错误条件名称
(4)SQLWARNING:匹配所有以 01 开头的 SQLSTATE 错误代码
(5)NOT FOUND:匹配所有以 02 开头的 SQLSTATE 错误代码
(6)SQLEXCEPTION:匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
6、处理语句
(1)如果出现上述条件之一,则采用对应的处理方式,并执行指定的处理语句
(2)语句可以是 SET 变量=值,也可以是 BEGIN ... END 编写的复合语句
7、定义方式(例)
(1)捕获 sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';
(2)捕获 mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
(3)先定义条件,再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';
(4)使用 SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';
(5)使用 NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';
(6)使用 SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';
流程控制
1、流程分类
(1)顺序结构:程序从上往下依次执行
(2)分支结构:程序按条件进行选择执行,从两条或多条路径中选择一条执行
(3)循环结构:程序满足一定条件下,重复执行一组语句
2、针对 MySQL 流程控制语句主要有 3 类,只能用于存储程序
(1)条件判断语句:IF、CASE
(2)循环语句:LOOP、WHILE、REPEAT
(3)跳转语句:ITERATE、LEAVE
分支结构
1、IF
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF;
(1)根据表达式的结果为 TRUE 或 FALSE 执行相应的语句
(2)[] 中的内容是可选
2、CASE
(1)如果是语句,需要加分号
(2)如果是放在 BEGIN END 中需要加上 CASE,如果放在 SELECT 后面不需要
(3)情况一:类似 switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1
WHEN 值2 THEN 结果2或语句2
...
ELSE 结果n或语句n
END [CASE];
(4)情况二:类似多重 if
CASE
WHEN 条件1 THEN 结果1或语句1
WHEN 条件2 THEN 结果2或语句2
...
ELSE 结果n或语句n
END [CASE];
循环结构
1、LOOP
(1)LOOP 循环语句用来重复执行某些语句
(2)使用 LEAVE 子句,跳出循环过程
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label];
(3)loop_label 表示 LOOP 语句的标注名称,该参数可以省略
2、WHILE
(1)创建一个带条件判断的循环过程
(2)WHILE 在执行语句执行时,先对指定的表达式进行判断,如果为真,就执行循环内的语句,否则退出循环
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
(3)while_label 为 WHILE 语句的标注名称,该参数可以省略
3、REPEAT
(1)创建一个带条件判断的循环过程
(2)与 WHILE 循环不同的是,REPEAT 循环首先会执行一次循环,然后在 UNTIL 中进行表达式的判断,如果满足条件就退出,即 END REPEAT;如果条件不满足,则会就继续执行循环,直到满足退出条件为止
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];
(3)repeat_label 为 REPEAT 语句的标注名称,该参数可以省略
4、对比
(1)三种循环都可以省略名称,但循环中添加循环控制语句(LEAVE 或 ITERATE),则必须添加名称
(2)LOOP:一般用于实现简单死循环
(3)WHILE:先判断后执行
(4)REPEAT:先执行后判断,无条件、至少执行一次
跳转语句
1、LEAVE
(1)可以用在循环语句内,或者以 BEGIN 和 END 包裹起来的程序体内,表示跳出循环或者跳出程序体的操作
(2)可以把 LEAVE 理解为 break
LEAVE label;
(3)label 参数表示循环的标志
(4)LEAVE 和 BEGIN ... END 或循环一起被使用
2、ITERATE
(1)只能用在循环语句:LOOP、REPEAT、WHILE 内
(2)表示重新开始循环,将执行顺序转到语句段开头处
(3)可以把 ITERATE 理解为 continue
ITERATE label;
(4)label 参数表示循环的标志,ITERATE 语句必须跟在循环标志前
游标 / 光标
1、能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构
2、游标让 SQL 这种面向集合的语言有面向过程开发的能力
3、在 SQL 中,游标是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针
4、游标充当指针的作用,可以通过操作游标来对数据行进行操作
5、MySQL 中游标可以在存储过程和函数中使用
使用游标
1、声明游标
(1)MySQL,SQL Server,DB2、MariaDB
DECLARE 游标名 CURSOR FOR SELECT语句;
(2)Oracle、PostgreSQL
DECLARE 游标名 CURSOR IS SELECT语句;
(3)SELECT 语句返回一个用于创建游标的结果集
2、打开游标
(1)SELECT 语句的查询结集送到游标工作区,为后面游标的逐条读取结果集中的记录做准备
OPEN 游标名;
3、使用游标:从游标中取得数据
FETCH 游标名 INTO var_name [, var_name] ...
(1)使用游标来读取当前行,并且将数据保存到 var_name 这个变量中,游标指针指到下一行
(2)如果游标读取的数据行有多个列名,则在 INTO 关键字后面赋值给多个变量名即可
(3)var_name 必须在声明游标之前就定义好
(4)游标的查询结果集中的字段数,必须跟 INTO 后面的变量数一致,否则,在存储过程执行的时候,MySQL 会提示错误
4、关闭游标
CLOSE 游标名;
(1)使用完游标后需要关闭掉该游标,因为游标会占用系统资源,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率
(2)关闭游标之后,不能再检索查询结果中的数据行,如果需要检索只能再次打开游标
5、优点
(1)逐条读取 结果集中的数据
(2)与应用层面实现相同的功能相比,游标可以在存储程序中使用,效率高,程序简洁
6、缺点
(1)游标是在内存中进行的处理,使用游标的过程中,会对数据行进行加锁
(2)在业务并发量大时,影响业务之间的效率,消耗系统资源,造成内存不足
MySQL 8.0 新特性:全局变量的持久化
1、使用 SET GLOBAL 设置的变量值只会临时生效,数据库重启后,服务器又从 MySQL 配置文件中读取变量的默认值
2、MySQL 8.0 版本新增 SET PERSIST 命令
(1)MySQL 会将该命令的配置,保存到数据目录下 mysqld-auto.cnf 文件中
(2)下次启动时,读取该文件,用其中的配置来覆盖默认的配置文件
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战