首页 > 数据库 >MySQL变量，流程控制与游标

MySQL变量，流程控制与游标

时间：2023-11-22 13:57:54浏览次数：39  
标签：语句 变量 流程 xld 游标 会话 MySQL MySql

变量，流程控制与游标

1. 变量

在MySql 数据库的存储过程和函数中，可以使用变量来存储查询或计算中间结果数据，和输出最终的结果数据。

在 MySql 数据库中，变量分为系统变量 以及 用户自定义变量。

1.1 系统变量

1.1.1 系统变量分类

变量由系统定义，不是用户定义，属于"服务器"层面。启动 MySql 服务实例期间，MySql 将为 MySql 服务器内存中的系统变量赋值，这些系统变量定义了当前 MySql 服务实例的属性，特征。这些系统变量的值要么是 编译MySql时参数的默认值，要么是 配置文件（my,ini，my.cnf）中的参数值，可以通过一下网址查看 MySql问的系统变量：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

  • 系统变量分为全局系统变量（global 关键字）以及会话系统变量（session 关键字），有时也把全局变量简称为 全局变量，会话变量简称为 local 变量。
  • 如果不写关键字，默认为会话级别变量。
  • 静态变量（在 MySql 服务实例运行期间它们的值不能使用 set 动态修改）属于特殊的全局系统变量

每一个 MySql 客户机成功连接 MySql 服务器后，都会产生与只对应的会话。会话期间，MySql 服务实例会在 MySql 服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。

session会话变量

系统变量作用域说明：

  • 全局系统变量针对于所有会话（连接）有效，但不能跨重启
  • 会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
  • 会话1对某个全局系统变量值进行了修改会导致所有会话中的同一个全局系统变量值的修改。

注意：

  • 在 MySql 中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；
  • 在 MySql 中有些系统变量即可以是全局的又可以是会话的，例如 character_set_client 用于设置客户端的字符集；
  • 在 MySql 中有些系统变量只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySql 连接 ID；

image-20220510154040937

1.1.2 查看系统变量

1.1.2.1 查询所有的系统变量

  • 查看所有全局变量
show global variables;
# 例如
SHOW GLOBAL VARIABLES;
  • 查看所有会话变量
show session variables;
# 例如
SHOW SESSION VARIABLES;
/ 
SHOW VARIABLES; # 不指定关键字默认是 session 会话变量
  • 查看满足条件的（global 全局）系统变量
show global variables like '变量名称';
# 例如
SHOW GLOBAL VARIABLES LIKE '%m%'
  • 查看满足条件的（session 会话）系统变量
show session variables like '变量名称';
# 例如
SHOW SESSION VARIABLES LIKE '%m%';

1.1.2.2 查询指定的系统变量

作为 MySql 编码规范，MySql 中的系统变量以 "两个@@" 开头：

  • "@@global" 仅用于标记全局系统变量。
# 全局系统变量
select @@global.变量名;
# 例如
SELECT @@GLOBAL.admin_port;
  • "@@session" 仅用于标记会话系统变量。
select @@session.变量名;
# 例如
SELECT @@session.pseudo_thread_id; # 只是 session 会话系统变量
SELECT @@session.character_set_client; # 即是 global全局系统变量又是 session会话系统变量 

# 错误 [Err] 1238 - Variable 'admin_port' is a GLOBAL variable; 表示 该变量是全局变量
  • "@@" 首先标记会话系统变量，如果会话系统变量不存在，则自动标记全局系统变量。
select @@变量名;
# 例如
select @@admin_port;
SELECT @@gcharacter_set_client;# 先查询 session会话系统变量，在查询 global全局系统变量

1.1.3 修改系统变量的值

有些时候，数据库管理员需要修改系统变量的默认值，提高使用效率。

修改当前会话或者MySql服务实例的属性，特征等...。具体方法：

  • 方式1：直接修改MySql配置文件（my.ini，my.cnf），继而修改 MySql系统变量的值（该方式需要重启 MySql 服务）

  • 方式2：在MySql服务运行期间，使用"set"命令重新设置系统变量的值（只针对当前的数据库实例有效）

  • 修改 global全局系统变量

