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服务实例的属性、特征

具体方法：

• 方法一：修改配置文件，修改后重启服务(如果服务器已经运行则没有意义)
  
  [En]

  method 1: modify the configuration file, and restart the service after modification (it is meaningless if the server is already running)*

• 方法二：在运行期间，使用 SET 指令

#为某个系统变量赋值：

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

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

用户变量

用户变量的分类

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

• SESSION用户变量：作用域与SESSION变量相同，仅对当前连接会话有效
  
  [En]

  session user variable: the scope is the same as the session variable, which is only valid for the current connection session*

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

会话用户变量

• 变量的定义与赋值

#方式一：:=或=SET @变量名 := 值;SET @变量名 = 值;#方式二：:=或INTOSELECT @变量名 := 表达式[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语句的执行顺序。

只要它是一个程序，这个过程就分为三类

• 顺序结构：从上到下执行
• 分支结构：按给定条件执行。从这两个选项中选择一个或多个。
  
  [En]

  Branch structure: execute according to the given conditions. Choose one or more of the two.*

• 循环结构：在一定条件下执行一组语句
  
  [En]

  Loop structure: execute a set of statements under certain conditions*

针对于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

游标小结

• 优点

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

它可以用在存储程序中，效率高，程序简单。

• 不足

它会带来一些性能问题，使用游标会锁定数据，当业务并发较大时会浪费系统资源(所以养成关机的习惯)

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

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

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

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

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

Original: https://www.cnblogs.com/wht-de-bk/p/16031615.html
Author: T,a,o
Title: MySQL变量、流程控制和游标