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

MySQL 存储过程、游标与触发器详解 | 极客日志

-- 修改 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 = '数据库名';

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

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

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

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

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;

-- 简单 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;

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

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

loop_label: LOOP
    statement_list
END LOOP loop_label;

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

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

DECLARE handler_type HANDLER FOR condition statement;

-- 创建班级表
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

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

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

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 '操作数据'
);

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

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

-- 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 触发器