16. 变量、流程控制与游标

1. 变量

系统变量

系统变量分类

由系统定义的变量，属于服务器层面。

启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。

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

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

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

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

查看系统变量

查看所有或部分系统变量

-- 查看所有全局变量
SHOW GLOBAL VARIABLES;
-- 查看所有会话变量
SHOW SESSION VARIABLES;
-- 或
SHOW VARIABLES;

-- 查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
-- 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

查看指定系统变量

作为 MySQL 编码规范，MySQL 中的系统变量以“@@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名;

修改系统变量的值

方式1：修改MySQL 配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;

用户变量

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

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

会话用户变量

变量的定义

-- 方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
-- 方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看用户变量的值（查看、比较、运算等）

SELECT @用户变量

示例：

SELECT @num := COUNT(*) FROM employees;
SELECT @num;
SELECT @big; #查看某个未声明的变量时，将得到NULL值

局部变量

定义：可以使用DECLARE语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN ... END 中有效
位置：只能放在 BEGIN ... END 中，而且只能放在第一句

BEGIN
-- 声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
-- 为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
-- 查看局部变量的值
SELECT 变量1,变量2,变量3;
END

声明局部变量时如果没有DEFAULT子句，初始值为NULL

变量赋值

-- 方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
-- 方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

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

用户变量	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号，不用指定类型
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@,需要指定类型

2. 定义条件与处理程序

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

案例：

DELIMITER //
CREATE PROCEDURE UpData()
BEGIN
SET @x = 1;
UPDATE emp SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE emp SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
-- ERROR 1048 (23000): Column 'email' cannot be null
CALL UpData();
SELECT @x;-- 1

在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

定义条件

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

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

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

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。
- MySQL_error_code是数值类型错误代码。
- sqlstate_value是长度为5的字符串类型错误代码。

