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

16.1：变量

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

系统变量

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

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

系统变量的分类

全局系统变量：需要添加global关键字，全局系统变量针对于所有的会话(连接)有效，但不能跨重启。

会话系统变量：需要添加session关键字，会话系统变量仅针对于当前会话(连接)有效，当前会话对某个系统变量值的修改，不会影响其他会话同一个系统变量的值。

查看系统变量

#1.查看所有或部分系统变量#查询全局系统变量SHOWGLOBALVARIABLES;SHOWGLOBALVARIABLESLIKE'admin_%';#查询会话系统变量SHOWSESSIONVARIABLES;SHOWVARIABLES;#默认查询的是会话系统变量SHOWVARIABLESLIKE'character_%';#2.查看指定系统变量SELECT&global.max_connections;SELECT&session.pseudo_thread_id;SELECT&character_set_client;#先查询会话系统变量，再查询全局系统变量#3.修改系统变量的值#全局系统变量：方式1：SET&global.max_connections=161;#方式2：SETGLOBALmax_connections=171;#会话系统变量：方式1：SET&session.character_set_client='gbk';#方式2：SETSESSIONcharacter_set_client='utf8mb4';

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

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

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

举例

SETPERSISTGLOBALmax_connections=1000;

用户变量

用户变量分类

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

会话用户变量：作用域和会话变量一样，只对当前连接会话有效。

局部变量：只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用。

会话用户变量

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

局部变量

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

#定义变量的语法DECLARE变量名类型[default值];#如果没有default子句，初始值为NULl#举例：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salaryDELIMITER//CREATEPROCEDUREtest_proBEGIN#声明DECLAREemp_nameVARCHAR(25);DECLAREsalDOUBLE(10,2)DEFAULT0.0;#赋值SELECTlast_name,salaryINTOemp_name,salFROMemployeesWHEREemployee_id=102;#使用SELECTemp_name,sal;END//DELIMITER;#调用存储过程CALLtest_pro;

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

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

16.2：定义条件与处理程序

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

定义条件

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

DECLARE错误名称CONDITIONFOR错误码(或错误条件)

错误码说明

MySQL_error_code是数值类型错误代码。

sqlstate_value是长度为5的字符串类型错误代码。

举例

#方式1：使用MySQL_error_codeDECLAREfield_Not_Be_NULLCONDITIONFOR1048;#方式2：使用sqlstate_valueDECLAREcommand_not_allowedCONDITIONFORSQLSTATE'42000';

定义处理程序

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

DECLARE处理方式HANDERFOR错误类型处理语句

处理方式

CONITNUE：表示遇到错误不处理，继续执行。

EXIT：表示遇到错误马上退出。

UNDO：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。

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

SQLSTATE'字符串错误码'：表示长度为5sqlstate_value类型的错误代码。

MySQL_error_code：匹配数值类型错误代码。

错误名称：表示DECLAER...CONDITION定义的错误条件名称。

SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。

NOTFOUND：匹配所有以02开头的SQLSTATE错误代码。

SQLEXCEPTION：匹配所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE错误代码。

处理语句：

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

举例

#方法1：捕获sqlstate_valueDECLARECONTINUEHANDLERFORSQLSTATE'42S02'SETINTO='NO_SUCH_TABLE';#方法2：捕获mysql_error_valueDECLARECONTINUEHANDLERFOR1146SET@info='NO_SUCH_TABLE';#方法3：先定义条件，在调用DECLAREno_such_tableCONDITIONFOR1146;DECLARECONTINUEHANDLERFORno_such_tableSET@info='NO_SUCH_TABLE';

案例解决

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

DELIMITER//CREATEPROCEDUREUpdateDataNoConditionBEGIN#声明处理程序DECLARECONTINUEHANDLERFOR1048SET@proc_value=-1;SET@x=1;UPDATEemployeesSETemail=NULLWHERElast_name='Abel';SET@x=2;UPDATEemployeesSETemail='aabbel'WHERElast_name='Abel';SET@x=3;END//DELIMITER;#调用存储过程：CALLUpdateDataNoCondition;#查看变量：SELECT@x,@proc_value;

