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

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

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.查看所有或部分系统变量#查询全局系统变量SHOWGLOBAL VARIABLES;SHOWGLOBAL VARIABLES LIKE'admin_%';#查询会话系统变量SHOWSESSION 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：SETGLOBAL max_connections =171;#会话系统变量：方式1：SET @@session.character_set_client='gbk';#方式2：SETSESSION 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;SELECTAVG(salary)INTO@avg_salFROM employees;#查看用户变量SELECT@m1;SELECT@count;SELECT@avg_sal;- 局部变量​ 可以使用DECLARE语句定义一个局部变量，仅仅在定义它的BEGIN ... END中有效，只能放在BEGIN ... END中，而且只能放在第一句。#定义变量的语法DECLARE 变量名 类型 [default 值];#如果没有default子句，初始值为NULl#举例：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salaryDELIMITER//CREATEPROCEDURE test_pro()BEGIN#声明DECLARE emp_name VARCHAR(25);DECLARE sal DOUBLE(10,2)DEFAULT0.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_codeDECLARE field_Not_Be_NULL CONDITION FOR1048;#方式2：使用sqlstate_valueDECLARE 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_valueDECLARECONTINUEHANDLERFOR SQLSTATE '42S02'SETINTO='NO_SUCH_TABLE';#方法2：捕获mysql_error_valueDECLARECONTINUEHANDLERFOR1146SET@info='NO_SUCH_TABLE';#方法3：先定义条件，在调用DECLARE no_such_table CONDITION FOR1146;DECLARECONTINUEHANDLERFOR no_such_table SET@info='NO_SUCH_TABLE';
3. 案例解决​ 在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到MySQL_error_code值为1048时，执行CONTINUE操作，并且将@proc_value的值设置为-1。DELIMITER//CREATEPROCEDURE UpdateDataNoCondition()BEGIN#声明处理程序DECLARECONTINUEHANDLERFOR1048SET@proc_value=-1;SET@x=1;UPDATE employees SET email =NULLWHERE 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 表达式1THEN 操作1[ELSEIF 表达式2THEN 操作2]...[ELSE 操作N]ENDIF特点：不同的表达式对应不同的操作，使用在BEGIN END中。2. 举例#举例1DELIMITER//CREATEPROCEDURE test_if()BEGINDECLARE age INTDEFAULT20;IF age >40THENSELECT'中老年';ELSEIF age >18THENSELECT'青壮年';ELSEIF age >8THENSELECT'青少年';ELSESELECT'婴幼儿';ENDIF;END//DELIMITER;#调用CALL test_if();#举例2：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。#判断该员工薪资如果低于9000元，就更新薪资为9000元；#薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；#其他的涨薪100元。DELIMITER//CREATEPROCEDURE 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 <9000THENUPDATE employees SET salary =9000WHERE employee_id = emp_id;ELSEIF emp_sal <10000AND bonus ISNULLTHENUPDATE employees SET commission_pct =0.01WHERE employee_id = emp_id;ELSEUPDATE employees SET salary = salary +100WHERE employee_id = emp_id;ENDIF;END//DELIMITER;#调用CALL update_salary_by_eid3(102);- CASE语句：1. 语法# 情况一CASE 表达式 WHEN 值1THEN 结果1或语句1(如果是语句，需要加分号)WHEN 值2THEN 结果2或语句2(如果是语句，需要加分号)...ELSE 结果n或语句n(如果是语句，需要加分号)END[CASE](如果是放在beginend中需要加上case, 如果放在select后面不需要)# 情况二CASEWHEN 条件1THEN 结果1或语句1(如果是语句，需要加分号)WHEN 条件2THEN 结果2或语句2(如果是语句，需要加分号)...ELSE 结果n或语句n(如果是语句，需要加分号)END[CASE](如果是放在beginend中需要加上case, 如果放在select后面不需要)2. 举例#举例1DELIMITER//CREATEPROCEDURE test_case()BEGINdeclare var1 intdefault10;casewhen var1 >=1000thenselect'三位数';when var1 >=10thenselect'两位数';elseselect'个位数';endcase;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//CREATEPROCEDURE update_salary_by_eid(IN emp_id INT)BEGIN#声明局部变量DECLARE hire_year INT;#记录员工入职公司的总时间(单位：年)#赋值SELECTROUND(DATEDIFF(CURDATE(), hire_date)/365)INTO hire_year FROM employees WHERE employee_id = emp_id;#判断CASE hire_year WHEN0THENUPDATE employees SET salary = salary +50WHERE employee_id = em_id;WHEN1THENUPDATE employees SET salary = salary +100WHERE employee_id = em_id;WHEN2THENUPDATE employees SET salary = salary +200WHERE employee_id = em_id;WHEN3THENUPDATE employees SET salary = salary +300WHERE employee_id = em_id;WHEN4THENUPDATE employees SET salary = salary +400WHERE employee_id = em_id;ELSEUPDATE employees SET salary = salary +500WHERE employee_id = emp_id;ENDCASE;END//DELIMITER;#调用CALL update_salary_by_eid(101);
3. 循环结构：程序满足一定条件下，重复执行一组语句。- LOOP语句1. 语法[loop_lebel:]LOOP 执行循环的语句ENDLOOP[loop_lebel](表示LOOP语句的标注名称，该参数可以省略)2. 举例#举例1：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。#声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。DELIMITER//CREATEPROCEDURE update_salary_loop(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;#记录员工的平均工资DECLARE loop_count INTDEFAULT0;#记录循环的次数#① 初始化条件#获取员工的平均工资SELECTAVG(salary)INTO avg_sal FROM employees; loop_lab: LOOP#② 循环条件#结束循环的条件IF avg_sal >=12000THENLEAVE loop_lab;ENDIF;#③ 循环体#如果低于12000，更新员工的工资UPDATE employees SET salary = salary *1.1;#④ 迭代条件#更新avg_sal变量的值SELECTAVG(salary)INTO avg_sal FROM employees;#记录循环次数SET loop_count = loop_count +1;ENDLOOP loop_lab;#给num赋值SET num = loop_count;END//DELIMITER;#调用CALL update_salary_loop(@num);SELECT@num;- WHILE语句1. 语法# WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。[while_label:]WHILE 循环条件 DO 循环体ENDWHILE[while_label];2. 举例#举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。#声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。DELIMITER//CREATEPROCEDURE update_salary_while(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;DECLARE while_count INTDEFAULT0;#赋值SELECTAVG(salary)INTO avg_sal FROM employees;WHILE avg_sal >5000DOUPDATE employees SET salary = salary *0.9;SET while_count = while_count +1;SELECTAVG(salary)INTO avg_sal FROM employees;ENDWHILE;#给num赋值SET num = while_count;END//DELIMITER;#调用CALL update_salary_while(@num);SELECT@num;- REPEAT语句1. 语法#REPEAT循环首先会执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出，即 END REPEAT; #如果条件不满足，则会就继续执行循环，直到满足退出条件为止。[repeat_label:]REPEAT 循环体的语句 UNTIL 结束循环的条件表达式ENDREPEAT[repeat_label]2. 举例#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。#声明存储过程“update_salary_repeat()”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。DELIMITER//CREATEPROCEDURE update_salary_repeat(OUT num INT)BEGIN#声明变量DECLARE avg_sal DOUBLE;DECLARE repeat_count INTDEFAULT0;#赋值SELECTAVG(salary)INTO avg_sal FROM employees;REPEATUPDATE employees SET salary = salary *1.5;SET repeat_count = repeat_count +1;SELECTAVG(salary)INTO avg_sal FROM employees; UNTIL avg_sal >=13000ENDREPEAT;#给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//CREATEPROCEDURE leave_while(OUT num INT)BEGINDECLARE avg_sal DOUBLE;#记录平均工资DECLARE while_count INTDEFAULT0;#记录循环次数#① 初始化条件SELECTAVG(salary)INTO avg_sal FROM employees;#② 循环条件 while_label: WHILETRUEDO#③ 循环体IF avg_sal <=10000THENLEAVE while_label;ENDIF;UPDATE employees SET salary = salary *0.9;SET while_count = while_count +1;#④ 迭代条件SELECTAVG(salary)INTO avg_sal FROM employees;ENDWHILE;#赋值SET num = while_count;END//DELIMITER;#调用CALL leave_while(@num);SELECT@num;#ITERATE的使用/*举例2： 定义局部变量num，初始值为0。循环结构中执行num + 1操作。如果num < 10，则继续执行循环；如果num > 15，则退出循环结构；*/DELIMITER//CREATEPROCEDURE test_iterate()BEGINDECLARE num INTDEFAULT0; loop_label: LOOP#赋值SET num = num +1;IF num <10THENITERATE loop_label;ELSEIF num >15THENLEAVE loop_label;ENDIF;SELECT'小王同学在学mysql';ENDLOOP;END//DELIMITER;#调用CALL test_iterate();

16.4：游标

1. 什么是游标​ 游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。​ 在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据进行操作。​ MySQL中游标可以在存储过程和存储函数中使用。
2. 使用游标步骤游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。- 第一步：声明游标在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：# 适用于mysql和SQL Server、DB2和MariaDBDECLARE cursor_name CURSORFOR select_statement;# 适用于Oracle或者PostgreSQLDECLARE cursor_name CURSORIS 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//CREATEPROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)BEGIN#声明局部变量DECLARE sum_sal DOUBLEDEFAULT0.0;DECLARE emp_sal DOUBLE;DECLARE emp_count INTDEFAULT0;#1.声明游标DECLARE emp_cursor CURSORFORSELECT salary FROM employees ORDERBY 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 ENDREPEAT;SET total_count = emp_count;#4.关闭游标CLOSE emp_cursor;END//DELIMITER;#调用CALL get_count_by_limit_total_salary(200000,@total_count);SELECT@total_count;