第十六章：变量、流程控制与游标

第十六章：变量、流程控制与游标

16.1：变量

​ 在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或输出最终的结果数据。变量分为系统变量和用户自定义变量。

1. 系统变量

   ​ 变量由系统定义，不是用户定义，输入服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。

   ​ 可以通过网址https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html查看MySQL文档的系统变量。

   • 系统变量的分类

     1. 全局系统变量：需要添加global关键字，全局系统变量针对于所有的会话(连接)有效，但不能跨重启。
     2. 会话系统变量：需要添加session关键字，会话系统变量仅针对于当前会话(连接)有效，当前会话对某个系统变量值的修改，不会影响其他会话同一个系统变量的值。

   • 查看系统变量

     #1.查看所有或部分系统变量
     #查询全局系统变量
     SHOW GLOBAL VARIABLES;
     SHOW GLOBAL VARIABLES LIKE 'admin_%';
     #查询会话系统变量
     SHOW SESSION VARIABLES;
     SHOW VARIABLES; #默认查询的是会话系统变量
     SHOW VARIABLES LIKE 'character_%';
     
     #2.查看指定系统变量
     SELECT @@global.max_connections;
     SELECT @@session.pseudo_thread_id;
     SELECT @@character_set_client; #先查询会话系统变量，再查询全局系统变量
      
     #3.修改系统变量的值
     #全局系统变量：方式1：
     SET @@global.max_connections = 161;
     #方式2：
     SET GLOBAL max_connections = 171;
     #会话系统变量：方式1：
     SET @@session.character_set_client = 'gbk';
     #方式2：
     SET SESSION character_set_client = 'utf8mb4';
     

2. 全局变量持久化(MySQL8.0新特性)

   • 在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。但是使用SET GLOBAL语句设置的变量只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。

   • MySQL8.0版本新增了SET PERSIST命令，MySQL会将该命名的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动是会读取该文件，用其中的配置来覆盖默认的配置文件。

   • 举例

     SET PERSIST GLOBAL max_connections = 1000;
     

3. 用户变量

   • 用户变量分类

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

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

   • 会话用户变量

     # 定义用户变量方式一
     SET @m1 = 1;
     SET @m2 := 2;
     
     # 定义用户变量方式二
     SELECT @count := COUNT(*) FROM employees;
     SELECT AVG(salary) INTO @avg_sal FROM employees;
     
     #查看用户变量
     SELECT @m1;
     SELECT @count;
     SELECT @avg_sal;
     

   • 局部变量

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

     #定义变量的语法
     DECLARE 变量名 类型 [default 值];  #如果没有default子句，初始值为NULl
     
     #举例：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary
     DELIMITER //
     CREATE PROCEDURE test_pro()
     BEGIN
     	#声明
     	DECLARE emp_name VARCHAR(25);
     	DECLARE sal DOUBLE(10, 2) DEFAULT 0.0;
     	#赋值
     	SELECT last_name, salary INTO emp_name, sal
     	FROM employees
     	WHERE employee_id = 102;
     	#使用
     	SELECT emp_name, sal;
     END //
     DELIMITER;
     #调用存储过程
     CALL test_pro();
     

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

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

16.2：定义条件与处理程序

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

1. 定义条件

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

   DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)
   

   • 错误码说明

     1. MySQL_error_code是数值类型错误代码。
     2. sqlstate_value是长度为5的字符串类型错误代码。

   • 举例

     #方式1：使用MySQL_error_code
     DECLARE field_Not_Be_NULL CONDITION FOR 1048;
     #方式2：使用sqlstate_value
     DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
     