# 方式1：
set @@global.变量名 = 变量值;
# 例如 
SET @@global.max_connections = 201; # 修改数据库最大连接为 201 个

# 方式2：
set global 变量名 = 变量值;
# 例如
SET GLOBAL MAX_CONNECTIONS = 202; # 修改数据库最大连接为 202 个

注意：使用“set”命令的方式设置 global全局系统变量，只针对当前的数据库实例有效，一旦重启MySql服务，就失效了。

  • 修改 session会话系统变量
# 方式1：
set @@session.变量名 = 变量值;
# 例如
SET @@session.character_set_client = 'uft8md4'; # 修改当前会话字符集为 uft8md4
# 方式2：
set session 变量名 = 变量值;
# 例如
SET SESSION character_set_client = 'uft8md4'; # 修改当前会话字符集为 uft8md4

注意：使用“set”命令的方式设置 session会话系统变量，只针对于当前会话是有效的，一旦结束会话，重新建立起新的会话，就失效了。

  • 隐式修改系统变量
SET autocommit = FALSE

注意：在不指定系统变量类型的情况下修改系统变量，默认先找 session会话系统变量修改，在找 global全局系统变量修改。

1.2 用户自定义变量

1.2.1 用户自定义变量分类

用户变量是用户自己定义的，作为 MySql 编码规范，MySql 中的用户变量以“一个@”开头。根据作用域范围不同分为 会话用户变量和局部变量。

  • 会话用户变量：作用域和会话变量一样，只对"当前连接"的会话有效。
  • 局部变量：只在 begin 和 end 语句块中有效。局部变量只能在"存储过程和函数"中使用。

1.2.2 会话用户变量

会话用户变量，使用"@"开头。作用域为当前会话

1.2.2.1 变量的定义
  • 方式1："=" 或者 ":="
set @用户变量 = 值;
set @用户变量 := 值;
# 例如
SET @xld = 'xld';
SET @xld_one := 'xld_one'
  • 方式2：":=" 或者 "INTO" 关键字
select @用户变量 := 字段 [from 等语句...]
select 字段 into @用户变量 [from 等语句...]
# 例如
SELECT @xld_id := id FROM xld; # 可以使用多行赋值，默认选择字段结果中的最后一个数据
SELECT person_name INTO @xld_name FROM xld WHERE id = 10
# [Err] 1172 - Result consisted of more than one row 表示结果为多行无法赋值
  1. 注意：在select中使用 ":=" 为用户变量赋值时（一次可以为多个变量赋值），是可以显示select语句中的其他定义的字段的，可以得到结果集。多行赋值，默认选择字段结果中的最后一个数据。（慢慢体会这种特性可以做到什么效果哦O(∩_∩)O~~）
  2. 注意：使用 "INTO" 为用户变量赋值时（一次只能为一个变量赋值），字段结果为多行数据的话，报错无法赋值。
1.2.2.2 查看用户变量的值（查看，比较运算以及函数等...）
select @用户变量名;
# 例如
SELECT CONCAT(@xld,'学习用户变量')

1.2.3 局部变量

局部变量必须，使用declare声明。只能使用在 begin ... edn 中（使用在存储过程，函数中）。且必须声明在 begin 中首行的位置。

使用局部变量的步骤：

  1. 定义变量：在 begin 首行定义局部变量
  2. 变量赋值：使用 set 或者 select 字段 into 变量 [from ...] 的方式
  3. 使用变量：select 查看变量值或者直接在存储过程体和函数体中使用。
1.2.3.1 定义局部变量
declare 变量名称 数据类型 [default 默认值] # 如果没有 default 设置默认值初始值为 null。
# 例如
delimiter $
CREATE PROCEDURE xld_procedure_jububianliang()
BEGIN
DECLARE xld INT DEFAULT 0;
SELECT xld;
END $
delimiter ;
1.2.3.2 局部变量赋值
  • 方式1："=" 或者 ":="
set 变量名 = 值;
# 或
set 变量名 := 值;

