mysql8.0变量

1、变量

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

1.1、系统变量

1.1.1、系统变量分类

变量由系统定义，不是用户定义，属于服务器层面，启动MySQL服务，生成MySQL服务实例期间
MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。
这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件（例如my.ini等）中的参数值。
大家可以通过网址https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html查看MySQL文档的系统变量。

系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，
有时也把会话系统变量称为local变量。如果不写，默认会话级别。
静态变量（在MySQL服务实例运行期间它们的值不能使用set动态修改）属于特殊的全局系统变量。
每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。
会话期间，MySQL服务实例会在MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。
如下图：

全局系统变量针对于所有会话（连接）有效，但不能跨重启

会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

在MySQL中有些系统变量只能是全局的，例如max_connections用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，
例如character_set_client用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如pseudo_thread_id用于标记当前会话的MySQL连接ID。

1.1.2、查看系统变量

1、查看所有或部分系统变量
#查看所有全局变量
SHOWGLOBALVARIABLES;

#查看所有会话变量
SHOWSESSIONVARIABLES;或
SHOWVARIABLES;

#查看满足条件的部分系统变量。
SHOWGLOBALVARIABLESLIKE'%标识符%';

#查看满足条件的部分会话变量
SHOWSESSIONVARIABLESLIKE'%标识符%';

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

#查看指定的系统变量的值
SELECT@@global.变量名;

#查看指定的会话变量的值
SELECT@@session.变量名;
#或者
SELECT@@变量名;

3、修改系统变量的值
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法：
方式1：修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1：
SET@@global.变量名=变量值;
#方式2：
SETGLOBAL变量名=变量值;

#为某个会话变量赋值
#方式1：
SET@@session.变量名=变量值;
#方式2：
SETSESSION变量名=变量值;

1.2、用户变量

1.2.1、用户变量分类

用户变量是用户自己定义的，作为MySQL编码规范，MySQL中的用户变量以一个“@”开头。根据作用范围不同，又分为会话用户变量和局部变量
    会话用户变量：作用域和会话变量一样，只对当前连接会话有效
    局部变量：只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用

1.2.2、会话用户变量

1、变量的定义
#方式1：“=”或“:=”
SET@用户变量=值;
SET@用户变量:=值;

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

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

SELECT @num := COUNT(*) FROM employees;
SELECT @num;
SELECT AVG(salary) INTO @avgsalary FROM employees;
SELECT @avgsalary;
SELECT@big; #查看某个未声明的变量时，将得到NULL值

1.2.3、局部变量

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

BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];#为局部变量赋值
    SET 变量名1 = 值;
    SELECT 值 INTO 变量名2 [FROM 子句];
    #查看局部变量的值
    SELECT 变量1,变量2,变量3;
END

1.定义变量
DECLARE 变量名 类型 [default值]; #如果没有DEFAULT子句，初始值为NULL
DECLARE　myparam　INT　DEFAULT100;

2.变量赋值
方式1：一般用于赋简单的值
SET变量名=值;
SET变量名:=值;
方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

3.使用变量（查看、比较、运算等）
SELECT 局部变量名;
#举例：
DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
	#1、声明局部变量
	DECLARE a INT DEFAULT 0;
	DECLARE b INT ;
	#DECLARE a,b INT DEFAULT 0;
	DECLARE emp_name VARCHAR(25);
	#2、赋值
	SET a = 1;
	SET b := 2;
	SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
	#3、使用
	SELECT a,b,emp_name;	
END //
DELIMITER ;
#调用存储过程
CALL test_var();

#举例1：声明局部变量，并分别赋值为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;

#举例2：声明两个变量，求和并打印 （分别使用会话用户变量、局部变量的方式实现）
#方式1：使用会话用户变量
SET @v1 = 10;
SET @v2 := 20;
SET @result := @v1 + @v2;
#查看
SELECT @result;

#方式2：使用局部变量
DELIMITER //
CREATE PROCEDURE add_value()
BEGIN
	#声明
	DECLARE value1,value2,sum_val INT;
	#赋值
	SET value1 = 10;
	SET value2 := 100;
	SET sum_val = value1 + value2;
	#使用
	SELECT sum_val;
END //
DELIMITER ;

举例3：创建存储过程“different_salary”查询某员工和他领导的薪资差距，并用IN参数emp_id接收员工id，用OUT参数dif_salary输出薪资差距结果。
DELIMITER //
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE)
BEGIN
	#分析：查询出emp_id员工的工资;查询出emp_id员工的管理者的id;查询管理者id的工资;计算两个工资的差值
	#声明变量
	DECLARE emp_sal DOUBLE DEFAULT 0.0; #记录员工的工资
	DECLARE mgr_sal DOUBLE DEFAULT 0.0; #记录管理者的工资
	DECLARE mgr_id INT DEFAULT 0; #记录管理者的id
	#赋值
	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 := 103;
SET @dif_sal := 0;
CALL different_salary(@emp_id,@dif_sal);

SELECT @dif_sal;

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

2、定义条件与处理程序

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

2.1、案例分析

#错误代码： 1364
#Field 'email' doesn't have a default value
INSERT INTO employees(last_name)
VALUES('Tom');

