16. 变量、定义条件和处理程序

变量
- 系统变量
CALL set_value();
DELIMITER ;
- 定义条件
- 案例解决

变量

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

系统变量

系统变量分类

启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件中的参数值
系统变量可分为全局系统变量（需要添加global关键字）和会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，把会话系统变量称为local变量。如果不写，默认为会话系统变量。静态变量（在MySQL服务实例运行期间，它们的值不能使用set动态修改）属于特殊的全局系统变量
每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制
全局系统变量针对于所有会话（连接）有效，但不能跨重启
会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改
在MySQL中有些系统变量只能是全局的，有些系统变量作用域既可以是全局的又可以是会话的，有些系统变量的作用域只能是当前会话

查看系统变量
查看所有或部分系统变量 ```sql //查看所有全局变量 SHOW GLOBAL VARIABLES;

//查看所有会话变量 SHOW SESSION VARIABLES; 或者 SHOW VARIABLES;

//查看满足条件的部分系统变量 SHOW GLOBAL VARIABLES LIKE ‘%标识符%’;

//查看满足条件的部分会话变量 SHOW SESSION VARIABLES LIKE ‘%标识符%’;


- 查看指定系统变量
MySQL中的系统变量以两个@开头，其中"@@global"用于标记全局系统变量，"@@session"用于标记会话系统变量。而"@@"首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量
```sql
//查看指定的全局系统变量
SELECT @@global.变量名;
//查看指定的会话系统变量
SELECT @@session.变量名;
或者
SELECT @@变量名;

修改系统变量的值
- 方式一：修改MySQL配置文件，继而修改MySQL系统变量的值，然后重启MySQL服务
- 方式二：在MySQL服务运行期间，使用set命令重新设置系统变量的值 ```sql //为某个系统变量赋值, 两种方式 SET @@global.变量名 = 变量值; SET GLOBAL 变量名 = 变量值;

//为某个会话系统变量赋值, 两种方式 SET @@session.变量名 = 变量值; SET SESSION 变量名 = 变量值;

<a name="DQIDw"></a>
### 用户变量
<a name="ABHP5"></a>
#### 用户变量分类
MySQL中的用户变量，根据作用范围不同，分为会话用户变量和局部变量

- 会话用户变量：以一个@开头，只对当前会话有效
- 局部变量：只在BEGIN 和 END语句块中有效，只能在存储过程和函数中使用
<a name="mk6no"></a>
#### 会话用户变量

- 变量的定义
```sql
//方式一: = 或 :=
SET @用户变量 = 值;
SET @用户变量 := 值;

//方式二：:= 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];

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

SELECT @用户变量; //查看某个未声明的变量时，将得到NULL值

局部变量

定义：使用DECLARE语句声明一个局部变量
作用域：必须声明和使用在BEGIN…END中
位置：DECLARE方式声明的局部变量必须声明在首行

变量声明

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

变量赋值 ```sql //方式一 SET 变量名 = 值; SET 变量名 := 值;

//方式二 SELECT 字段名或表达式 INTO 变量名 [FROM子句];


3. 使用变量
```sql
SELECT 变量名;
SELECT 变量名1, 变量名2,...;

举例 ```sql //声明局部变量, 并分别赋值为employees表中employee_id为102的last_name和salary DELIMITER // CREATE PROCEDURE set_value() BEGIN DECLARE emp_name VARCHAR(25); DECLARE sal DOUBLE(10, 2); SELECT last_name, salary INTO emp_name, sal FROM employees WHERE employee_id = 102; SELECT emp_name, sal; END // DELIMITER ;

CALL set_value();

//声明两个变量，求和并打印 //方式一：使用会话用户变量的方式 SET @m = 1; SET @n = 1; SET @sum = @m + @n; SELECT @sum;

//方式二：使用局部变量的方式 DELIMITER // CREATE PROCEDURE add_value() BEGIN DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 2; DECLARE sum INT; SET sum = m + n; SELECT sum; END //

DELIMITER ;

//创建存储过程”different_salary”查询某员工和他领导的薪资差距, 并用IN参数emp_id接收员工id, 用OUT参数dif_salary输出薪资差距结果

DELIMITER // CREATE PROCEDURE different_salary(IN emp_id INT, OUT dif_salary DOUBLE) BEGIN DECLARE emp_sal, mgr_sal DOUBLE DEFAULT 0.0; DECLARE mgr_id INT; SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id; SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id; SET dif_salary = mgr_sal - emp_sal; END // DELIMITER ;

SET @emp_id = 102; CALL different_salary(@emp_id, @diff_sal); SELECT @diff_sal;


---

<a name="vYRaH"></a>
## 定义条件和处理程序

- 定义条件是事先定义程序执行过程中可能遇到的问题
- 处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行
- 说明：定义条件和处理程序在存储过程、存储函数中都是支持的
<a name="ILeiS"></a>
### 案例分析
```sql
//创建一个名称为"UpdateDataNoCondition"的存储过程
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
BEGIN
  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 ;

调用存储过程：

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

定义条件

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

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 ```sql //使用MySQL_error_code DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

//使用sqlstate_value DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE ‘23000’;

<a name="TlwIk"></a>
### 定义处理程序
可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序
```sql
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句;

处理方式：有三个取值：COUTINUE、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';

案例解决

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 ;

调用存储过程：

变量