MySQL 存储过程、游标与触发器详解

2. 变量

2.1 系统变量

系统变量分为全局变量和会话变量。

• 会话变量：仅影响当前会话连接，每个客户端连接可以有自己的会话变量设置（默认从全局变量继承其初始值）。
• 全局变量：影响整个服务器运行环境，MySQL 服务器启动时从 my.ini 文件读取初始值。

-- 查询全局/会话变量
SHOW [GLOBAL|SESSION] VARIABLES [LIKE ...];
SELECT @@[GLOBAL|SESSION].系统变量名;
-- 修改全局/会话变量
SET [GLOBAL|SESSION] 系统变量名 = 值;
SET @@[GLOBAL|SESSION].系统变量名 = 值;

未指定 [GLOBAL|SESSION] 时默认查询/修改 SESSION 变量。上述修改操作仅为内存级修改，MySQL 服务器启动时从 my.ini 文件读取初始值。

2.2 用户变量

用户变量以 @ 符号开头，作用域为当前会话连接，不需要提前声明。常在存储过程中作为参数传递，用于存储查询中间结果。

-- 设置用户变量
SET @user_demo = 值;
-- 查询用户变量
SELECT @user_demo;
-- 设置并查询用户变量
SELECT @user_demo := 值;

2.3 局部变量

局部变量仅存在于存储过程、函数、触发器中，使用 DECLARE 声明。

DELIMITER //
CREATE PROCEDURE IF NOT EXISTS query()
BEGIN
    DECLARE num INT DEFAULT 0;
    SET num := 100;
    SELECT num;
END//
DELIMITER ;
CALL query();

3. SQL 编程

3.1 参数列表 / IF

IF 语法：

IF 条件 1 THEN
    -- sql 语句 1
ELSEIF 条件 2 THEN
    -- sql 语句 2
ELSE
    -- sql 语句 3
ENDIF;

示例：

-- 示例 1
DELIMITER //
CREATE PROCEDURE demo_if(IN score INT, OUT result VARCHAR(20))
BEGIN
    IF score >= 90 THEN
        SET result := '优秀';
    ELSEIF score >= 80 AND score < 90 THEN
        SET result = '良好';
    ELSEIF score >= 70 AND score < 80 THEN
        SET result = '及格';
    ELSE
        SET result = '不及格';
    ENDIF;
END//
DELIMITER ;
CALL demo_if(60, @result);
SELECT @result;

-- 示例 2
SET @score := 70;
DELIMITER //
CREATE PROCEDURE demo2_if(INOUT score INT)
BEGIN
    SET score := score + 10;
END//
DELIMITER ;
CALL demo2_if(@score);
SELECT @score;

3.2 CASE

语法：

-- 简单 CASE 表达式：将一个表达式与一组简单值进行比较
CASE case_value WHEN when_value1 THEN result1 WHEN when_value2 THEN result2 ... ELSE else_result END;

-- 搜索 CASE 表达式：将一个表达式与一组独立的布尔表达式进行比较
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE else_result END;

示例：

-- 示例 1
DELIMITER //
CREATE PROCEDURE demo_case(IN code INT, OUT result VARCHAR(50))
BEGIN
    CASE code
        WHEN 0 THEN SET result := '成功';
        WHEN 10001 THEN SET result := '用户名或密码错误';
        WHEN 10002 THEN SET result := '没有权限';
        WHEN 20001 THEN SET result := '传入参数有误';
        WHEN 20002 THEN SET result := '无结果';
        ELSE SET result := '内部错误';
    END CASE;
END//
DELIMITER ;
CALL demo_case(10002, @result);
SELECT @result;

-- 示例 2
DELIMITER //
CREATE PROCEDURE demo2_case(IN month INT, OUT result VARCHAR(50))
BEGIN
    CASE WHEN month >= 1 AND month <= 3 THEN SET result = '第一节度';
         WHEN month >= 4 AND month <= 6 THEN SET result = '第二节度';
         WHEN month >= 7 AND month <= 9 THEN SET result = '第三节度';
         WHEN month >= 10 AND month <= 12 THEN SET result = '第四节度';
         ELSE SET result = '非法输入';
    END CASE;
END//
DELIMITER ;
CALL demo2_case(7, @result);
SELECT @result;

3.3 WHILE

语法：

WHILE search_condition DO
    statement_list
END WHILE;

示例：

DELIMITER //
CREATE PROCEDURE demo_while(IN n INT)
BEGIN
    DECLARE sum INT DEFAULT 0;
    WHILE n > 0 DO
        SET sum := sum + n;
        SET n := n - 1;
    END WHILE;
    SELECT sum;
