欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

入门MySQL：变量、流程控制、游标与触发器的基础知识

最编程 2024-08-05 07:17:14
...

MySQL基础-变量/流程控制/游标/触发器


一、变量

网络异常，图片无法展示
|

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

在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量


1、系统变量

变量由系统定义，不是用户定义，属于 服务器 层面

启动MySQL服务，生成MySQL服务实例期间，MySQL将为MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征


  • 查看变量：
#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
#或 
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';


  • 修改系统变量的值：

方式1：修改MySQL 配置文件 ，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;
#为某个会话变量赋值
#方式1：
SET @@session.变量名=变量值;
#方式2：
SET SESSION 变量名=变量值;


2、用户变量

  1. 用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个“@” 开头
  2. 根据作用范围不同，又分为 会话用户变量 和 局部变量
  3. 会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效
  4. 局部变量：只在 BEGIN 和 END 语句块中有效，局部变量只能在 存储过程和函数 中使用


  • 定义用户变量：
#方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;
#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量 [FROM 等子句];


  • 定义局部变量：
  1. 定义：可以使用 DECLARE 语句定义一个局部变量
  2. 作用域：仅仅在定义它的 BEGIN … END 中有效
  3. 位置：只能放在 BEGIN … END 中，而且只能放在第一句
BEGIN
#声明局部变量
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
#为局部变量赋值
SET 变量名1 = 值;
SELECT 值 INTO 变量名2 [FROM 子句];
#查看局部变量的值
SELECT 变量1,变量2,变量3;
END


  • 案例：声明局部变量，并分别赋值为employees表中employee_id为102的last_name和salary
DELIMITER //
CREATE PROCEDURE set_value(IN emp_id INT)
BEGIN
  DECLARE emp_name VARCHAR(25);
  DECLARE sal DOUBLE(10,2);
  
  SELECT last_name,salary INTO emp_name,sal
  FROM employees
  WHERE employee_id=emp_id;
  
  SELECT emp_name,sal;
END //
DELIMITER ;


网络异常，图片无法展示
|


二、流程控制


流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分

  • 只要是执行的程序，流程就分为三大类：
  1. 顺序结构 ：程序从上往下依次执行
  2. 分支结构 ：程序按条件进行选择执行，从两条或多条路径中选择一条执行
  3. 循环结构 ：程序满足一定条件下，重复执行一组语句


  • 针对于MySQL 的流程控制语句主要有 3 类：
  1. 条件判断语句 ：IF 语句和 CASE 语句（类似switch语句）
  2. 循环语句 ：LOOP（类似while(true)语句）、WHILE （while循环）和 REPEAT 语句（do-while语句）
  3. 跳转语句 ：ITERATE（continue） 和 LEAVE （break）语句

注：只能用于存储程序

1、分支语句

  • IF 语句的语法结构是：
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF


  • CASE 语句的语法结构：
#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）
#情况二：类似于多重if
CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）


2、循环语句

LOOP语句的基本格式：

[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label]


  • 案例：声明存储过程“update_salary_loop()”，存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍，直到全公司的平均薪资达到12000结束，并统计循环次数
DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
DECLARE avg_salary DOUBLE;
DECLARE loop_count INT DEFAULT 0;
SELECT AVG(salary) INTO avg_salary FROM employees;

label_loop:LOOP
    IF avg_salary >= 12000 THEN LEAVE label_loop;
    END IF;
    UPDATE employees SET salary = salary * 1.1;
    SET loop_count = loop_count + 1;
    SELECT AVG(salary) INTO avg_salary FROM employees;
END LOOP label_loop;

SET num = loop_count;
END //
DELIMITER ;


  • WHILE语句的基本格式：
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];


  • REPEAT语句的基本格式：
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]


  • 对比三种循环语句:
  1. 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称
  2. LOOP：一般用于实现简单的"死"循环；WHILE：先判断后执行；REPEAT：先执行后判断，无条件至少执行一次


3、跳转语句

  • LEAVE语句：可以把 LEAVE 理解为 break
LEAVE 标记名
#label参数表示循环的标志


  • ITERATE语句：可以把 ITERATE 理解为 continue
ITERATE label


  • 案例：定义局部变量num，初始值为0，循环结构中执行num + 1操作，如果num < 10，则继续执行循环；如果num > 15，则退出循环结构
DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE num INT DEFAULT 0;
    my_loop:LOOP
        SET num = num + 1;
        IF num < 10
        THEN ITERATE my_loop;
        ELSEIF num > 15
        THEN LEAVE my_loop;
        END IF;
    END LOOP my_loop;
END //
DELIMITER ;


三、游标


1、概念

  1. 游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构，游标让 SQL 这种面向集合的语言有了面向过程开发的能力
  2. 在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标 充当了指针的作用 ，我们可以通过操作游标来对数据行进行操作


MySQL中游标可以在存储过程和函数中使用

  • 游标优点缺点：
  1. 游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案，跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁
  2. 但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理


2、使用

  • 声明游标：
DECLARE cursor_name CURSOR FOR select_statement;
#适用于 MySQL，SQL Server，DB2 和 MariaDB


注：select_statement 代表的是SELECT 语句，返回一个用于创建游标的结果集

  • 打开游标：
OPEN cursor_name


  • 使用游标：
FETCH cursor_name INTO var_name [, var_name] ...


注：使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可

  • 关闭游标：
CLOSE cursor_name

注：游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率


  • 案例：创建存储过程“get_count_by_limit_total_salary()”，函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到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 ;


四、触发器


在实际开发中，我们经常会遇到这样的情况：有 2 个或者多个相互关联的表，如 商品信息 和 库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录

创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，

上一篇： 玩转MySQL游标

下一篇： 玩转游标Cursor：第十九篇指南

推荐阅读