MySQL进阶学习四（存储过程、游标、触发器）

一、什么是存储过程

存储过程是一组预编译好的 SQL 语句集合，存储在数据库中，可通过名字调用。

• 预编译：一次编译，多次执行，效率高
• 封装逻辑：复杂业务逻辑封装在数据库端
• 可带参数：输入、输出、输入输出参数
• 可控制流程：支持条件、循环、异常处理

二、创建存储过程语法

DELIMITER $$
CREATE PROCEDURE 过程名 ([参数列表])
[特性...]
BEGIN
    -- SQL 语句
END $$
DELIMITER ;

关键点

部分	说明
DELIMITER $$	临时改分隔符，避免 ; 冲突
CREATE PROCEDURE	创建存储过程
参数列表	可选，IN / OUT / INOUT
BEGIN...END	过程体
DELIMITER ;	恢复分隔符

三、参数类型（重点）

类型	说明	示例
IN	输入参数（默认）	IN id INT
OUT	输出参数，可带回值	OUT count INT
INOUT	输入输出，可传入也可带回	INOUT num INT

-- 创建
create procedure 过程名(IN 参数名 类型, OUT 参数名 类型, INOUT 参数名 类型)
begin
    --sql语句
end;
-- 调用
call 过程名([参数列表]);

示例

-- 1. IN 参数
create procedure get_emp(IN emp_id INT)
begin
    SELECT * FROM employee WHERE id = emp_id;
end;

-- 2. OUT 参数
CREATE PROCEDURE count_emp(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employee;
END;

-- 3. INOUT 参数
CREATE PROCEDURE double_num(INOUT num INT)
BEGIN
    SET num = num * 2;
END;

查看存储过程

-- 查看所有
show procedure status;

-- 按数据库过滤
show procedure status where Db = '数据库名';

-- 查看创建语句
show create procedure 过程名;

-- 信息模式
select * from information_schema.ROUTINES where ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = '过程名';

修改与删除存储过程

-- 修改（MySQL 不支持直接 ALTER 过程体，只能先删后建）
drop procedure if exists 过程名;
create procedure 过程名() ...;

-- 删除
drop procedure 过程名;
drop procedure if exists 过程名;

MySQL 的 ALTER PROCEDURE 只能改特性（如注释、权限），不能改过程体。

变量

-- 查看
show variables;
show [session|global] variables; # 查看所有系统变量
show global variables like 'auto%';
select @@global.binlog_format;
-- 设置
set session binlog_format = ROW;
set @@global.binlog_format = ROW;
-- 用户自定义
set @var_my_sql = 'wangyanfei'; # 定义
select @var_my_sql := 'wangxiaobai'; # 定义
select count(*) into @var_my_sql from tb_emp; # 定义
select @var_my_sql; # 查看
-- 局部变量（只内部生效）
create procedure p3()
begin
    declare var_my_c int default 0; # 定义局部变量
    set var_my_c = 2; # 赋值方式一
    select count(*) into var_my_c from tb_emp; # 赋值方式二
    select var_my_c := 3; # 赋值方式三
    select var_my_c; # 查看
end;

流程控制语句

1. IF ... THEN ... ELSE

DELIMITER $$
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
    DECLARE sal DECIMAL(10,2);
    SELECT salary INTO sal FROM employee WHERE id = emp_id;
    
    IF sal > 10000 THEN
        SELECT '高薪';
    ELSEIF sal > 5000 THEN
        SELECT '中等';
    ELSE
        SELECT '低薪';
    END IF;
END $$
DELIMITER ;

2. CASE

CASE 变量
    WHEN 值1 THEN 语句1;
    WHEN 值2 THEN 语句2;
    ELSE 语句3;
END CASE;

DELIMITER $$
CREATE PROCEDURE p_case(IN level INT)
BEGIN
    CASE level
        WHEN 1 THEN SELECT 'VIP';
        WHEN 2 THEN SELECT '普通';
        ELSE SELECT '游客';
    END CASE;
END $$
DELIMITER ;

3. WHILE 循环

DELIMITER $$
CREATE PROCEDURE p_while(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= n DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

4. REPEAT 循环（先执行后判断）

DELIMITER $$
CREATE PROCEDURE p_repeat(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i > n
    END REPEAT;
END $$
DELIMITER ;

5. LOOP + LEAVE退出循环体（ITERATE退出当次循环进入下次循环）

DELIMITER $$
CREATE PROCEDURE p_loop(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    my_loop: LOOP
        IF i > n THEN
            LEAVE my_loop;      -- 退出循环
        END IF;
        SELECT i;
        SET i = i + 1;
    END LOOP my_loop;
END $$
DELIMITER ;

LEAVE 类似 break，ITERATE 类似 continue。

完整示例

delimiter $$
create procedure p2(in emp_id int, in percent decimal(5,2), out new_salary decimal(10, 2))
begin 
    declare old_salary decimal(10, 2);
    -- 查询原工资
    select salary into old_salary from tb_emp where id = emp_id;
    -- 计算新工资
    set new_salary = old_salary * (1 + percent / 100);
    -- 更新
    update tb_emp set salary = new_salary where id = emp_id;
end $$
delimiter ;

-- 调用
call p2(1, 10, @new_sal);
select @new_sal;

什么是游标

游标（Cursor）是指向查询结果集的指针，用于逐行处理查询结果。 适用场景：需要对查询结果每行做不同处理时。

游标使用四步法（核心）

DECLARE 游标名 CURSOR FOR SELECT ...;   -- 1. 声明游标
OPEN 游标名;                            -- 2. 打开游标
FETCH 游标名 INTO 变量列表;              -- 3. 取值
CLOSE 游标名;                           -- 4. 关闭游标

处理器 handler

create procedure p3()
begin
    declare exit handler for SQLSTATE '02000' close u_cursor; # 意思是 当这个sql状态为02000时 关闭u_cursor 游标
    declare exit handler for not found close u_cursor; # 意思是 当这个sql状态为02开头时 关闭u_cursor 游标
end;

存储函数

创建函数时必须指定以下特征之一（除非开启 log_bin_trust_function_creators)

特征	含义
DETERMINISTIC	确定性：相同输入必得相同输出
NO SQL	不包含 SQL 语句
READS SQL DATA	包含只读数据的 SQL
MODIFIES SQL DATA	包含写数据的 SQL

create function p4(n int)
returns 返回值类型
[特征...]
begin
    -- sql语句
    return 值;
end;
-- 调用
select p4()

触发器

触发器是与表关联的、在特定事件发生时自动执行的一段 SQL 代码，无需手动调用。

• 自动执行：由 INSERT / UPDATE / DELETE 事件触发
• 绑定表：每个触发器只属于一张表
• 行级触发：MySQL 只支持 FOR EACH ROW（每影响一行触发一次）
• 常用于：审计日志、数据校验、自动同步、级联操作

创建触发器语法

delimiter $$
create trigger 触发器名
    {BEFORE | AFTER} {INSERT | UPDATE | DELETE}
    on 表名
    for each row -- 行级触发 可选
    begin
        -- 触发逻辑
    end $$
delimiter ;
-- 查看当前数据库的全部触发器
show triggers ;
-- 删除触发器，不加触发名等于删除当前数据库的全部触发器
drop trigger 触发名;