MySQL基础-变量/流程控制/游标/触发器-阿里云开发者社区

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、用户变量

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

定义用户变量：

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

定义局部变量：

定义：可以使用 DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN … END 中有效
位置：只能放在 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 语句的执行顺序，是我们完成复杂操作必不可少的一部分

只要是执行的程序，流程就分为三大类：

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类：

条件判断语句：IF 语句和 CASE 语句（类似switch语句）
循环语句：LOOP（类似while(true)语句）、WHILE （while循环）和 REPEAT 语句（do-while语句）
跳转语句：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]

对比三种循环语句:

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称
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、概念

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

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

游标优点缺点：

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

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 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录

创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。这样一来，就不用担心因为忘记添加库存数据而导致的数据缺失了

1、触发器概念

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序
触发器是由事件来触发某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作
当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时（例如执行添加日志等），可以使用触发器来实现

2、触发器使用

创建触发器：

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

表名：表示触发器监控的对象
BEFORE|AFTER ：表示触发的时间，BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发
INSERT|UPDATE|DELETE ：表示触发的事件
INSERT 表示插入记录时触发/UPDATE 表示更新记录时触发/DELETE 表示删除记录时触发
当执行语句块只有一个动作时可以不用添加BEGIN-END块

案例：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');
END //
DELIMITER ;
#或者 触发单一动作
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
INSERT INTO test_trigger_log (t_log)
VALUES('before_insert');

查看数据库触发器信息：

#查看当前数据库的所有触发器的定义  
SHOW TRIGGERS\G
#查看当前数据库中某个触发器的定义  
SHOW CREATE TRIGGER 触发器名
#从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息  
SELECT * FROM information_schema.TRIGGERS;

删除触发器：

DROP TRIGGER IF EXISTS 触发器名称;

3、触发器的优缺点

优点：

1、触发器可以确保数据的完整性

2、触发器可以帮助我们记录操作日志

3、触发器还可以用在操作数据前，对数据进行合法性检查

缺点：

1、触发器最大的一个问题就是可读性差

2、相关数据的变更，可能会导致触发器出错

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