# 例如
delimiter $
CREATE PROCEDURE xld_procedure_jububianliang()
BEGIN
DECLARE xld INT DEFAULT 0; # 默认值为 0
SET xld = 1001; # 为变量赋值
SET xld := 1002;# 为变量赋值
SELECT xld;
END $
delimiter ;
  • 方式2： "INTO" 关键字 。 注意：select 语句中只能使用 INTO 的方式为局部变量赋值
select 字段 into 变量名 [from 等语句...]
# 例如
delimiter $
CREATE PROCEDURE xld_procedure_jububianliang(IN xld_id INT)
BEGIN
DECLARE xld_variables INT DEFAULT 0;
SELECT id INTO xld_variables FROM xld WHERE id = xld_id; # 为变量赋值
SELECT xld_variables;
END $
delimiter ;
# [Err] 1172 - Result consisted of more than one row 表示结果为多行无法赋值

注意：使用 "INTO" 为用户变量赋值时（一次只能为一个变量赋值），字段结果为多行数据的话，报错无法赋值。

1.2.3.3 查看局部变量
select 变量名;
# 例如
delimiter $
CREATE PROCEDURE xld_procedure_jububianliang()
BEGIN
DECLARE xld INT DEFAULT 0; # 默认值为 0
SELECT xld; # 查看局部变量
END $
delimiter ;

1.2.3 对比会话用户变量与局部变量

用户变量类型 作用域 定义位置 语法
会话用户变量 当前会话（当前连接） 会话的如何地方 加 @ 符号，不用指定类型
局部变量 定义在 begin ... end 中 begin ... end 的首行 不加 @ 符号，使用 declare 关键字，需要指定数据类型

1.3 系统变量扩展

  • 思考一下 autocommit 是什么类型的系统变量？
答：及是全局系统变量，也是会话系统变量。
  • MyBatis 中的 sqlSession 是不是一次数据库的会话？
答：是的。且sqlSession不是线程安全的。千万不能将SqlSession搞成单例或静态域和实例变量的形式，这些形式都会导致SqlSession出现事务问题
  • 代码中没有添加事务和加事务的情况？

没有事务：在没有加事务的情况下，Mapper的每次请求数据库，都会创建一个SqlSession与数据库交互。（Mapper的每次请求数据库都是一次数据库的会话）

添加事务：在方法中加了事务后，两次或多次Mapper的请求只会创建了一个SqlSession，每次SQL执行完毕都会释放这个SqlSession，并在下次执行SQL时获取这个SqlSession。（一次事务的操作，是一次数据库的会话）

  1. 通过上述的理论，可以推断出MyBatis中的 SqlSession对象会建立一次数据库会话。
  2. 没有事务时，一次Mapper请求一次数据库会话。有事务时，一次事务一次数据库会话。
  3. 在代码中实现事务提交回滚的本质就是，修改了数据库中的系统会话变量（session 系统变量）中的autocommit 为 false。关闭当前会话的自动提交功能。
什么是SqlSession
我们必须得搞明白，什么是SqlSession？

简单来说，SqlSession是MyBatis工作的最顶层API会话接口，所有的数据库操作都经由它来实现，由于它就是一个会话，即一个SqlSession应该仅存活于一个业务请求中，也可以说SqlSession对应这一次数据库会话，它不是永久存货的，每次访问数据库都需要创建它。

因此，SqlSession并不是线程安全，每个线程都应该有它自己的SqlSession实例，千万不能将SqlSession搞成单例或静态域和实例变量的形式，这些形式都会导致SqlSession出现事务问题，这也就是为什么多个请求同一个事务中会共用一个SqlSession会话的原因，我们从SqlSession的创建过程来说明这点。
SqlSession的创建过程:
	1.从Configuration配置类中拿到Environment数据源；
	2.从数据源中获取TransactionFactory和DataSource，并创建一个Transaction连接管理对象；
	3.创建Executor对象（SqlSession只是所有操作的门面，真正要干活的是Executor，它封装了底层JDBC所有的操作细节）；
	4.创建SqlSession会话。
	
每次创建一个SqlSession会话，都会伴随创建一个专属SqlSession的连接管理对象，如果SqlSession共享，就会出现事务问题。

2. 定义条件与处理程序

  • 定义条件：就是事先定义程序执行过程中可能遇到的问题。
  • 处理程序：就是定义了在遇到问题时应当采取的处理方式，并且保存存储过程或函数在遇到警告或错误时能继续。