DESC employees;

#错误演示：
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 ;

#调用存储过程
#错误代码： 1048
#Column 'email' cannot be null
CALL UpdateDataNoCondition();
SELECT @x;

可以看到，此时@x变量的值为1。结合创建存储过程的SQL语句代码可以得出：
在存储过程中未定义条件和处理程序，且当存储过程中执行的SQL语句报错时，MySQL数据库会抛出错误，并退出当前SQL逻辑，不再向下继续执行。

2.2、定义条件

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

定义条件使用DECLARE语句，语法格式如下：
DECLARE错误名称CONDITION FOR 错误码（或错误条件）
错误码的说明：
MySQL_error_code和sqlstate_value都可以表示MySQL的错误。
    MySQL_error_code是数值类型错误代码。
    sqlstate_value是长度为5的字符串类型错误代码。
    
例如，在ERROR1418(HY000)中，1418是MySQL_error_code，'HY000'是sqlstate_value。
例如，在ERROR1142（42000）中，1142是MySQL_error_code，'42000'是sqlstate_value。

举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR1048(23000)”对应。
#使用MySQL_error_code
DECLAREField_Not_Be_NULLCONDITIONFOR1048;
#使用sqlstate_value
DECLAREField_Not_Be_NULLCONDITIONFORSQLSTATE'23000';

举例2：定义"ERROR1148(42000)"错误，名称为command_not_allowed。
#使用MySQL_error_code
DECLAREcommand_not_allowedCONDITIONFOR1148;
#使用sqlstate_value
DECLAREcommand_not_allowedCONDITIONFORSQLSTATE'42000';

2.3、定义处理程序

可以为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错误代码；
    NOTFOUND：匹配所有以02开头的SQLSTATE错误代码；
    SQLEXCEPTION：匹配所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE错误代码；
处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“SET变量=值”这样的简单语句
也可以是使用BEGIN...END编写的复合语句。

定义处理程序的几种方式，代码如下：
#方法1：捕获sqlstate_value
DECLARECONTINUEHANDLERFORSQLSTATE'42S02'SET@info='NO_SUCH_TABLE';
#方法2：捕获mysql_error_value
DECLARECONTINUEHANDLERFOR1146SET@info='NO_SUCH_TABLE';
#方法3：先定义条件，再调用
DECLAREno_such_tableCONDITIONFOR1146;
DECLARECONTINUEHANDLERFORNO_SUCH_TABLESET@info='NO_SUCH_TABLE';
#方法4：使用SQLWARNING
DECLAREEXITHANDLERFORSQLWARNINGSET@info='ERROR';
#方法5：使用NOTFOUND
DECLAREEXITHANDLERFORNOTFOUNDSET@info='NO_SUCH_TABLE';
#方法6：使用SQLEXCEPTION
DECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='ERROR';

2.4、案例解决

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

#重新定义存储过程，体现错误的处理程序
DELIMITER //
CREATE PROCEDURE UpdateDataNoCondition()
	BEGIN
		#声明处理程序
		#处理方式1：
		DECLARE CONTINUE HANDLER FOR 1048 SET @prc_value = -1;
		#处理方式2：
		#DECLARE CONTINUE HANDLER FOR sqlstate '23000' SET @prc_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,@prc_value;

案例二：
DROP PROCEDURE IF EXISTS InsertDataWithCondition;
重新定义存储过程（考虑到错误的处理程序）

DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
	BEGIN		
		#处理程序
		#方式1：
		#declare exit handler for 1062 set @pro_value = -1;
		#方式2：
		#declare exit handler for sqlstate '23000' set @pro_value = -1;
		#方式3：
		#定义条件
		DECLARE duplicate_entry CONDITION FOR 1062;
		DECLARE EXIT HANDLER FOR duplicate_entry SET @pro_value = -1;
		SET @x = 1;
		INSERT INTO departments(department_name) VALUES('测试');
		SET @x = 2;
		INSERT INTO departments(department_name) VALUES('测试');
		SET @x = 3;
	END //
DELIMITER ;

#调用
CALL InsertDataWithCondition();

SELECT @x,@pro_value;

练习

#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION get_count() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;#定义局部变量
    SELECT COUNT(*) INTO c#赋值
    FROM employees;
    RETURN c;
END //
DELIMITER ;
#调用
SELECT get_count();

#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
    SET @sal=0;#定义用户变量
    SELECT salary INTO @sal #赋值
    FROM employees
    WHERE last_name = emp_name;
    RETURN @sal;
END //
DELIMITER ;
#调用
SELECT ename_salary('Abel');

#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
DELIMITER //
CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE
BEGIN
    DECLARE avg_sal DOUBLE ;
    SELECT AVG(salary) INTO avg_sal
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name=dept_name;
    RETURN avg_sal;
END //
DELIMITER ;
#调用
SELECT dept_sal('Marketing');

#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)
RETURNS FLOAT
BEGIN
    DECLARE SUM FLOAT;
    SET SUM=value1+value2;
    RETURN SUM;
END //
DELIMITER ;

#调用
SET @v1 := 12.2;
SET @v2 = 2.3;
SELECT add_float(@v1,@v2);