2. 定义处理程序

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

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

   • 处理方式

     1. CONITNUE：表示遇到错误不处理，继续执行。
     2. EXIT：表示遇到错误马上退出。
     3. UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

   • 错误类型(即条件)可以有如下取值：

     1. SQLSTATE '字符串错误码'：表示长度为5sqlstate_value类型的错误代码。
     2. MySQL_error_code：匹配数值类型错误代码。
     3. 错误名称：表示DECLAER ... CONDITION定义的错误条件名称。
     4. SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。
     5. NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。
     6. SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。

   • 处理语句：

     ​ 如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是想SET 变量 = 值这样的简单，也可以是使用BEGIN ... END编写复合语句。

   • 举例

     #方法1：捕获sqlstate_value
     DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET INTO = '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';
     

3. 案例解决

   ​ 在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。

   DELIMITER //
   CREATE PROCEDURE UpdateDataNoCondition()
   	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 UpdateDataNoCondition();
   #查看变量：
   SELECT @x, @proc_value;
   

16.3：流程控制

解决复杂问题不可能通过一个SQL语句完成，我们需要执行多个SQL操作。流程控制语句的作用就是控制存储过程中SQL语句的执行顺序，是我们完成复杂操作比不可少的一部分。只要是执行的程序，流程就分为三大类：

1. 顺序结构：程序从上往下依次执行。

2. 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行。

   • IF语句：

     1. 语法

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

        特点：不同的表达式对应不同的操作，使用在BEGIN END中。

     2. 举例

        #举例1
        DELIMITER //
        CREATE PROCEDURE test_if()
        BEGIN
        	DECLARE age INT DEFAULT 20;
        	
        	IF age > 40
        		THEN SELECT '中老年';
        	ELSEIF age > 18
        		THEN SELECT '青壮年';
        	ELSEIF age > 8
        		THEN SELECT '青少年';
        	ELSE
        		SELECT '婴幼儿';
        	END IF;
        END //
        DELIMITER;
        #调用
        CALL test_if();
        
        #举例2：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。
        #判断该员工薪资如果低于9000元，就更新薪资为9000元；
        #薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；
        #其他的涨薪100元。
        DELIMITER //
        CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
        BEGIN
        	#声明变量
        	DECLARE emp_sal DOUBLE;
        	DECLARE bonus DOUBLE;
        	#赋值
        	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
        	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;
        	#判断
        	IF emp_sal < 9000
        		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
        	ELSEIF emp_sal < 10000 AND bonus IS NULL
        		THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
        	ELSE
        		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
        	END IF;
        END //
        DELIMITER;
        #调用
        CALL update_salary_by_eid3(102);
        

   • CASE语句：

     1. 语法

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

     2. 举例

        #举例1
        DELIMITER //
        CREATE PROCEDURE test_case()
        BEGIN
        	declare var1 int default 10;
        	case 
        		when var1 >= 1000 then select '三位数';
        		when var1 >= 10 then select '两位数';
        		else select '个位数';
        	end case;
        	END //
        DELIMITER;
        #调用
        CALL test_case();
        
        #举例2：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。
        #判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；
        #如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
        DELIMITER //
        CREATE PROCEDURE update_salary_by_eid(IN emp_id INT)
        BEGIN
        		#声明局部变量
        		DECLARE hire_year INT; #记录员工入职公司的总时间(单位：年)
        		#赋值
        		SELECT ROUND(DATEDIFF(CURDATE(), hire_date)/365) INTO hire_year 
        		FROM employees WHERE employee_id = emp_id;
        		#判断
        		CASE hire_year
        			WHEN 0 THEN UPDATE employees SET salary = salary + 50 WHERE employee_id = em_id;
        			WHEN 1 THEN UPDATE employees SET salary = salary + 100 WHERE employee_id = em_id;
        			WHEN 2 THEN UPDATE employees SET salary = salary + 200 WHERE employee_id = em_id;
        			WHEN 3 THEN UPDATE employees SET salary = salary + 300 WHERE employee_id = em_id;
        			WHEN 4 THEN UPDATE employees SET salary = salary + 400 WHERE employee_id = em_id;
        			ELSE UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;
        		END CASE;
        END //
        DELIMITER;
        #调用
        CALL update_salary_by_eid(101);
        