这个可以增强存储程序处理问题的能力，避免程序异常停止运行。

注意：定义条件和处理程序只能在存储过程，存储函数中使用。

2.1 定义条件

定义条件就是给MySql中的错误码命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个语句可以随后被用在定义处理程序的 DECLARE HANDLER 语句中。

2.1.1 定义条件使用 declare 语句，语法格式如下：

declare 错误名称 condition for 错误码（或错误条件）
# 例如
DECLARE xld_Column_not_null CONDITION FOR 1048; # 使用 MySql_error_code

DECLARE xld_Column_not_null CONDITION FOR  SQLSTATE '23000'; # 使用 sqlstate_value

错误码的说明：

  • MySql_error_code 和 sqlstate_value都可以表示MySql的错误。
  1. MySql_error_code 是数值类型错误代码。
  2. sqlstate_value 是长度为5的字符串类型错误代码。
  • 例如：在error 1418 (HY000)中，1418是MySql_error_code，HY000是sqlstate_value。
  • 例如：在error 1142(24000)中，1142是MySql_error_code，42000是sqlstate_value。

2.1.2 定义处理程序 - （很少用，但必须知道）

概述：可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。

2.1.2.1 定义处理程序时，使用 declare 语句的语法如下：

declare 处理方式 handler for 错误类型 处理语句
# 例如
# 方式1 MySql_error_code
DECLARE EXIT HANDLER FOR 1048 SET @error_msg = '1048错误'; # 使用 MySql_error_code

# 方式2 sqlstate 
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @error_msg = '23000错误'; # 使用 sqlstate

# 方式3 错误名称（即定义条件）
DECLARE EXIT HANDLER FOR SQLSTATE xld_Column_not_null SET @error_msg = 'xld_Column_not_null错误'; # 使用 错误名称

说明：

  • 处理方式：3种 CONTINUE，EXIT，UNDO
    1. continue：表示遇到错误不处理，继续执行
    2. exit：表示遇到错误马上退出。
    3. undo：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作
  • 错误类型：（即定义的条件）
    1. sqlstate '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码
    2. MySQL_error_code：匹配数值类型错误代码。
    3. 错误名称：表示 declare ... condition for 定义的错误条件名称（即定义条件）。
    4. sqlwarning：匹配所有以01开头的sqlstate错误代码。
    5. not found：匹配所有以02开头的sqlstate错误代码。
    6. sqlexception：匹配没有除sqlwarning，not found 之外的sqlstate代码。
  • 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。可以是"set 变量 = 值"这样简单语句，也可以使用"begin ... end"复合的语句。

3. 流程控制

概述：在存储过程和函数中解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程函数中的 SQL 语句的执行顺序（逻辑），是我们完成复杂操作必不可少的一部分。

流程控制结果分为三大类：

  • 顺序结构：程序从上往下依次执行。
  • 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行。
  • 循环结构：程序满足一定条件下，重复执行一组语句。

针对于MySQL 的流程控制语句主要有 3 类。

  • 条件判断语句：if 和 case 语句
  • 循环语句：loop， while 和 repeat 语句
  • 跳转语句：iterate 和 leave 语句

3.1 分支结构之 IF

if 语句的语法结构：

IF 表达式 [and 表达式 或 or 表达式] 
	then 操作
ELSEIF 表达式 [and 表达式 或 or 表达式]  
	then 操作
ELSE 
	操作
END IF;

​ 例如

	IF age <= 10 
			THEN SELECT '少年';
	ELSEIF age <= 20
			THEN SELECT  '青少年';
	ELSEIF age <= 40 
			THEN SELECT '壮年';
	ELSE 
			SELECT '老年';
	END IF;
  • 根据表达式的结果为 true 或者 false 执行相应的语句。其中 elseif和 else 选项是可选的。
  • 不同的表达式对应不同的操作
  • 使用在 begin ... end 中

举例：

