MySQL变量、流程控制和游标

变量、流程控制和游标

变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果的数据

系统变量

变量由系统定义，属于服务器层面

系统变量的分类

每一个MySQL客户机成功连接服务器后，都会产生与之对应的会话（建立一次连接相当于一次会话）。MySQL服务实例会在服务器内存中生成与该会话对应的系统变量，他们的初值都是全局系统变量值的复制

  • 全局变量（global）

修改针对所有的会话有效，但不能跨重启（重启后修改值全面恢复默认值）

  • 会话变量（session）

修改针对当前的会话有效，不会影响其他同一会话系统变量的值

注

如果不写关键字，则默认会话级别

有些系统变量只是全局，有些只是会话，有些既是全局又是会话

查看系统变量

  • 查看所有或部分系统变量
#所有

#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或是
SHOW VARIABLES;

查询效果：

#部分

#查看部分会话变量
SHOW GLOBAL VARIABLES LIKE '模糊查询';
#查看部分会话变量
SHOW SESSION VARIABLES LIKE '模糊查询'；
  • 查看指定系统变量

MySQL中是以两个@开始；@@global是全局，@@session是会话 \ 既全局又会话。@@符首先标记会话变量，要是没有会话变量，则标记系统变量

#查看指定的系统变量
SELECT @@global.变量名;
#查看指定会话变量
SELECT @@session.变量名;
或是
SELECT @@变量名;

修改系统变量

有时要修改系统变量的值，以便修改当前会话或是MySQL服务实例的属性、特征

具体方法：

  • 方法一：修改配置文件，修改后要重启服务(服务器如果已经运行，则毫无意义)

  • 方法二：在运行期间，使用 SET 指令
#为某个系统变量赋值：

#全局系统变量：针对当前数据库实例是有效的，一旦重启服务器，就失效了
#方式一：
SET @@global.变量名 = 要赋的值;
#方式二：
SET GLOBAL 变量名 = 要赋的值;

#会话系统变量：针对当前会话是有效的，一旦建立起新的会话，就失效了
#方式一：
SET @@session.变量名 = 要赋的值;
#方式二：
SET SESSION 变量名 = 要赋的值;

用户变量

用户变量的分类

用户变量是用户自定义。在MySQL以一个'@'开头(主要修饰会话用户变量)

  • 会话用户变量：作用域和会话变量一样，只对当前来连接会话有效

  • 局部变量：只在BEGIN和END中有效，只在存储过程和函数中使用

会话用户变量

  • 变量的定义与赋值
#方式一：:=或=
SET @变量名 := 值;
SET @变量名 = 值;

#方式二：:=或INTO
SELECT @变量名 := 表达式[FROM 等句];
SELECT 表达式 INTO @变量名 [FROM 等句];
  • 使用
SELECT @变量名;

局部变量

定义：使用 DECLARE 去定义一个局部变量

作用域：仅在它的 BEGIN...END 中有效

位置：只能放在 BEGIN...END 中，且在第一句

  • 变量定义
DECLARE 变量名 类型 [default 值]#如果没有默认值，则初始值null
  • 赋值
SET 变量名 = 值；
SET 变量名 := 值；

SELECT 变量名 := 表达式[FROM 等句];
SELECT 表达式 INTO 变量名 [FROM 等句];
  • 使用
SELECT 局部变量名;

举个例子：

delimiter $
create procedure test_var()
begin 
      declare a int default 0;
      declare b int default 0;
	  #declare a,b int default 0;
      declare emp_name varchar(15);
      
      set a = 1,b := 2; 
      select name into emp_name from emp1 where id = 3;
      select a,b,emp_name;
end $
delimiter ;
call test_var();

对比会话用户与局部变量

定义条件与处理程序（异常处理）

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

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

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

定义条件

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

语法格式：

DECLARE 错误名字 CONDITION FOR 错误码或错误条件(数值类型的错误码);
或是
DECLARE 错误名字 CONDITION FOR sqlstate '(字符串类型的错误代码)';

错误码说明：

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

MySQL_error_code：数值类型错误代码

sqlstate_value：长度为s的字符串类型的错误代码

举个例子

DECLARE Field_Not_Be_NULL CONDITION FOR 1048;
        #错误名字                       错误码

定义处理程序

可以为MySQL执行过程中发生的某种类型的错误定义特殊的处理程序。(在一开头就编写)

语法格式：

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

CONTINUE:遇到错误不处理，继续执行

EXIT:遇到错误马上退出

UNDD:遇到错误后撤回之前的操作，MySQL暂时不支持这样的操作

  • 错误类型（条件）：

SQLSTATE'字符串错误码'：长度是s的字符串类型错误码

MySQL_error_code:数值类型错误码

错误名称：定义条件起的名字

SQLWARNING:匹配01开头的SQLSTATE错误码

NOT FOUND:匹配02开头的SQLSTATE错误码

SQLEXCEPTION:匹配既不是SQLWARNING，也不是NOT FOUND的SQLSTATE类型错误码

  • 处理语句

简单句：SET 变量 = 值;

复杂句：BEGING ... END;

举个例子

DECLARE CONTINUE HANDLER FOR 1048 SET @error = -1;
      #处理方式：继续         错误码    让error = 1

流程控制