3. 循环结构：程序满足一定条件下，重复执行一组语句。

   • LOOP语句

     1. 语法

        [loop_lebel:] LOOP
        	执行循环的语句
        END LOOP [loop_lebel](表示LOOP语句的标注名称，该参数可以省略)
        

     2. 举例

        #举例1：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
        #声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。
        #存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
        DELIMITER //
        CREATE PROCEDURE update_salary_loop(OUT num INT)
        BEGIN
        	#声明变量
        	DECLARE avg_sal DOUBLE; #记录员工的平均工资
        	DECLARE loop_count INT DEFAULT 0; #记录循环的次数
        	#① 初始化条件
        	#获取员工的平均工资
        	SELECT AVG(salary) INTO avg_sal FROM employees;
        	
        	loop_lab: LOOP
        		#② 循环条件
        		#结束循环的条件
        		IF avg_sal >= 12000
        			THEN LEAVE loop_lab;
        		END IF;
        		#③ 循环体
        		#如果低于12000，更新员工的工资
        		UPDATE employees SET salary = salary * 1.1;
        		#④ 迭代条件
        		#更新avg_sal变量的值
        		SELECT AVG(salary) INTO avg_sal FROM employees;
        		#记录循环次数
        		SET loop_count = loop_count + 1;
        	END LOOP loop_lab;
        	#给num赋值
        	SET num = loop_count;
        END //
        DELIMITER;	
        #调用
        CALL update_salary_loop(@num);
        SELECT @num;
        

   • WHILE语句

     1. 语法

        # WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。
        [while_label:] WHILE 循环条件 DO
        	循环体
        END WHILE [while_label];
        

     2. 举例

        #举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
        #声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。
        #存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。
        DELIMITER //
        CREATE PROCEDURE update_salary_while(OUT num INT)
        BEGIN
        	#声明变量
        	DECLARE avg_sal DOUBLE;
        	DECLARE while_count INT DEFAULT 0;	
        	#赋值
        	SELECT AVG(salary) INTO avg_sal FROM employees;
        	
        	WHILE avg_sal > 5000 DO
        		UPDATE employees SET salary = salary * 0.9;
        		SET while_count = while_count + 1;
        		
        		SELECT AVG(salary) INTO avg_sal FROM employees;
        	END WHILE;
        	#给num赋值
        	SET num = while_count;
        END //
        DELIMITER;
        #调用
        CALL update_salary_while(@num);
        SELECT @num;
        

   • REPEAT语句

     1. 语法

        #REPEAT循环首先会执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出，即 END REPEAT; 
        #如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
        [repeat_label:] REPEAT
        	循环体的语句
        	UNTIL 结束循环的条件表达式
        END REPEAT[repeat_label]
        

     2. 举例

        #举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
        #声明存储过程“update_salary_repeat()”，声明OUT参数num，输出循环次数。
        #存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。
        DELIMITER //
        CREATE PROCEDURE update_salary_repeat(OUT num INT)
        BEGIN
        	#声明变量
        	DECLARE avg_sal DOUBLE;
        	DECLARE repeat_count INT DEFAULT 0;
        	#赋值
        	SELECT AVG(salary) INTO avg_sal FROM employees;
        	
        	REPEAT
        		UPDATE employees SET salary = salary * 1.5;
        		SET repeat_count = repeat_count + 1;
        		
        		SELECT AVG(salary) INTO avg_sal FROM employees;		
        		UNTIL avg_sal >= 13000		
        	END REPEAT;
        	#给num赋值
        	SET num = repeat_count;
        END //
        DELIMITER;
        #调用
        CALL update_salary_repeat(@num);
        SELECT @num;
        