例如，在ERROR 1418 (HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。

示例：

-- 使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
-- 使用sqlstate_value
DECLARE Field_Not_Be_NULL 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：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';
-- 方法2：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' 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';

案例解决：

DELIMITER //
CREATE PROCEDURE UpData()
BEGIN
-- 定义处理程序
DECLARE CONTINUE HANDLER FOR 1048 SET @proc_value = -1;
SET @x = 1;
UPDATE employees SET email = NULL WHERE last_name = 'Abel';
SET @x = 2;
UPDATE employees SET email = 'aabbel' WHERE last_name = 'Abel';
SET @x = 3;
END //
DELIMITER ;
CALL UpData();
SELECT @x,@proc_value;-- 3;-1

3. 流程控制

只要是执行的程序，流程就分为三大类：

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

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

分支结构之 IF

语法结构：

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

示例：

DELIMITER $
CREATE PROCEDURE update_salary(IN emp_id INT)
BEGIN
    DECLARE sal DOUBLE;
    DECLARE bonus DECIMAL(3,2);
    SELECT salary,commission_pct INTO sal,bonus 
    FROM emp WHERE employee_id=emp_id;
    IF sal<9000 THEN 
        UPDATE emp SET salary=9000 WHERE employee_id=emp_id;
    ELSEIF sal<10000 AND bonus IS NULL THEN
        UPDATE emp SET commission_pct =0.01 
        WHERE employee_id=emp_id;
    ELSE UPDATE emp SET salary=salary+100 WHERE employee_id=emp_id;
    END IF;
END $
DELIMITER ;

分支结构之 CASE

结构与单行函数中流程控制函数的CASE()函数相同。

CASE 语句的语法结构1：

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

CASE 语句的语法结构2：

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

示例：

DELIMITER $
CREATE PROCEDURE update_salary1(IN emp_id INT)
BEGIN 
    DECLARE hire_year INT;
    SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year
    FROM emp WHERE employee_id =emp_id;
    CASE hire_year
        WHEN 3 THEN UPDATE emp SET salary=salary+300
        WHERE employee_id=emp_id;
        WHEN 4 THEN UPDATE emp SET salary=salary+400
        WHERE employee_id=emp_id;
        ELSE UPDATE emp SET salary=salary+500
        WHERE employee_id=emp_id;
    END CASE;
END $
DELIMITER ;

循环结构之LOOP

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

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

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

示例：

DELIMITER $
CREATE PROCEDURE update_salary2(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE;
    DECLARE cou INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM emp;
    label:LOOP
        IF avg_sal>=12000 THEN LEAVE label;
        END IF;
        UPDATE emp SET salary=salary*1.1;
        SET cou=cou+1;
        SELECT AVG(salary) INTO avg_sal FROM emp;
    END LOOP label;
    SET num=cou;
END $
DELIMITER ;

循环结构之WHILE

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

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

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

示例：

DELIMITER $
CREATE PROCEDURE update_salary3(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE;
    DECLARE cou INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM emp;
    WHILE avg_sal>9000 DO
        UPDATE emp SET salary=salary*0.9;
        SET cou=cou+1;
        SELECT AVG(salary) INTO avg_sal FROM emp;
    END WHILE;
    SET num=cou;
END $
DELIMITER ;

循环结构之REPEAT

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

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

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

示例：

DELIMITER $
CREATE PROCEDURE update_salary4(OUT num INT)
BEGIN
    DECLARE avg_sal DOUBLE;
    DECLARE cou INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM emp;
    REPEAT
        UPDATE emp SET salary=salary*0.9;
        SET cou=cou+1;
        SELECT AVG(salary) INTO avg_sal FROM emp;
    UNTIL avg_sal<6000
    END REPEAT;
    SET num=cou;
END $
DELIMITER ;

对比三种循环结构：

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

跳转语句之LEAVE语句

LEAVE语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。(可以理解为Java中的break)

基本格式：

LEAVE label

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

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 emp;
    END IF;
    SELECT COUNT(*) FROM emp;
END //
DELIMITER ;
CALL leave_begin(1);

跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。(可以理解为Java中的continue)

基本格式：

ITERATE label

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

示例：

DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
    DECLARE num INT DEFAULT 0;
    my_loop:LOOP
    SET num = num + 1;
    IF num < 5 THEN ITERATE my_loop;
    ELSEIF num > 10 THEN LEAVE my_loop;
    END IF;
    SELECT 'MYSQL';
    END LOOP my_loop;
END //
DELIMITER ;

跳转语句的两个示例由于SQLyog显示的原因需要在命令行中运行

4. 游标

可以在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

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

游标的使用步骤

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

1. 声明游标

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

DECLARE cursor_name CURSOR FOR select_statement;

这个语法适用于 MySQL，SQL Server，DB2 和 MariaDB。如果是用 Oracle 或者 PostgreSQL，需要写成：

DECLARE cursor_name CURSOR IS select_statement;

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

2. 打开游标

OPEN cursor_name

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

3. 使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

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

注意：

var_name必须在声明游标之前就定义好。
游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

4. 关闭游标

CLOSE cursor_name

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

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

使用示例：

-- 累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_salary
-- 参数的值，返回累加的人数给total_count。
DELIMITER //
CREATE PROCEDURE get_count(IN limit_salary DOUBLE,OUT total_count INT)
BEGIN
    DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
    DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
    DECLARE emp_count INT DEFAULT 0; #记录循环个数
    #定义游标
    DECLARE emp_cursor CURSOR FOR SELECT salary 
    FROM emp ORDER BY salary DESC;
    #打开游标
    OPEN emp_cursor;
    WHILE sum_salary < limit_salary DO
    #使用游标（从游标中获取数据）
    FETCH emp_cursor INTO cursor_salary;
    SET sum_salary = sum_salary + cursor_salary;
    SET emp_count = emp_count + 1;
    END WHILE;
    SET total_count = emp_count;
    #关闭游标
    CLOSE emp_cursor;
END //
DELIMITER ;

游标是 MySQL 的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。

5. MySQL 8.0的新特性—全局变量的持久化

使用SET GLOBAL语句设置的变量值只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0版本新增了 SET PERSIST 命令。

示例：

-- 设置服务器的最大连接数为1000：
SET PERSIST [GLOBAL] max_connections = 1000;

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

posted @ 2023-10-15 21:58 LemonPuer 阅读(17) 评论(0) 收藏举报

刷新页面返回顶部

柠檬普洱