# 创建存储过程，根据输入的年龄判断，输入该做的事情，如果未null 的话，显示未出生
delimiter //
CREATE PROCEDURE xld_procedure_age_if(IN age TINYINT)
BEGIN
	DECLARE msg VARCHAR(50);

	IF age<=6 
			THEN SET msg = '幼儿园';
	ELSEIF age <= 12 
			THEN SET msg = '上小学';
	ELSEIF age <= 15
			THEN SET msg = '上初中';
	ELSEIF age <= 18 
			THEN SET msg = '上高中';
	ELSEIF age <= 23
			THEN SET msg = '上大学';
	ELSEIF age <= 30
			THEN SET msg = '结婚上班';
	ELSEIF age <= 50
			THEN SET msg = '为了孩子/生活上班挣钱';
	ELSEIF age <= 80
			THEN SET msg = '养老';
	ELSEIF age IS NULL
			THEN SET msg = '还未出世';
	ELSE
			SET msg = '一把黄土';
	END IF;

	SELECT msg;
 END //
delimiter ;
# 调用存储过程
CALL xld_procedure_age_if(24)

注意：在 else 后面是不需要写 then 关键字的。

3.2 分支结构之 CASE

case 语句的语法结构 -（方式2 类似 switch）：

case 表达式(字段)
when 值 then 操作 (如果是语句，需要加分号)
when 值 then 操作 (如果是语句，需要加分号)
...
else 操作 (如果是语句，需要加分号)
end case (如果放在begin ... end中需要加上case，如果在select则不需要)

​ 例如

	CASE age
	WHEN 6 THEN SET msg = '幼儿园';
	WHEN 12 THEN SET msg = '上小学'; 
	WHEN 15 THEN SET msg = '上初中';
	WHEN 18 THEN SET msg = '上高中';
	WHEN 23 THEN SET msg = '上大学';
	WHEN 30 THEN SET msg = '结婚上班';
	WHEN 50 THEN SET msg = '为了孩子/生活上班挣钱';
	WHEN 80 THEN SET msg = '养老';
	WHEN NULL THEN SET msg = '还未出世';
	ELSE SET msg = '一把黄土';
	END CASE;

case 语句的语法结构 -（方式2 类似 多重 if）：

case 
when 表达式 [and 表达式 或 or 表达式]  then 操作 (如果是语句，需要加分号)
when 表达式 [and 表达式 或 or 表达式]  then 操作 (如果是语句，需要加分号)
...
else 操作 (如果是语句，需要加分号)
end case (如果放在begin ... end中需要加上case，如果在select则不需要)

​ 例如

	CASE 
	WHEN age <=6 THEN SET msg = '幼儿园';
	WHEN age <=12 THEN SET msg = '上小学'; 
	WHEN age <=15 THEN SET msg = '上初中';
	WHEN age <=18 THEN SET msg = '上高中';
	WHEN age <=23 THEN SET msg = '上大学';
	WHEN age <=30 THEN SET msg = '结婚上班';
	WHEN age <=50 THEN SET msg = '为了孩子/生活上班挣钱';
	WHEN age <=80 THEN SET msg = '养老';
	WHEN NULL THEN SET msg = '还未出世';
	ELSE SET msg = '一把黄土';
	END CASE;

举例：

delimiter // 
CREATE PROCEDURE xld_procedure_case(IN age TINYINT)
BEGIN
	# 定义局部变量
	DECLARE msg VARCHAR(50);
	
	CASE 
	WHEN age <=6 THEN SET msg = '幼儿园';
	WHEN age <=12 THEN SET msg = '上小学'; 
	WHEN age <=15 THEN SET msg = '上初中';
	WHEN age <=18 THEN SET msg = '上高中';
	WHEN age <=23 THEN SET msg = '上大学';
	WHEN age <=30 THEN SET msg = '结婚上班';
	WHEN age <=50 THEN SET msg = '为了孩子/生活上班挣钱';
	WHEN age <=80 THEN SET msg = '养老';
	WHEN NULL THEN SET msg = '还未出世';
	ELSE SET msg = '一把黄土';
	END CASE;
	
	# 输出变量值
	SELECT msg;
END //
delimiter ;
# 调用存储过程
CALL xld_procedure_case(20)

注意：在 else 后面是不需要写 then 关键字的。

3.3 循环结构之 LOOP

