变量、流程控制、游标

变量

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 表达式
WHEN1 THEN 结果1或语句1
WHEN2 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)下次启动时,读取该文件,用其中的配置来覆盖默认的配置文件

posted @   半条咸鱼  阅读(46)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示