16.3：流程控制

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

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

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

IF语句：

语法

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

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

举例

#举例1DELIMITER//CREATEPROCEDUREtest_ifBEGINDECLAREageINTDEFAULT20;IFage>40THENSELECT'中老年';ELSEIFage>18THENSELECT'青壮年';ELSEIFage>8THENSELECT'青少年';ELSESELECT'婴幼儿';ENDIF;END//DELIMITER;#调用CALLtest_if;#举例2：声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。#判断该员工薪资如果低于9000元，就更新薪资为9000元；#薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；#其他的涨薪100元。DELIMITER//CREATEPROCEDUREupdate_salary_by_eid3(INemp_idINT)BEGIN#声明变量DECLAREemp_salDOUBLE;DECLAREbonusDOUBLE;#赋值SELECTsalaryINTOemp_salFROMemployeesWHEREemployee_id=emp_id;SELECTcommission_pctINTObonusFROMemployeesWHEREemployee_id=emp_id;#判断IFemp_sal

CASE语句：

语法

#情况一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后面不需要)

举例

#举例1DELIMITER//CREATEPROCEDUREtest_caseBEGINdeclarevar1intdefault10;casewhenvar1>=1000thenselect'三位数';whenvar1>=10thenselect'两位数';elseselect'个位数';endcase;END//DELIMITER;#调用CALLtest_case;#举例2：声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。#判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；#如果是2年，薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。DELIMITER//CREATEPROCEDUREupdate_salary_by_eid(INemp_idINT)BEGIN#声明局部变量DECLAREhire_yearINT;#记录员工入职公司的总时间(单位：年)#赋值SELECTROUND(DATEDIFF(CURDATE,hire_date)/365)INTOhire_yearFROMemployeesWHEREemployee_id=emp_id;#判断CASEhire_yearWHEN0THENUPDATEemployeesSETsalary=salary+50WHEREemployee_id=em_id;WHEN1THENUPDATEemployeesSETsalary=salary+100WHEREemployee_id=em_id;WHEN2THENUPDATEemployeesSETsalary=salary+200WHEREemployee_id=em_id;WHEN3THENUPDATEemployeesSETsalary=salary+300WHEREemployee_id=em_id;WHEN4THENUPDATEemployeesSETsalary=salary+400WHEREemployee_id=em_id;ELSEUPDATEemployeesSETsalary=salary+500WHEREemployee_id=emp_id;ENDCASE;END//DELIMITER;#调用CALLupdate_salary_by_eid(101);

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

LOOP语句

语法

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

举例

#举例1：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。#声明存储过程“update_salary_loop”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。DELIMITER//CREATEPROCEDUREupdate_salary_loop(OUTnumINT)BEGIN#声明变量DECLAREavg_salDOUBLE;#记录员工的平均工资DECLAREloop_countINTDEFAULT0;#记录循环的次数#(1)初始化条件#获取员工的平均工资SELECTAVG(salary)INTOavg_salFROMemployees;loop_lab:LOOP#(2)循环条件#结束循环的条件IFavg_sal>=12000THENLEAVEloop_lab;ENDIF;#(3)循环体#如果低于12000，更新员工的工资UPDATEemployeesSETsalary=salary*1.1;#(4)迭代条件#更新avg_sal变量的值SELECTAVG(salary)INTOavg_salFROMemployees;#记录循环次数SETloop_count=loop_count+1;ENDLOOPloop_lab;#给num赋值SETnum=loop_count;END//DELIMITER;#调用CALLupdate_salary_loop(@num);SELECT@num;

WHILE语句

语法

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

举例

#举例2：市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。#声明存储过程“update_salary_while”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家降薪，薪资降为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。DELIMITER//CREATEPROCEDUREupdate_salary_while(OUTnumINT)BEGIN#声明变量DECLAREavg_salDOUBLE;DECLAREwhile_countINTDEFAULT0;#赋值SELECTAVG(salary)INTOavg_salFROMemployees;WHILEavg_sal>5000DOUPDATEemployeesSETsalary=salary*0.9;SETwhile_count=while_count+1;SELECTAVG(salary)INTOavg_salFROMemployees;ENDWHILE;#给num赋值SETnum=while_count;END//DELIMITER;#调用CALLupdate_salary_while(@num);SELECT@num;