4. 跳转语句：在某些特定的条件下，跳出循环结构语句。

   • LEAVE语句

     可以用在循环语句内，或者以BEGIN和END包裹起来的程序题内，表示跳出循环或者跳出程序体的操作。

     LEAVE 标记名;
     

   • ITERATE语句

     只能用在循环语句(LOOP、REPEAT和WHILE)内，表示循环开始循环，将执行顺序转到语句段开头处。

     ITERATE label;
     

   • 举例

     #LEAVE的使用
     #举例1：当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
     #声明存储过程“leave_while()”，声明OUT参数num，输出循环次数，
     #存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。
     DELIMITER //
     CREATE PROCEDURE leave_while(OUT num INT)
     BEGIN
     	DECLARE avg_sal DOUBLE; #记录平均工资
     	DECLARE while_count INT DEFAULT 0; #记录循环次数
     	#① 初始化条件
     	SELECT AVG(salary) INTO avg_sal FROM employees; 
     	#② 循环条件
     	while_label: WHILE TRUE DO
     		#③ 循环体
     		IF avg_sal <= 10000 THEN
     			LEAVE while_label;
     		END IF;
     		
     		UPDATE employees SET salary = salary * 0.9;
     		SET while_count = while_count + 1;
     		#④ 迭代条件
     		SELECT AVG(salary) INTO avg_sal FROM employees;
     	END WHILE;
     	#赋值
     	SET num = while_count;
     END //
     DELIMITER;
     #调用
     CALL leave_while(@num);
     SELECT @num;
     
     #ITERATE的使用
     /*
     举例2： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。
     如果num < 10，则继续执行循环；如果num > 15，则退出循环结构；
     */
     DELIMITER //
     CREATE PROCEDURE test_iterate()
     BEGIN
     	DECLARE num INT DEFAULT 0;
     	
     	loop_label: LOOP
     		#赋值
     		SET num = num + 1;
     		
     		IF num < 10
     			THEN ITERATE loop_label;
     		ELSEIF num > 15
     			THEN LEAVE loop_label;
     		END IF;
     		
     		SELECT '小王同学在学mysql';
     	END LOOP;
     END //
     DELIMITER;
     #调用
     CALL test_iterate();
     

16.4：游标

1. 什么是游标

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

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

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

2. 使用游标步骤

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

   • 第一步：声明游标

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

     # 适用于mysql和SQL Server、DB2和MariaDB
     DECLARE cursor_name CURSOR FOR select_statement;
     # 适用于Oracle或者PostgreSQL
     DECLARE cursor_name CURSOR IS select_statement;
     

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

   • 第二步：打开游标

     OPEN cursor_name;
     

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

   • 第三步：使用游标(从游标中取得数据)

     FETCH cursor_name INTO var_name [, var_name]....
     

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

   • 第四步：关闭游标

     CLOSE cursor_name;
     

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

3. 举例

   # 举例：创建存储过程“get_count_by_limit_total_salary()”
   # 声明IN参数 limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。
   # 函数的功能可以实现累加薪资最高的几个员工的薪资，直到薪资总和到limit_total_salary参数的值，返回累加的人数total_count。
   DELIMITER //
   CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE, OUT total_count INT)
   BEGIN
   	#声明局部变量
   	DECLARE sum_sal DOUBLE DEFAULT 0.0;
   	DECLARE emp_sal DOUBLE;
   	DECLARE emp_count INT DEFAULT 0;
   	#1.声明游标
   	DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
   	#2.打开游标
   	OPEN emp_cursor;
   	
   	REPEAT
   		#3.使用游标
   		FETCH emp_cursor INTO emp_sal;
   		
   		SET sum_sal = sum_sal + emp_sal;
   		SET emp_count = emp_count + 1;
   		UNTIL sum_sal >= limit_total_salary
   	END REPEAT;
   	
   	SET total_count = emp_count;
   	#4.关闭游标
   	CLOSE emp_cursor;
   END//
   DELIMITER;
   #调用
   CALL get_count_by_limit_total_salary(200000, @total_count);
   SELECT @total_count;