变量、流程控制与游标

作者：FL

博客：https://www.cnblogs.com/flblogs/

• 注意：本笔记部分参考尚硅谷-宋红康

1. 变量

MySQL中的变量是一种可以在SQL语句中动态存储和传递数据的机制。与其他编程语言类似，在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。

常量：

1. 字符串常量

2. 数值常量

3. 时间日期常量

用户定义变量

用户定义变量使用两个 @ 符号开头，在执行完该语句后存活，只能在当前会话中使用。下面是一个示例：

SET @var1 = 'Hello';
SET @var2 = 5;
SELECT @var1, @var2+10;

以上代码设置了两个变量 @var1 和 @var2，在 SELECT 查询语句中使用这些变量做了简单的计算和输出操作。

系统变量

系统变量由MySQL服务器维护，在全局范围内使用，通常可以通过 SET 或 SHOW 命令来修改或查询其值。例如，我们可以通过以下命令获取 MySQL 的版本信息：

SHOW VARIABLES LIKE 'version%';

以上命令会查询数据库中以“version”开头的所有系统变量，并将它们的名称以及对应的值输出。这样就可以方便地获取MySQL的版本信息。

注意:

在使用变量时，需要遵循MySQL的变量命名规则，比如变量名必须唯一、不能包含特殊字符等等。此外，在某些MySQL版本中还要求变量名必须小写。

流程控制

分支结构之 IF

• 作用：用于存储过程与函数的分支选择的判断

• IF 语句的语法结构是：

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

根据表达式的结果为TRUE或FALSE执行相应的语句。这里“[]”中的内容是可选的。

• 特点：

  • 不同的表达式对应不同的操作
  • 使用在begin end中
  • 可以嵌套

  IF val IS NULL 
  	THEN SELECT 'val is null';
  ELSE SELECT 'val is not null';
  
  END IF;
  

案例：

-- 使用IF和CASE语句分别完成对customer表的余额 menber＿balance 进行判 断，如果余额大于5000，则输出“余额高”；如果余额大于2000，则输出“余额中等”；否则输出“余额偏低”。
SELECT * FROM customer;
-- if
DELIMITER $$

CREATE PROCEDURE p_menber_balance_if(IN sid INT)
BEGIN
	DECLARE balance DOUBLE;
	SELECT menber_balance INTO balance FROM customer WHERE id=sid ;
IF balance > 5000 THEN
    SELECT balance,'余额高' AS '级别';
ELSEIF balance > 2000 THEN
    SELECT balance,'余额中等' AS '级别';
ELSE
    SELECT balance,'余额偏低' AS '级别';
END IF;
END $$

DELIMITER ;

CALL p_menber_balance_if(6);

分支结构之CASE

CASE 语句的语法结构1：

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

CASE 语句的语法结构2：

#情况二：类似于多重if
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

• 举例1：

使用CASE流程控制语句的第1种格式，判断val值等于1、等于2，或者两者都不等。

CASE val
　　　WHEN 1 THEN SELECT 'val is 1';
　　　WHEN 2 THEN SELECT 'val is 2';
　　　ELSE SELECT 'val is not 1 or 2';
END CASE;

• 举例2：

使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0。

CASE
	WHEN val IS NULL THEN SELECT 'val is null';
	WHEN val < 0 THEN SELECT 'val is less than 0';
	WHEN val > 0 THEN SELECT 'val is greater than 0';
	ELSE SELECT 'val is 0';
END CASE;

• 举例3:
  使用IF和CASE语句分别完成对customer表的余额 menber＿balance 进行判 断，如果余额大于5000，则输出“余额高”；如果余额大于2000，则输出“余额中等”；否则输出“余额偏低”。

DELIMITER $$

CREATE PROCEDURE p_menber_balance_case(IN sid INT)
BEGIN
	DECLARE balance DOUBLE;
	SELECT menber_balance INTO balance FROM customer WHERE id=sid ;
CASE 
WHEN balance > 5000 THEN
    SELECT balance,'余额高' AS '级别';

WHEN balance > 2000 THEN
    SELECT balance,'余额中等' AS '级别';

ELSE
    SELECT balance,'余额偏低' AS '级别';
END CASE;
END $$

DELIMITER ;

CALL p_menber_balance_case(6);

循环结构之LOOP

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

LOOP语句的基本格式如下：

[loop_label:] LOOP
	循环执行的语句
END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略。

举例1：

使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。

循环结构之WHILE

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

