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

1. 存储过程

概念： 存储过程是一组预编译的 SQL 语句集合，存储在数据库中，可通过名称调用。支持条件判断、循环等逻辑控制，适合封装复杂业务逻辑。

优势：

性能优化：存储过程在创建时已经经过编译和优化，形成可执行的二进制代码，直接存储在数据库服务器中。性能高于单独执行时需要解析、编译的 SQL 语句。

代码复用：存储过程可以重复调用（类似 C 语言函数/Java 方法）。

安全性高：避免应用程序直接访问数据库，通过存储过程间接访问（结合参数化查询），从而保证安全性。

降低耦合：当表结构发生变化时，只需要修改相应的存储过程，应用程序变动较小。

缺点：

移植性差：不同数据库创建存储过程的语法不同。

不适合高并发场景：高并发场景下，存储过程会进一步降低数据库的性能。

语法：

-- 修改 SQL 语句结束符为 DELIMITER
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE IF NOT EXISTS 存储过程名 (参数列表)
BEGIN
    -- SQL 语句
END//
-- 还原 SQL 语句结束符为 ;
DELIMITER ;
-- 调用存储过程
CALL 存储过程名 (参数列表);
-- 删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;
-- 查看当前数据库已创建的存储过程
SELECT * FROM information_schema.routines WHERE routine_schema = '数据库名';

在 MySQL 中，存储过程、函数等数据库对象的信息可以通过 information_schema 中的 routines 系统视图查询。这个视图包含所有数据库中的存储过程和函数的元数据信息。

routine_schema：所属的数据库名

routine_name：存储过程/函数名称

routine_type：对象类型 (procedure 或 function)

data_type：函数返回值类型，存储过程没有返回值

routine_definition：存储过程/函数的定义语句

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

类型	描述
IN	输入型参数，调用存储过程时传入，默认参数类型
OUT	输出型参数，可以作为存储过程的返回值
INOUT	输入输出型参数

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：条件处理程序类型

CONTINUE：继续执行后续语句

EXIT：终止当前 BEGIN...END 块

condition：指定了处理程序会响应的条件类型

mysql_error_code：MySQL 错误码

SQLSTATE sqlstate_value：表示特定的 SQL 状态码

NOT FOUND：捕获所有以'02'开头的 sqlstate 代码

SQLWARNING：捕获所有以'01'开头的 sqlstate 代码的警告

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);

运行结果：

id	student_name	class_id
1	刘备	1
2	关羽	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 语句。

触发器类型：

从触发时机区分

BEFORE 触发器：在触发事件执行前激活

AFTER 触发器：在触发事件完成后再执行

从触发事件区分

INSERT 触发器：响应数据插入操作

UPDATE 触发器：响应数据更新操作

DELETE 触发器：响应数据删除操作

从作用粒度区分

行级触发器：针对受影响的每一行数据都会触发一次

语句级触发器：整个 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);

执行结果：

id	operation_type	operation_time	operation_id	operation_date
1	insert	2025-10-31 17:25:17	9	9,马超，200001,18,1,2025-10-31 17:25:17,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 = '马超';

执行结果：

id	operation_type	operation_time	operation_id	operation_date
3	insert	2025-10-31 17:40:14	10	10,马超，200001,18,1,2025-10-31 17:40:14,1
4	update	2025-10-31 17:41:11	10	更新前数据：10,马超，200001,18,1,2025-10-31 17:40:14,1,更新后数据：10,马超，200001,20,1,2025-10-31 17:41:11,2

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 = '马超';

执行结果：

id	name	sno	age	gender	enroll_date	class_id
1	刘备	100001	18	1	2025-10-26 22:11:23	1
2	关羽	100002	18	1	2025-10-26 22:11:23	1
3	张飞	100003	18	1	2025-10-26 22:11:23	2
4	赵云	100004	18	1	2025-10-26 22:11:23	2

id	operation_type	operation_time	operation_id	operation_date
3	insert	2025-10-31 17:40:14	10	10,马超，200001,18,1,2025-10-31 17:40:14,1
4	update	2025-10-31 17:41:11	10	更新前数据：10,马超，200001,18,1,2025-10-31 17:40:14,1,更新后数据：10,马超，200001,20,1,2025-10-31 17:41:11,2
5	delete	2025-10-31 17:47:09	10	删除的数据：10,马超，200001,20,1,2025-10-31 17:41:11,2

1. 存储过程

概念： 存储过程是一组预编译的 SQL 语句集合，存储在数据库中，可通过名称调用。支持条件判断、循环等逻辑控制，适合封装复杂业务逻辑。

优势：

性能优化：存储过程在创建时已经经过编译和优化，形成可执行的二进制代码，直接存储在数据库服务器中。性能高于单独执行时需要解析、编译的 SQL 语句。

代码复用：存储过程可以重复调用（类似 C 语言函数/Java 方法）。

