变量、流程控制与游标
作者：FL
博客：https://www.cnblogs.com/flblogs/
- 注意：本笔记部分参考尚硅谷-宋红康
1. 变量
MySQL中的变量是一种可以在SQL语句中动态存储和传递数据的机制。与其他编程语言类似，在 MySQL 数据库中，变量分为系统变量以及用户自定义变量。
常量：
字符串常量
数值常量
时间日期常量
用户定义变量
用户定义变量使用两个 @ 符号开头，在执行完该语句后存活，只能在当前会话中使用。下面是一个示例：
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;