loop 循环语句用来重复执行某些语句。loop 内的语句一直重复执行直到循环被退出（使用 leave ），跳出循环过程。

loop 语句的语法结构：

[loop_label:] loop
	# 循环体
end loop [loop_label];

​ 例如

delimiter //
CREATE PROCEDURE xld_procedure_loop()
BEGIN
	#定义变量
	DECLARE num TINYINT DEFAULT 0;
	
	loop_xld:LOOP
			IF num > 10 
				THEN LEAVE loop_xld;
			ELSE 
				SET num = num + 1;
			END IF;
	END	LOOP loop_xld;
	
	# 输出变量值
	SELECT num;
END //
delimiter ;
# 调用存储过程
CALL xld_procedure_loop();

其中，loop_label表示 loop 语句的（循环的名称）标注名称，该参数可以省略-（建议不要省略）。

3.4 循环结构之 WHILE

while 语句创建一个带条件判断的循环过程。while 在执行语句时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。

while 语句的语法结构：

[while_label:] while 循环条件 do
	# 循环体
end while [while_label];

​ 例如

delimiter //
CREATE PROCEDURE xld_procedure_while()
BEGIN
	# 定义变量
	DECLARE num TINYINT DEFAULT 0;

	while_xld:WHILE num < 10 DO	
		# 循环体
		SET num = num + 1;
	END WHILE while_xld;

	# 输出变量值
	SELECT num;
END //
delimiter ;
# 调用存储过程
CALL xld_procedure_while();
  • 其中，while_label表示 loop 语句的（循环的名称）标注名称，该参数可以省略-（建议不要省略）。
  • 如果循环条件结构为真，while 语句内的语句群被执行，若循环条件为假，退出循环。

3.5 循环结构之 REPEAT

repeat 语句创建一个带条件判断的循环过程。与 while 循环不同的是，repeat 循环首先会执行一次，然后在 until 中进行表达式的判断，如果满足条件就退出；

如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

repeat 语句的语法结构：

[repeat_label:] repeat
	# 循环体
until 结束循环的表达式 [and 表达式 或 or 表达式]  
end repeat [repeat_lamel];

​ 例如

delimiter //
CREATE PROCEDURE xld_procedure_repeat()
BEGIN
	# 定义变量
	DECLARE num TINYINT DEFAULT 0;

	repeat_xld:REPEAT
	SET num = num + 1;
	UNTIL num > 10
	END REPEAT repeat_xld;
	
	# 输出变量值
	SELECT num;
END //
delimiter ;
# 调用存储过程
CALL xld_procedure_repeat()
  • 其中，repeat_label表示 loop 语句的（循环的名称）标注名称，该参数可以省略-（建议不要省略）。
  • 注意：repeat 循环不管如何，只要被调用，都会执行一次。

3.6 三种循环结构对比

  1. 这个三种循环都可以省略名称，但如果想在循环中使用循环控制语句（leave 或 iterate）则必须添加（循环的名称）标注名称。

  2. 循环的特性：

    • loop：一般用于实现简单的"死"循环。
  • while：先判断后执行。
    • repeat：先执行一次，再判断。无条件下至少执行一次。

3.7 跳转结构之 LEAVE

leave 语句：可以用在循环语句中，或者使用在 begin ... end 程序体内（结束程序）。表示跳出循环或者跳出程序体的操作。

基本格式如下：

leave 标记名;

​ 例如

leave loop_xld;

begin ... end 程序中使用 leave：

delimiter //
CREATE PROCEDURE xld_procedure_leave(IN num TINYINT)
begin_xld:BEGIN
	IF num = 0
			THEN LEAVE begin_xld; # 结束程序
	ELSEIF num = 1
			THEN SELECT num;
	ELSE
			SELECT num;
	END IF;
END //
delimiter ;

3.8 跳转结构之 ITERATE

iterate 语句：只能用在循环语句中，表示结束本次循环，进入下一次的循环。

基本格式如下：

iterate 标记名;

​ 例如

iterate while_xld;

试题：下方代码 select 输出多少次？