安全性高：避免应用程序直接访问数据库，通过存储过程间接访问（结合参数化查询），从而保证安全性。

降低耦合：当表结构发生变化时，只需要修改相应的存储过程，应用程序变动较小。

缺点：

移植性差：不同数据库创建存储过程的语法不同。

不适合高并发场景：高并发场景下，存储过程会进一步降低数据库的性能。

语法：

-- 修改 SQL 语句结束符为 DELIMITER
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE IF NOT EXISTS 存储过程名 (参数列表)
BEGIN
    -- SQL 语句
END//
-- 还原 SQL 语句结束符为 ;
DELIMITER ;
-- 调用存储过程
CALL 存储过程名 (参数列表);
-- 删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;
-- 查看当前数据库已创建的存储过程
SELECT * FROM information_schema.routines WHERE routine_schema = '数据库名';

在 MySQL 中，存储过程、函数等数据库对象的信息可以通过 information_schema 中的 routines 系统视图查询。这个视图包含所有数据库中的存储过程和函数的元数据信息。

routine_schema：所属的数据库名

routine_name：存储过程/函数名称

routine_type：对象类型 (procedure 或 function)

data_type：函数返回值类型，存储过程没有返回值

routine_definition：存储过程/函数的定义语句

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

类型	描述
IN	输入型参数，调用存储过程时传入，默认参数类型
OUT	输出型参数，可以作为存储过程的返回值
INOUT	输入输出型参数

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：条件处理程序类型

CONTINUE：继续执行后续语句

EXIT：终止当前 BEGIN...END 块

condition：指定了处理程序会响应的条件类型

mysql_error_code：MySQL 错误码

SQLSTATE sqlstate_value：表示特定的 SQL 状态码

NOT FOUND：捕获所有以'02'开头的 sqlstate 代码

SQLWARNING：捕获所有以'01'开头的 sqlstate 代码的警告

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);

运行结果：

id	student_name	class_id
1	刘备	1
2	关羽	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 语句。

触发器类型：

从触发时机区分

BEFORE 触发器：在触发事件执行前激活

AFTER 触发器：在触发事件完成后再执行

从触发事件区分

INSERT 触发器：响应数据插入操作

UPDATE 触发器：响应数据更新操作

DELETE 触发器：响应数据删除操作

从作用粒度区分

行级触发器：针对受影响的每一行数据都会触发一次

语句级触发器：整个 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);

执行结果：

id	operation_type	operation_time	operation_id	operation_date
1	insert	2025-10-31 17:25:17	9	9,马超，200001,18,1,2025-10-31 17:25:17,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 = '马超';

执行结果：

id	operation_type	operation_time	operation_id	operation_date
3	insert	2025-10-31 17:40:14	10	10,马超，200001,18,1,2025-10-31 17:40:14,1
4	update	2025-10-31 17:41:11	10	更新前数据：10,马超，200001,18,1,2025-10-31 17:40:14,1,更新后数据：10,马超，200001,20,1,2025-10-31 17:41:11,2

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 = '马超';

执行结果：

id	name	sno	age	gender	enroll_date	class_id
1	刘备	100001	18	1	2025-10-26 22:11:23	1
2	关羽	100002	18	1	2025-10-26 22:11:23	1
3	张飞	100003	18	1	2025-10-26 22:11:23	2
4	赵云	100004	18	1	2025-10-26 22:11:23	2

id	operation_type	operation_time	operation_id	operation_date
3	insert	2025-10-31 17:40:14	10	10,马超，200001,18,1,2025-10-31 17:40:14,1
4	update	2025-10-31 17:41:11	10	更新前数据：10,马超，200001,18,1,2025-10-31 17:40:14,1,更新后数据：10,马超，200001,20,1,2025-10-31 17:41:11,2
5	delete	2025-10-31 17:47:09	10	删除的数据：10,马超，200001,20,1,2025-10-31 17:41:11,2

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

1. 存储过程

2. 变量

2.1 系统变量

2.2 用户变量

2.3 局部变量

3. SQL 编程

3.1 参数列表 / IF

3.2 CASE

3.3 WHILE

3.4 REPEAT

3.5 LOOP

3.6 游标 & 条件处理程序

3.6.1 游标

3.6.2 条件处理程序

3.6.3 示例

3.7 存储函数

4. 触发器

4.1 INSERT 触发器

4.2 UPDATE 触发器

4.3 DELETE 触发器

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

1. 存储过程

2. 变量

2.1 系统变量

2.2 用户变量

2.3 局部变量

3. SQL 编程

3.1 参数列表 / IF

3.2 CASE

3.3 WHILE

3.4 REPEAT

3.5 LOOP

3.6 游标 & 条件处理程序

3.6.1 游标

3.6.2 条件处理程序

3.6.3 示例

3.7 存储函数

4. 触发器

4.1 INSERT 触发器

4.2 UPDATE 触发器

4.3 DELETE 触发器

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具