[while_label:] WHILE 循环条件  DO
	循环体
END WHILE [while_label];

while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环。

循环结构之REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

REPEAT语句的基本格式如下：

[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

对比三种循环结构：

1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
2、
LOOP：一般用于实现简单的"死"循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次

跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

基本格式如下：

LEAVE 标记名

其中，label参数表示循环的标志。LEAVE和BEGIN ... END或循环一起被使用。

举例1：创建存储过程 “leave_begin()”，声明INT类型的IN参数num。给BEGIN...END加标记名，并在BEGIN...END中使用IF语句判断num参数的值。

• 如果num<=0，则使用LEAVE语句退出BEGIN...END；
• 如果num=1，则查询“employees”表的平均薪资；
• 如果num=2，则查询“employees”表的最低薪资；
• 如果num>2，则查询“employees”表的最高薪资。

IF语句结束后查询“employees”表的总人数。

DELIMITER //

CREATE PROCEDURE leave_begin(IN num INT)

	begin_label: BEGIN
		IF num<=0 
			THEN LEAVE begin_label;
		ELSEIF num=1 
			THEN SELECT AVG(salary) FROM employees;
		ELSEIF num=2 
			THEN SELECT MIN(salary) FROM employees;
		ELSE 
			SELECT MAX(salary) FROM employees;
		END IF;
		
		SELECT COUNT(*) FROM employees;
	END //


DELIMITER ;

跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

语句基本格式如下：

ITERATE label

label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

举例： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。

• 如果num < 10，则继续执行循环；
• 如果num > 15，则退出循环结构；

DELIMITER //

CREATE PROCEDURE test_iterate()

BEGIN
	DECLARE num INT DEFAULT 0;
	
	my_loop:LOOP
		SET num = num + 1;
	
		IF num < 10 
			THEN ITERATE my_loop;
		ELSEIF num > 15 
			THEN LEAVE my_loop;
		END IF;
	
		SELECT '第'+num;
	
	END LOOP my_loop;

END //

DELIMITER ;

总结：

游标

引入

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

这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

介绍

• 游标（Cursor）是一种数据库对象，它允许程序对结果集进行遍历和操作。在MySQL中，游标可以用于存储过程和函数中，以便在处理结果集时进行更精细的控制。

• 游标可以用于以下情况：
  1. 遍历结果集：游标可以让程序逐行遍历结果集，并对每一行进行操作。
  2. 逐行处理数据：游标可以让程序对每一行数据进行特定的操作，例如计算总和、平均值等。
  3. 处理复杂查询：当查询语句比较复杂时，游标可以让程序更好地控制结果集，以便进行更复杂的操作。

• 在MySQL中，游标的使用需要注意以下几点：
  1. 游标必须在存储过程或函数中声明，并且必须在使用前打开。
  2. 游标必须在使用后关闭，并且必须释放相关资源。
  3. 游标只能用于SELECT语句，不能用于INSERT、UPDATE或DELETE语句。
  4. 游标的使用可能会影响性能，因此应该谨慎使用。

游标相关操作

第一步，声明游标

• 在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement; 

• 要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，这里 select_statement 代表的是 SELECT 语句，返回一个用于创建游标的结果集。

第二步，打开游标

• 打开游标的语法如下：

OPEN cursor_name

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

第三步，使用游标（从游标中取得数据）

• 语法如下：

FETCH cursor_name INTO var_name [, var_name] ...

• 这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

FETCH　cur_emp INTO emp_id, emp_sal ;

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

第四步，关闭游标

CLOSE cursor_name

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

关闭游标之后，我们就不能再检索查询结果中的数据行，如果需要检索只能再次打开游标。

案例：

-- 使用游标创建一个存储过程，统计商品单价大于100的记录的数量
SELECT * FROM goods;
DELIMITER //

CREATE PROCEDURE count_expensive_goods()
BEGIN
	-- 创建 用于接收游标值的变量
	DECLARE u_price DOUBLE DEFAULT 0;# 定义临时变量
  DECLARE done INT DEFAULT FALSE;# 定义退出条件标识符
  DECLARE count INT DEFAULT 0;# 定义计数
  -- 声明游标
  DECLARE cur CURSOR FOR SELECT unit_price FROM goods WHERE unit_price > 100;
  -- 定义处理方式
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打开游标
  OPEN cur;
-- 在循环中使用游标
  read_loop: LOOP
    FETCH cur INTO u_price; # 需要一个临时变量存储游标指向的值
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET count = count + 1;
  END LOOP;
-- 关闭游标
  CLOSE cur;

  SELECT count;
END//

DELIMITER ;

CALL count_expensive_goods();

异常处理

介绍

• 在mysql中，通过定义条件和处理程序对异常进行处理
• 定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。
• 说明：定义条件和处理程序在存储过程、存储函数中都是支持的。

定义条件

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

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

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

错误码的说明：
如下图，推荐使用终端或百度查看完整错误码。

• MySQL_error_code和sqlstate_value都可以表示MySQL的错误。
  • MySQL_error_code是数值类型错误代码。
  • sqlstate_value是长度为5的字符串类型错误代码。（图像界面可能没有）

例：

-- 错误处理

INSERT INTO goods (id, goods_name, supplier_id, goods_type, banner, introduce, unit_price, amount, goods_memo)
    VALUES (22,'农夫山泉', 1, '饮用水', 'banner.jpg', 'Product A description', 2.5, 100, 'Memo'); # 插入id，主键重复错误
		
-- 定义条件
# 格式：DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
-- 方式一：错误码
DECLARE Duplicate_Entry CONDITION FOR 1062;
-- 方式二：错误条件
DECLARE Duplicate_Entry CONDITION FOR SQLSTATE'23000';

定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

• 处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
  • CONTINUE：表示遇到错误不处理，继续执行。
  • EXIT：表示遇到错误马上退出。
  • UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
• 错误类型（即条件）可以有如下取值：
  • SQLSTATE '字符串错误码'：表示长度为5的sqlstate_value类型的错误代码；
  • MySQL_error_code：匹配数值类型错误代码；
  • 错误名称：表示DECLARE ... CONDITION定义的错误条件名称。
  • SQLWARNING：匹配所有以01开头的SQLSTATE错误代码；
  • NOT FOUND：匹配所有以02开头的SQLSTATE错误代码；
  • SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
• 处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“SET 变量 = 值”这样的简单语句，也可以是使用BEGIN ... END编写的复合语句。

#方法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';

例：

-- 定义条件
# 格式：DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
-- 方式一：错误码
DECLARE Duplicate_Entry CONDITION FOR 1062;
-- 方式二：错误条件
DECLARE Duplicate_Entry CONDITION FOR SQLSTATE'23000';

-- 创建一个通过id插入商品的存储过程，如果重复就不加入，返回-1并退出
DROP PROCEDURE IF EXISTS insert_good_by_id;
DELIMITER $
CREATE PROCEDURE insert_good_by_id(
    IN p_id INT(11),
    IN p_goods_name VARCHAR(50),
    IN p_supplier_id INT(11),
    IN p_goods_type CHAR(20),
    IN p_banner VARCHAR(255),
    IN p_introduce VARCHAR(255),
    IN p_unit_price DECIMAL(11,2),
    IN p_amount INT(10),
    IN p_goods_memo VARCHAR(300))
	BEGIN
		# 声明处理程序
		# 方式一
		#DECLARE EXIT HANDLER FOR 1062 SET @err_code = -1;
		# 方式二
		#DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err_code = -1;
		# 方式三
		DECLARE duplicate_entry CONDITION FOR 1062;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @err_code = -1;
		
		INSERT INTO goods (id, goods_name, supplier_id, goods_type, banner, introduce, unit_price, amount, goods_memo)
    VALUES (p_id, p_goods_name, p_supplier_id, p_goods_type, p_banner, p_introduce, p_unit_price, p_amount, p_goods_memo);
		
		END $
DELIMITER ;
		CALL insert_good_by_id(22,'农夫山泉', 1, '饮用水', 'banner.jpg', 'Product A description', 2.5, 100, 'Memo');
		SELECT @err_code;

本栏目推荐文章
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 4- if 流程语句和案例
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• Ansible - 定义变量
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• 深入浅出 C 语言：学变量、掌控流程、玩指针，全方位掌握 C 编程技能
• js中的对象，如果赋值给多个变量，那么会有性能问题吗
• 【LeetCode 2142. 每辆车的乘客人数 I】乘客到站等车，车到站载客。MySQL用户变量编程完成
• 采用递归的方法简化函数 在允许创建临时变量的前提下实现求字符串长度

游标 变量 流程游标 变量 流程mysql 游标 变量 游标 变量 语句sqlserver 游标 变量 流程 运算符 常量 变量 流程 变量 函数 关键字 流程 游标 游标sql 游标oracle 游标 视图