第16章 变量、流程控制与游标

1.变量

*在MySQL数据库中，变量分为系统变量和用户自定义变量

1.1系统变量

*系统变量的分类：全局系统变量（global）、会话系统变量（session）

*全局系统变量针对所有会话有效，但不能跨重启

*会话系统变量仅针对于当前会话有效

*查看系统变量

（1）查看所有或部分系统变量

#查看所有全局变量

SHOW GLOBAL VARIABLES;

#查看所有会话变量

SHOW SESSION VARIABLES;

或

SHOW VARIABLES; #默认查询会话系统变量

#查看满足条件的部分系统变量。

SHOW GLOBAL VARIABLES LIKE '%标识符%';

#查看满足条件的部分会话变量

SHOW SESSION VARIABLES LIKE '%标识符%';

（2）查看指定系统变量

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

   #查看指定的系统变量的值

SELECT @@global.变量名;

#查看指定的会话变量的值

SELECT @@session.变量名;

#或者

SELECT @@变量名;

（3）修改系统变量的值

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

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

#为某个系统变量赋值

#方式1：

SET @@global.变量名=变量值;

#方式2：

SET GLOBAL 变量名=变量值;

#为某个会话变量赋值

#方式1：

SET @@session.变量名=变量值;

#方式2：

SET SESSION 变量名=变量值;

1.2用户变量

*用户变量的分类：会话用户变量、局部变量

*会话用户变量：作用域和会话变量一样，只对当前连接会话有效，使用@开头

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

*会话用户变量的定义：

   #方式1：“=”或“:=”

SET @用户变量 = 值;

SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字

SELECT @用户变量 := 表达式 [FROM 等子句];

SELECT 表达式 INTO @用户变量 [FROM 等子句];

*查看用户变量的值：

   SELECT @用户变量;

*局部变量的定义：

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

BEGIN

#声明局部变量

DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];

DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

#为局部变量赋值

SET 变量名1 = 值;

SELECT 值 INTO 变量名2 [FROM 子句];

#查看局部变量的值

SELECT 变量1,变量2,变量3;

END

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

 

2.定义条件与处理程序

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

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

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

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

*定义处理程序：可以为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';

3.流程控制

*流程：顺序结构、分支结构、循环结构

*MySQL流程控制语句：条件判断语句、循环语句、跳转语句

3.1分支结构_IF

*语法结构：

IF 表达式1 THEN 操作1

[ELSEIF 表达式2 THEN 操作2]……

[ELSE 操作N]

END IF

*特点：不同的表达式对应不同的操作；使用在begin end中

*举例：

声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。

DELIMITER //

CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)

BEGIN

DECLARE emp_salary DOUBLE;

DECLARE hire_year DOUBLE;

SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;

SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year

FROM employees WHERE employee_id = emp_id;

IF emp_salary < 8000 AND hire_year > 5

THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id;

END IF;

END //

DELIMITER ;

3.2分支结构_CASE

*语法结构：

#情况一：类似于switch

CASE 表达式

WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)

WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)

...

ELSE 结果n或语句n(如果是语句，需要加分号)

END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

#情况二：类似于多重if

CASE

WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)

WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)

...

ELSE 结果n或语句n(如果是语句，需要加分号)

END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

3.3循环结构_LOOP

*loop循环语句用来重复执行某些语句，loop内的语句一直重复执行直到循环被退出（使用LEAVE子句退出）

*语法结构：

[loop_label:] LOOP

循环执行的语句

END LOOP [loop_label]

*举例：

使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。

DECLARE id INT DEFAULT 0;

add_loop:LOOP

SET id = id +1;

IF id >= 10 THEN LEAVE add_loop;

END IF;

END LOOP add_loop;

3.4循环结构_WHILE

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

*语法结构：

[while_label:] WHILE 循环条件 DO

循环体

END WHILE [while_label];

*举例：

WHILE语句示例，i值小于10时，将重复执行循环过程，代码如下：

DELIMITER //

CREATE PROCEDURE test_while()

BEGIN

DECLARE i INT DEFAULT 0;

WHILE i < 10 DO

SET i = i + 1;

END WHILE;

SELECT i;

END //

DELIMITER ;

#调用

CALL test_while();

3.5循环结构_REPEAT

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

*语法结构：

[repeat_label:] REPEAT

循环体的语句

UNTIL 结束循环的条件表达式

END REPEAT [repeat_label]

3.6跳转语句_LEAVE

*可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作（可以把 LEAVE 理解为 break）。

*语法结构：

   LEAVE 标记名

3.7跳转语句_ITERATE

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

语法格式：

   ITERATE label

4.游标

*游标：游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用 ，我们可以通过操作游标来对数据行进行操作。MySQL中游标可以在存储过程和函数中使用。

*游标的使用步骤：

   第一步：声明游标

      DECLARE cursor_name CURSOR FOR select_statement;

   第二步：打开游标

      OPEN cursor_name;

   第三步：使用游标

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

   第四步：关闭游标

      CLOSE cursor_name

*举例：

   创建存储过程“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_salary DOUBLE DEFAULT 0; #记录累加的总工资

DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值

DECLARE emp_count INT DEFAULT 0; #记录循环个数

#定义游标

DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;

#打开游标

OPEN emp_cursor;

REPEAT

#使用游标（从游标中获取数据）

FETCH emp_cursor INTO cursor_salary;

SET sum_salary = sum_salary + cursor_salary;

SET emp_count = emp_count + 1;

UNTIL sum_salary >= limit_total_salary

END REPEAT;

SET total_count = emp_count;

#关闭游标

CLOSE emp_cursor;

END //

DELIMITER ;