类比其他语言的流程控制（我这里会写的简单一点），在执行语句时记得加上WHERE来限制

控制存储过程中SQL语句的执行顺序。

只要是程序，流程就分为三大类

  • 顺序结构：从上到下执行
  • 分支结构：按给出的条件执行，二选一或是多选一
  • 循环结构：在一定条件下，执行一组语句

针对于MySQL，的流程控制主要有三类（只用于存储程序）

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

分支结构1（IF）

语句中可以没有else

语法格式;

IF 表达式1 THEN 操作1
ELSEIF 表达式2 THEN 操作2
...
ELSE 操作N
END IF;

分支结构2（CASE）

语法格式：

#情况一：
CASE 表达式
WHEN 值1 THEN 操作1;
WHEN 值2 THEN 操作2;
...
ELSE 操作N;
END[case];#BEGIN END 中要加case

#情况二：
CASE 
WHEN 条件1 THEN 操作1;
WHEN 条件2 THEN 操作2;
...
ELSE 操作N;
END[case];#BEGIN END 中要加case

循环结构1（LOOP）

循环语句有四个条件：

  • 初始条件
  • 循环条件
  • 循环体
  • 迭代条件

LOOP内语句一直重复执行，知道循环退出（使用LEAVE子句）

语法格式：

[loop_label:] LOOP
    循环体;
END LOOP [loop_label];

举个例子：

#从1一每次加1直到为10输出
BEGIN
#初始化
SET a int default 1;
loop_label:LOOP
    #循环主体（此时省略了，程序太过于简单）
    
    #迭代条件
    SET a = a + 1;
    #循环条件
    IF a >= 10 THEN LEAVE loop_label;
    END IF;
    
END LOOP loop_label;

END $

循环结构2（WHILE）

while不控制循环：while true

语法格式：

[while_label:]WHILE 循环条件 DO 
        循环体;
END WHILE [while_label];

循环结构3（REPEAT）

类似于DO WHILE，至少执行一次

语法格式：

[repeat_label:]REPEAT
        循环体;
UNTILL 结束循环语句 #没有;
END REPEAT [repeat_label];

跳转语句1（LEAVE）

类似于break，用在循环语句内或是 在BEGIN ...END 中使用，可以跳出循环体或是程序

要跳出谁，给谁加标签

语法格式：

LEAVE 标签名;

举个例子：

 #部分代码不完整
 SET a = a + 1;
 IF a >= 10 THEN LEAVE loop_label;
                         #标签名
 END IF;

跳转语句2（ITERATE）

类似于 continue ,只能在循环语句中使用，跳过本次循环，进入下一次循环

语法格式：

ITERATE 标签名;

游标

什么是游标（光标）

可以定位指定的记录并可以对其操作（充当指针）

使用游标的步骤

游标必须在声明处理程序之前声明，并且变量和条件也必须在声明游标或是处理程序之前声明

  • 第一步：声明游标

MySQL中，使用DECLARE关键字来声明游标，语法格式如下：

DECLARE 游标名 CURSOR FOR 查询语句（结果集）;

如果是Oracle或是PostgreSQL中，语法格式如下：

DECLARE 游标名 CURSOR IS 查询语句（结果集）;
  • 第二步：打开游标
OPEN 游标名;
  • 第三步：使用游标
FETCH 游标名 INTO 查询结果的字段1,字段2,...（要一一对应，名字之间要有关联，这样易调用）;
#让游标读取当前行，游标指针指向下一行
  • 第四步：关闭游标
CLOSE 游标名;

如果不及时关闭，游标会占用系统资源，影响系统运行效率

举个例子：

#给出一个工资总和的上限数，让所有员工的工资进行降序排列，并依次相加，直到相加的工资总数大于所给的工资总上限数，计算相加人数

#部分代码（存储过程未写）
上限数：limit_total_salry double(in)
相加人数：total_count int(out)

#声明局部变量（保存每个人的工资，保存相加的数,保存相加人数）：
DECLARE emp_sal double;
DECLARE sum_sal int default 0;
DECLARE emp_count int default 0;

#声明游标
DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; 

#打开游标
OPEN emp_cursor;

#使用游标
WHILE sum <= limit_total_salry DO
      FETCH emp_cursor INTO emp_sal;
      SET sum_sal = emp_sal + sum_sal;
      emp_count = emp_count + 1;
END WHILE;

#赋值相加人数
SET total_count = emp_count;

#关闭游标
CLOSE emp_cursor;

游标小结

  • 优点

游标为逐条读取数据提供了解决方案。

可以在存储程序中使用，效率高，程序也会更简洁

  • 不足

会带来一些性能的问题，使用游标会对数据加锁，在业务并发量大的时候，会损耗系统资源（所以要养成关闭的习惯）

补充：MySQL8.0的新特性—全局变量的持久化

重启服务器后，修改的全局变量依旧有效

MySQL8.0新增了 SET PERSIST 命令，格式如下：

SET PERSIST global 全局变量名字 = 1000;

MySQL会将给命令的配置保存到数据目录下的mysql-auto.cnf文件中，用其中配置文件来覆盖默认的配置文件

posted @ 2022-03-20 21:24  T,a,o  阅读(231)  评论(0)  编辑  收藏  举报