MySql教程

MySQL变量、流程控制和游标

本文主要是介绍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文件中,用其中配置文件来覆盖默认的配置文件

这篇关于MySQL变量、流程控制和游标的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!