REPEAT语句

语法

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

UNTIL结束循环的条件表达式ENDREPEAT[repeat_label]

举例

#举例2：当市场环境变好时，公司为了奖励大家，决定给大家涨工资。#声明存储过程“update_salary_repeat”，声明OUT参数num，输出循环次数。#存储过程中实现循环给大家涨薪，薪资涨为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。DELIMITER//CREATEPROCEDUREupdate_salary_repeat(OUTnumINT)BEGIN#声明变量DECLAREavg_salDOUBLE;DECLARErepeat_countINTDEFAULT0;#赋值SELECTAVG(salary)INTOavg_salFROMemployees;REPEATUPDATEemployeesSETsalary=salary*1.5;SETrepeat_count=repeat_count+1;SELECTAVG(salary)INTOavg_salFROMemployees;UNTILavg_sal>=13000ENDREPEAT;#给num赋值SETnum=repeat_count;END//DELIMITER;#调用CALLupdate_salary_repeat(@num);SELECT@num;

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

LEAVE语句

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

LEAVE标记名;

ITERATE语句

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

ITERATElabel;

举例

#LEAVE的使用#举例1：当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。#声明存储过程“leave_while”，声明OUT参数num，输出循环次数，#存储过程中使用WHILE循环给大家降低薪资为原来薪资的90%，直到全公司的平均薪资小于等于10000，并统计循环次数。DELIMITER//CREATEPROCEDUREleave_while(OUTnumINT)BEGINDECLAREavg_salDOUBLE;#记录平均工资DECLAREwhile_countINTDEFAULT0;#记录循环次数#(1)初始化条件SELECTAVG(salary)INTOavg_salFROMemployees;#(2)循环条件while_label:WHILETRUEDO#(3)循环体IFavg_sal15，则退出循环结构；*/DELIMITER//CREATEPROCEDUREtest_iterateBEGINDECLAREnumINTDEFAULT0;loop_label:LOOP#赋值SETnum=num+1;IFnum15THENLEAVEloop_label;ENDIF;SELECT'小王同学在学mysql';ENDLOOP;END//DELIMITER;#调用CALLtest_iterate;

16.4：游标

什么是游标

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

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

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

使用游标步骤

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

第一步：声明游标

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

#适用于mysql和SQLServer、DB2和MariaDBDECLAREcursor_nameCURSORFORselect_statement;#适用于Oracle或者PostgreSQLDECLAREcursor_nameCURSORISselect_statement;

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

第二步：打开游标

OPENcursor_name;

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

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

FETCHcursor_nameINTOvar_name[,var_name]....

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

第四步：关闭游标

CLOSEcursor_name;

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

举例

#举例：创建存储过程“get_count_by_limit_total_salary”#声明IN参数limit_total_salary，DOUBLE类型；声明OUT参数total_count，INT类型。#函数的功能可以实现累加薪资最高的几个员工的薪资，直到薪资总和到limit_total_salary参数的值，返回累加的人数total_count。DELIMITER//CREATEPROCEDUREget_count_by_limit_total_salary(INlimit_total_salaryDOUBLE,OUTtotal_countINT)BEGIN#声明局部变量DECLAREsum_salDOUBLEDEFAULT0.0;DECLAREemp_salDOUBLE;DECLAREemp_countINTDEFAULT0;#1.声明游标DECLAREemp_cursorCURSORFORSELECTsalaryFROMemployeesORDERBYsalaryDESC;#2.打开游标OPENemp_cursor;REPEAT#3.使用游标FETCHemp_cursorINTOemp_sal;SETsum_sal=sum_sal+emp_sal;SETemp_count=emp_count+1;UNTILsum_sal>=limit_total_salaryENDREPEAT;SETtotal_count=emp_count;#4.关闭游标CLOSEemp_cursor;END//DELIMITER;#调用CALLget_count_by_limit_total_salary(200000,@total_count);SELECT@total_count;