END//
DELIMITER ;
CALL demo_while(100);

3.4 REPEAT

先执行一次 statement_list，再判断 search_condition 是否成立。

语法：

REPEAT
    statement_list
UNTIL search_condition
END REPEAT;

示例：

DELIMITER //
CREATE PROCEDURE demo_repeat(IN n INT)
BEGIN
    DECLARE sum INT DEFAULT 0;
    REPEAT
        SET sum := sum + n;
        SET n := n - 1;
    UNTIL n <= 0
    END REPEAT;
    SELECT sum;
END//
DELIMITER ;
CALL demo_repeat(100);

3.5 LOOP

语法：

loop_label: LOOP
    statement_list
END LOOP loop_label;

控制 LOOP 循环的关键字：

• LEAVE：用于退出循环，相当于其他语言中的 BREAK。
• ITERATE：用于跳过当前迭代，继续下一次循环，相当于其他语言中的 CONTINUE。

示例：

-- 求 1-100 所有偶数的和
DELIMITER //
CREATE PROCEDURE demo_loop(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0;
    sum_loop: LOOP
        IF n <= 0 THEN LEAVE sum_loop; END IF;
        IF n % 2 = 1 THEN SET n := n - 1; ITERATE sum_loop; END IF;
        SET total := total + n;
        SET n := n - 1;
    END LOOP sum_loop;
    SELECT total;
END//
DELIMITER ;
CALL demo_loop(100);

3.6 游标 & 条件处理程序

3.6.1 游标

游标 (cursor)： 是一种数据库对象，用于在存储过程或函数中逐行处理查询结果集。当游标遍历到结果集末尾时，会触发 NOT FOUND 状态，需要结合条件处理程序结束循环，避免无限执行。

语法：

-- 声明游标
DECLARE cursor_name CURSOR FOR 查询语句;
-- 打开游标
OPEN cursor_name;
-- 获取游标记录
FETCH cursor_name INTO 变量列表;

3.6.2 条件处理程序

条件处理程序： 用于捕获和处理 SQL 执行过程中的异常或特定状态，需与游标配合使用。

语法：

DECLARE handler_type HANDLER FOR condition statement;

• handler_type：条件处理程序类型
  1. CONTINUE：继续执行后续语句
  2. EXIT：终止当前 BEGIN...END 块
• condition：指定了处理程序会响应的条件类型
  1. mysql_error_code：MySQL 错误码
  2. SQLSTATE sqlstate_value：表示特定的 SQL 状态码
  3. NOT FOUND：捕获所有以'02'开头的 sqlstate 代码
  4. SQLWARNING：捕获所有以'01'开头的 sqlstate 代码的警告
  5. SQLEXCEPTION：捕获所有不以'00'(成功)、'01'(警告)、'02'(未找到) 开头的 sqlstate 代码的错误
• statement：SQL 语句

3.6.3 示例

初始化数据：

-- 创建班级表
CREATE TABLE class (id INT PRIMARY KEY);
-- 创建学生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sno VARCHAR(10),
    age INT,
    gender TINYINT DEFAULT 1,
    enroll_date DATETIME DEFAULT NOW(),
    class_id INT,
    FOREIGN KEY(class_id) REFERENCES class(id)
);
-- 向班级表插入数据
INSERT INTO class VALUES(1),(2);
-- 向学生表插入数据
INSERT INTO student (name,sno,age,class_id) VALUES('刘备','100001',18,1),('关羽','100002',18,1),('张飞','100003',18,2),('赵云','100004',18,2);

示例：

DELIMITER //
CREATE PROCEDURE demo_cursor(IN input_class_id INT)
BEGIN
    -- 声明变量用于接收每一列的结果
    DECLARE student_name VARCHAR(20);
    DECLARE class_id INT;
    DECLARE flg BOOL DEFAULT TRUE;
    
    -- 声明游标用于接收查询结果
    DECLARE demo_cursor CURSOR FOR
    SELECT s.name AS student_name, c.id AS id 
    FROM student AS s, class AS c 
    WHERE s.class_id = c.id AND s.class_id = input_class_id;
    
    -- 声明处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET flg := FALSE;
    
    -- 创建新表
    CREATE TABLE class_new (
        id INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(20),
        class_id INT,
        FOREIGN KEY(class_id) REFERENCES class(id)
    );
    
    -- 开启游标
    OPEN demo_cursor;
    
    read_loop: LOOP
        FETCH demo_cursor INTO student_name, class_id;
        IF NOT flg THEN LEAVE read_loop; END IF;
        INSERT INTO class_new VALUES(NULL, student_name, class_id);
    END LOOP read_loop;
    
    -- 关闭游标
    CLOSE demo_cursor;
