Mysql基础篇（8）—— 变量、流程控制和游标

变量

变量分为系统变量和用户自定义变量

系统变量

由系统定义属于服务器层面。这些系统变量定义了当前Mysql服务实例的属性、特征。系统变量分为全局系统变量需要加global关键字和会话级系统变量需要加session关键字如果不写默认会话级别。

查看系统变量

#查看所有全局变量
SHOW GLOBAL VARIABLES [LIKE '%标识符%'];
#查看所有会话变量
SHOW SESSION VARIABLES [LIKE '%标识符%']; 或 SHOW VARIABLES [LIKE '%标识符%'];

查看指定系统变量

#全局
SELECT @@global.变量名;
#会话
SELECT @@session.变量名; 或者 SELECT @@变量名;

修改系统变量

• 方式一修改Mysql的配置文件windows是my.inilinux是my.cnf重启服务
• 方式二在服务运行期间使用set命令重新设置系统变量。语法:

SET @@global.变量名=变量值;
SET GLOBAL 变量名=变量值;
SET @@session.变量名=变量值;
SET SESSION 变量名=变量值;

用户变量

用户变量是用户自己定义的分为会话用户变量和局部用户变量。

• 会话级用户变量在当前会话生效
• 局部变量只有BEGIN和END语块中生效

会话用户变量定义赋值

SET @变量名 = 变量值;
SET @变量名 := 变量值;
SELECT @变量名 := 表达式;如select @var := count(*) from t1
SELECT 表达式 into @变量名 [FROM 等子句];

局部用户变量定义赋值

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

定义条件与处理程序

定义条件就是给错误设置个别名处理程序就是定义在遇到错误时的处理方式。

定义条件

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。

举例:

定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。

DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
或者
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE ‘23000’;

处理程序

可以为SQL执行过程中发生的某种类型的vu哦呜定义特殊的处理程序。语法如下

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

• 处理方式处理方式有三个取值CONTINUE、EXIT、UNDO。
  • CONTINUE遇到错误不处理继续执行.
  • EXIT遇到错误推出执行。
  • UNDO遇到错误撤销执之前的操作。
• 错误类型
  • SQLSTATE ‘字符串错误码’ 表示长度为5的sqlstate_value类型的错误代码
  • MySQL_error_code 匹配数值类型错误代码
  • 错误名称 上面说的定义条件
  • SQLWARNING 匹配所有以01开头的SQLSTATE错误代码
  • NOT FOUND 匹配所有以02开头的SQLSTATE错误代码
  • SQLEXCEPTION 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码
• 处理语句如果出现上述条件之一则采用对应的处理方式并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的简单语句也可以是使用 BEGIN … END 编写的复合语句。

流程控制

作用就是控制sql语句的执行顺序。有以下三类

• 条件判断IF语句和CASE语句
• 循环语句LOOP、WHILE和REPEAT语句
• 跳转语句ITERATE和LEAVE语句

分支结构

IF

IF 返回布尔值的表达式 THEN 操作1
ELSEIF 返回布尔值的表达式 THEN 操作2
ELSE 操作3
END IF

CASE

#情况一类似switch
CASE 表达式
WHEN 值1 THEN 操作一
WHEN 值2 THEN 操作二
END CASE

循环结构

LOOP

loop_label:LOOP
#循环体[
	SET id = id + 1;
	IF id >= 10 THEN LEAVE loop_label;
	END IF;
#循环体]
END LOOP loop_label;

WHILE

WHILE 循环条件 DO
循环体
END WHILE;

REPEAT

REPEAT
	循环体
UNITL 结束循环的条件
END REPEAT;

跳转语句

LEAVE

用在循环语句内loop语法有写或者以BEGIN和END包裹的程序体内。可以理解为java的break

LEAVE 标记名;

ITERATE

只能用在循环语句类似java的continue;

游标

是一种临时的数据库对象可以指向存储在数据库表中的数据行指针。

游标的使用

• 第一步声明游标

DECLARE 游标名称(cursor_name) CURSOR FOR 查询语句(select * from test)

• 第二步打开游标

OPEN cursor_name;

• 第三步使用游标从游标中获取数据

FETCH cursor_name INTO 变量一(var_name1) ...

• 第四步关闭游标

CLOSE cursor_name

举例

创建存储过程“get_count_by_limit_total_salary()”声明IN参数 limit_total_salaryDOUBLE类型声明OUT参数total_countINT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值直到薪资总和达到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 ;

小结

游标是 MySQL 的一个重要的功能为 逐条读取 结果集中的数据提供了完美的解决方案。跟在应用层面实现相同的功能相比游标可以在存储程序中使用效率高程序也更加简洁。

但同时也会带来一些性能问题比如在使用游标的过程中会对数据行进行 加锁 这样在业务并发量大的时候不仅会影响业务之间的效率还会 消耗系统资源 造成内存不足这是因为游标是在内存中进行的处理。