delimiter //
CREATE PROCEDURE xld_procedure_iterate(IN count TINYINT)
BEGIN
	# 定义变量
	DECLARE num TINYINT DEFAULT 1;

	while_xld:WHILE TRUE DO # 千万注意：在任何循环中必须都设置一个结束循环的条件
		SET num = num + 1;
		IF num < 10
				THEN ITERATE while_xld;
		ELSEIF num > 15 
				THEN LEAVE while_xld;
		END IF;

		SELECT '小林当在学习流程控制之跳转语句中的iterate' msg;
		SET count = count + 1;
		END WHILE while_xld;
		SELECT count;
END //
delimiter ;
# 调用存储过程
CALL xld_procedure_iterate(0);

4 游标

4.1 什么是游标（或光标）

虽然我们也可以通过筛选条件 where 和 having，或者是限定返回记录的关键字 limit 返回一条记录，但是，却 无法在结果集中像指针一样，定位到某一条数据，并对记录的数据进行处理。

这个时候，就可以使用游标了。游标可以让我们对结果集中的每一条记录进行定位，并对定位到的数据进行操作的数据结构。面向(结果集)过程开发。

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里 游标充当了指针的作用，可以通过操作游标来对数据行进行操作。

MySQL 中游标可以在存储过 程和函数中使用。

4.2 使用游标（步骤）

游标必须在 处理程序之前声明，且还必须在 变量和条件之后声明。

使用游标一般有以下 四个步骤。不同的 DBMS 中，使用游标的语法可能略有不同。

  1. 第一步：声明游标

在MySql 中，使用 declare 关键字来说声明，语法如下：

declare 游标名称 cursor for select 语句;
# 例如
DECLARE xld_cursor CURSOR FOR select 语句;

注意：该语法使用大多数数据库，但 oracle 却是 cursor is。

  1. 第二步：打开游标

当我们定义好游标之后，如果想 要使用游标，必须先打开游标。打开游标的时候 select 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

语法如下:

open 游标名称;
# 例如
OPEN xld_cursor;
  1. 第三步：使用游标（从游标中取得数据）

语法如下：

fetch 游标名称 into var_name [,...,var_name]
# 例如
FETCH xld_cursor INTO xld_name;

该语句的作用是：读取指定的 "该游标名称" 的游标中当前行的数据，并且将数据依次保存到 var_name... 变量中，随后游标指针到下一行。如果游标读取的 "数据行" 有多个列名，则在 INTO 关键字后面赋值给 "多个变量名" 即可。

  • 注意：var_name（变量）必须在声明游标之前声明（定义）好

  • 注意：游标的查询结果集中的 "字段数"，必须跟 INTO 后面的 "变量数一致"，否则，在存储过程执行的时候，MySQL 会提示错误。

  1. 第四步：关闭游标

语法如下：

close 游标名称;
# 例如
CLOSE xld_cursor;

有 opne 就会有 close ，也就是打开个关闭游标。当使用完游标后需要关闭该游标。因为游标会 占用系统资源，如果 不及时关闭，游标会一直保持到存储过程结束，影响系统运行效率。

举例：

delimiter //
CREATE PROCEDURE xld_procedure_cursor(OUT xld_count TINYINT)
BEGIN
# 声明变量 
DECLARE xld_name VARCHAR(50); # 名称
DECLARE xld_num TINYINT DEFAULT 0; # 循环变量
DECLARE xld_count_while TINYINT; # 总数变量

# 声明游标
DECLARE xld_cursor CURSOR FOR SELECT person_name FROM xld;

# 变量赋值
SELECT COUNT(*) INTO xld_count_while FROM xld;

# 处理程序
OPEN xld_cursor; # 打开游标

# 结合循环使用游标 while_xld 循环体名称
while_xld:WHILE xld_num < xld_count_while DO
# 使用游标
FETCH xld_cursor INTO xld_name;

SET xld_num = xld_num + 1;
SELECT xld_name;
END WHILE while_xld;

CLOSE xld_cursor; # 关闭游标
SET xld_count = xld_num;

END //
delimiter ;

# 调用存储过程
CALL xld_procedure_cursor(@xld_numm)
SELECT @xld_numm

4.3 小结

  • 游标是 MySql 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。
  • 但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，因为游标是在系统内存中进行的处理。
  • 建议：有开必关。