END//
DELIMITER ;
CALL demo_cursor(1);

运行结果：

3.7 存储函数

存储函数： 是一种存储在数据库中的可重用代码块，接收参数并返回一个值。与存储过程不同，存储函数必须包含返回值，且可以使用 SELECT 直接调用。

语法：

CREATE FUNCTION 存储函数名 (参数列表) RETURNS 返回值类型 [characteristic]

characteristic： 可选特性

• DETERMINISTIC：表明函数对于相同的输入参数总是返回相同的结果
• NOT DETERMINISTIC：默认选项，表示函数可能对相同的输入返回不同的结果
• NO SQL：表示函数不包含任何 SQL 语句
• READS SQL DATA：表示函数包含读取数据的 SQL 语句 (SELECT)
• MODIFIES SQL DATA：表示函数包含修改数据的 SQL 语句 (INSERT/UPDATE/DELETE)
• CONTAINS SQL：表示函数仅包含读取数据的 SQL 语句，不修改数据

示例：

CREATE FUNCTION func1(n INT) RETURNS INT DETERMINISTIC
BEGIN
    DECLARE time INT DEFAULT 0;
    WHILE n > 0 DO
        SET time := time + n;
        SET n := n - 1;
    END WHILE;
    RETURN time;
END;
SELECT func1(100);

4. 触发器

触发器： 一种与表相关的数据库对象，在指定事件 (如 INSERT、UPDATE、DELETE) 发生时自动执行预定义的 SQL 语句。

触发器类型：

1. 从触发时机区分
   • BEFORE 触发器：在触发事件执行前激活
   • AFTER 触发器：在触发事件完成后再执行
2. 从触发事件区分
   • INSERT 触发器：响应数据插入操作
   • UPDATE 触发器：响应数据更新操作
   • DELETE 触发器：响应数据删除操作
3. 从作用粒度区分
   • 行级触发器：针对受影响的每一行数据都会触发一次
   • 语句级触发器：整个 SQL 语句执行完毕后仅触发一次 (MySQL 暂不支持)

触发器中的 NEW 和 OLD

• NEW：表示触发事件中的新数据
• OLD：表示触发事件前的旧数据

语法：

CREATE TRIGGER 触发器名 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

初始化数据：

-- 创建日志表
CREATE TABLE log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    operation_type VARCHAR(20) NOT NULL COMMENT '操作类型',
    operation_time DATETIME NOT NULL COMMENT '操作时间',
    operation_id INT NOT NULL COMMENT '操作的记录的 id',
    operation_date VARCHAR(500) COMMENT '操作数据'
);

4.1 INSERT 触发器

-- insert 触发器
CREATE TRIGGER trg_insert AFTER INSERT ON student FOR EACH ROW
BEGIN
    INSERT INTO log VALUES(NULL, 'insert', NOW(), NEW.id, CONCAT(NEW.id,',',NEW.name,',',NEW.sno,',',NEW.age,',',NEW.gender,',',NEW.enroll_date,',',NEW.class_id));
END;
-- 向学生表插入数据
INSERT INTO student VALUES(NULL, '马超', 200001, 18, 1, NOW(), 1);

执行结果：

4.2 UPDATE 触发器

-- update 触发器
CREATE TRIGGER trg_update AFTER UPDATE ON student FOR EACH ROW
BEGIN
    INSERT INTO log VALUES(NULL, 'update', NOW(), NEW.id, CONCAT(' 更新前数据： ',OLD.id,',',OLD.name,',',OLD.sno,',',OLD.age,',',OLD.gender,',',OLD.enroll_date,',',OLD.class_id,' ,更新后数据： ',NEW.id,',',NEW.name,',',NEW.sno,',',NEW.age,',',NEW.gender,',',NEW.enroll_date,',',NEW.class_id));
END;
-- 更新学生表的数据
UPDATE student SET age = 20, enroll_date = NOW(), class_id = 2 WHERE name = '马超';

执行结果：

4.3 DELETE 触发器

-- delete 触发器
CREATE TRIGGER trg_delete AFTER DELETE ON student FOR EACH ROW
BEGIN
    INSERT INTO log VALUES(NULL, 'delete', NOW(), OLD.id, CONCAT('删除的数据： ',OLD.id,',',OLD.name,',',OLD.sno,',',OLD.age,',',OLD.gender,',',OLD.enroll_date,',',OLD.class_id));
END;
DELETE FROM student WHERE name = '马超';

执行结果：