MsSQL 变量、定义条件与处理程序、游标

变量、定义条件与处理程序、游标

变量

系统变量

MySQL中的系统变量以@@开头

系统变量分为

• 全局系统变量（需要添加 global 关键字）

  • 对于所有会话（连接）有效，但不能跨重启

    MySQL 8.0版本新增了SET PERSIST命令，可以用它跨重启。

    MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

    SET PERSIIST global max_connections =1000;
    -- 设置服务器的最大连接数为1000
    

  • 有时也把全局系统变量简称为全局变量

• 会话系统变量（需要添加 session 关键 字）默认

  • 仅对于当前会话（连接）有效，不会影响其他会话的系统变量的值
  • 有时也把会话系统变量称为local变量

查看

-- 查看所有全局系统变量
SHOW GLOBAL VARIABLS;

-- 查看所有会话系统变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;-- 默认查询的是会话系统变量


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

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

查看指定的系统变量

-- 指定的全局系统变量的值
SELECT @@global.变量名;

-- 指定的会话系统变量的值
SELECT @@session.变量名;
或
SELECT @@变量名;-- 默认查看会话系统变量

修改

1. 修改MySQL的配置文件，继而修改MySQL系统变量的值（需重启MySQL）
2. 使用“set”命令重新设置系统变量的值

-- 系统变量
-- 重启后修改失效
SET @@global.变量名=变量值;
或
SET GLOBAL 变量名=变量值;

-- 会话变量
-- 仅对当前会话有效
SET @@session.变量名=变量值;
或
SET SESSION 变量名=变量值;

用户变量

MySQL中用户变量以@开头

用户变量可分为

• 会话用户变量
  • 作用域和会话变量一样，只对当前连接会话有效
• 局部变量(DECLARE定义)
  • 仅仅在定义它的 BEGIN … END 中有效,只能放在第一句，只能在存储过程和函数中使用

会话用户变量

作用域和会话系统变量一样，只对当前连接会话有效

-- 定义
SET @用户变量 = 值;
SET @用户变量 := 值;

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

-- 查看
SELECT @用户变量;

局部变量

仅仅在定义它的 BEGIN … END 中有效,只能放在 BEGIN … END 中，而且只能放于段首

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

对比

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

定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题

处理程序定义了在遇到问题时应当采取的处理方 式，并且保证存储过程或函数在遇到警告或错误时能继续执行

定义条件和处理程序在存储过程、存储函数中都是支持的

定义条件

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

DECLARE 错误名称 CONDITION FOR 错误代码(或错误条件)

• MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误

  • MySQL_error_code是数值类型错误代码

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

  • ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'data' at line 1
    -- 此处1064为MySQL_error_code
    -- 4200为sqlstate_value
    

处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

• 处理方式
  • CONTINUE:表示遇到错误不处理，继续执行
  • EXIT:表示遇到错误马上退出
  • UNDO:表示遇到错误后撤回之前的操作（MySQL暂不支持）
• 错误类型
  • SQLSTATE‘字符串错误码’:表示长度为5的sqlstate_value类型的错误代码
  • MySQL_error_code:匹配数值类型错误代码
  • 错误名称:表示DECLARE…CONDITION定义的错误名称
  • SQLWARNING：匹配所有以01开头的SQLSRARE错误代码
  • NOT FOUND：匹配所有以02开头的SQLSRARE错误代码
  • SQLEXCEPITION:匹配所有没有被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';

创建一个名称为“InsertDataWithCondition”的存储过程，代码如下。 在存储过程中，定义处理程序，捕获sqlstate_value值，当遇到sqlstate_value值为23000时，执行EXIT操 作，并且将@proc_value的值设置为-1

-- 准备工作
CREATE TABLE departments
AS
SELECT * FROM atguigudb.`departments`;
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE(department_id);

DELIMITER //
CREATE PROCEDURE InsertDataWithCondition()
BEGIN
DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ;
DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_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 ;

-- 调用
mysql> CALL InsertDataWithCondition();
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @x,@proc_value;
+------+-------------+
| @x   | @proc_value |
+------+-------------+
| 2    |      -1     |
+------+-------------+
1 row in set (0.01 sec)

游标

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

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

优点：为逐条读取结果集中的数据，提供了完美的解决方案。在存储过程中使用，效率高，程序也更加简洁

缺点：会对数据行加锁，这样的业务并发量大的时候，会消耗系统资源，造成内存不足，养成用完之后就关闭的习惯，这样才能提高系统的整体效率

声明

DECLARE cursor_name CURSOR FOR select_statement;
-- 适用于 MySQL，SQL Server，DB2 和 MariaDB

DECLARE cursor_name CURSOR IS select_statement;
-- 适用于 Oracle 或者 PostgreSQL

打开

OPEN cursor_name

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

OPEN cur_emp;

使用

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

游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时 候，MySQL 会提示错误。

关闭

游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率

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_sal DOUBLE DEFAULT 0.0;-- 累加的工资总额
	DECLARE emp_sal DOUBEL; -- 记录每个员工的工资
	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 emp_sal;-- 使用游标
		
		-- 迭代条件
		SET sum_sal = sum_sal + emp_sal;
		
		SET emp_count = emp_count +1;
		
		-- 循环条件
		UNTIL sum_sal>=limit_total_salary
	END REPEAT;
	SET total_count=emp_count;-- 返回值
	CLOSE emp_cusor;-- 关闭游标
END//
DELIMITER;

-- 调用
SET @total_count=0;
CALL get_count_by_limit_total_salary(任意值,@total_count);
SELECT @total_count;