MySQL变量、流程控制和游标

database

变量、流程控制和游标

变量

在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文件中,用其中配置文件来覆盖默认的配置文件

以上是 MySQL变量、流程控制和游标 的全部内容, 来源链接: utcz.com/z/536368.html

回到顶部