补充：MySql 8.0 的新特性-全局变量的持久化

set global 命令

在MySql 数据库中，全局变量可以通过 set global 语句来设置。例如：设置服务器语句超时的限制（max_execution_time）

SET GLOBAL max_execution_time = 2000;

使用 set global 语句设置的变量值只会临时生效。数据库重启后，会恢复MySql 的默认配置

set persist 命令 - 系统变量持久化

MySql 8.0版本新增了 set persist 命令。例如：设置数据库的最大连接数（max_connections）

SET persist max_connections = 203; # 持久化配置

MySql 会将该命令的配置保存到数据目录下的 mtsqld_auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

标签：语句,变量,流程,xld,游标,会话,MySQL,MySql
From： https://www.cnblogs.com/xiaolindang/p/17848844.html

相关文章

  • MySQL触发器
    触发器1.触发器概述MySql从5.0.2版本开始支持触发器。MySql的触发器和存储过程一样，都是嵌入到MySql服务器的一段程序。触发器是由事件来触发某个操作，这些事件包括insert，update，delete事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些......
  • MySQL存储过程
    存储过程1.存储过程概述概述：存储过程的英文是"storedprocedure"。它的思想很简单，"就是一组经过"预先编译"的SQL语句的封装"。执行过程：存储过程预先存储在MySql服务器上，需要执行的时候，"客户端只需要向服务器端发送调用存储过程的命令"，服务端就可以把预先存储好的这一系列......
  • MySQL视图
    视图（view）1.常见的数据库对象对象描述表（table）表是存储数据的单元，以行和列的形式存在，列就是字段，行就是记录数据字典（系统表）就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只能查看约束（constraint）执行数据校验的规则，......
  • mysql8下载安装及配置
    一、下载官网地址：https://dev.mysql.com/downloads/mysql/8.0.html选择“downloads”-->"mysqlcommunityserver",如下图所示：向下滑动页面，找到你电脑适配的版本，点击“download”，如下图：页面跳转，不需要注册和登陆，点击“Nothanks,juststartmydownload.......
  • mysql 处理 json 字段
    1.查看包含json字段的表信息mysql>desctab_json;+-------+------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+------------+------+-----+---------+----------------+|id|bigint(20)......
  • java类转mysql表创建语句
    packagecn.eangaie.cloud.wx3562;importcn.hutool.core.io.FileUtil;importcn.hutool.core.lang.Console;importcom.baomidou.mybatisplus.annotation.TableId;importcom.baomidou.mybatisplus.annotation.TableName;importio.swagger.annotations.ApiModelProper......
  • MySQL-重置主键自动递增值-从头开始
    如果你想要重置MySQL数据库中的AUTO_INCREMENT值并让它从头开始增加新的ID，请按照以下步骤进行操作：首先，确定你要修改的表格名和要修改的字段名。例如，假设你有一个名为“users”的表格，并且该表格中的主键字段名为“id”。然后，执行以下SQL命令来更改该字段的当前自动递增计数器：ALT......
  • mysql无法登陆，报错ERROR 1045 (28000): Access denied for user 'root'@'localhost' (
    问题描述在使用命令行登录MySQL时出现了下述问题： 出错原因usingpassword:NO：表示输入没有输入密码就尝试登陆了usingpassword:YES：表示输入了密码，但密码错误 解决方案：修改密码1.修改mysql配置文件my.cnf。在 [mysqld]增加skip-grant-tables 无密码进入mys......
  • 【MySQL】配置文件
    #Disablingsymbolic-linksisrecommendedtopreventassortedsecurityrisks#symbolic-links=0#Settingsuserandgroupareignoredwhensystemdisused.#Ifyouneedtorunmysqldunderadifferentuserorgroup,#customizeyoursystemdunitfilefor......
  • MySql 中 DATEDIFF() 用法
    DATEDIFF函数用于计算两个日期之间的差值，并以天数返回结果。它的语法如下：DATEDIFF(end_date,start_date)其中，end_date和start_date是要计算差值的结束日期和起始日期。以下是一个示例，计算两个日期之间的天数差值：SELECTDATEDIFF('2023-11-21','2023-11-01')ASdays_di......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99