数据库开发|SQL技巧 mysql存储过程语法详解及游标使用方法

数据库开发 | SQL技巧：MySQL存储过程语法详解及游标使用方法

场景引入：

"小王最近接手了一个电商平台的订单统计任务，每天需要手动执行十几条SQL语句生成报表，不仅效率低还容易出错，同事老张看不下去了，甩过来一句：'用存储过程啊，一次写好天天自动跑！' 小王挠头：'存储过程？游标？这些听起来好高级...'"

别担心！今天我们就来拆解MySQL存储过程的"黑匣子"，让你像老司机一样优雅地批量处理数据。

存储过程：SQL里的"自动脚本"

什么是存储过程？
简单说就是预存在数据库里的一组SQL语句集合，像个小程序，随时调用不用重复写代码。

三大优势：

1. 效率高：一次编译多次执行
2. 安全：避免SQL注入
3. 复用：像函数一样随时调用

手把手创建存储过程

基础语法模板

DELIMITER //  -- 临时修改结束符（避免与SQL语句冲突）
CREATE PROCEDURE 过程名(参数列表)
BEGIN
    -- 这里写SQL逻辑
END //
DELIMITER ;  -- 恢复默认结束符

实际案例：订单统计

DELIMITER //
CREATE PROCEDURE daily_order_report(IN date_param DATE)
BEGIN
    -- 1. 创建临时表存储结果
    CREATE TEMPORARY TABLE temp_report (
        product_name VARCHAR(100),
        total_orders INT,
        total_amount DECIMAL(10,2)
    );
    -- 2. 插入统计结果
    INSERT INTO temp_report
    SELECT p.name, COUNT(o.id), SUM(o.price)
    FROM orders o JOIN products p ON o.product_id = p.id
    WHERE o.order_date = date_param
    GROUP BY p.name;
    -- 3. 返回结果
    SELECT * FROM temp_report;
    -- 4. 清理临时表
    DROP TEMPORARY TABLE temp_report;
END //
DELIMITER ;

调用方法：

CALL daily_order_report('2025-08-20');

参数传递的三种姿势

示例：

CREATE PROCEDURE update_balance(
    IN user_id INT,
    INOUT balance DECIMAL(10,2),
    OUT status VARCHAR(20)
)
BEGIN
    IF balance > 0 THEN
        UPDATE accounts SET amount = amount + balance 
        WHERE id = user_id;
        SET status = '充值成功';
    ELSE
        SET status = '金额不合法';
    END IF;
END

游标：逐行处理的"扫描仪"

当需要逐行处理查询结果时（比如复杂计算、数据迁移），游标就是你的神器。

游标使用四部曲

CREATE PROCEDURE process_orders()
BEGIN
    -- 1. 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_amount DECIMAL(10,2);
    -- 2. 声明游标
    DECLARE cur CURSOR FOR 
        SELECT id, amount FROM orders WHERE status = 'unpaid';
    -- 3. 声明异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    -- 4. 使用游标
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO order_id, order_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 这里处理每一行数据（示例：记录日志）
        INSERT INTO payment_log 
        VALUES (order_id, order_amount, NOW());
    END LOOP;
    CLOSE cur;
END

关键点：

• DECLARE CONTINUE HANDLER 必须写在游标声明之后
• 游标使用后必须CLOSE释放资源
• 循环内一定要有退出条件

避坑指南

1. 变量命名冲突：避免与列名相同，建议加前缀如v_username
2. 错误处理：使用DECLARE EXIT HANDLER FOR SQLEXCEPTION捕获异常
3. 性能优化：
   • 游标处理大数据集时较慢,尽量用批量操作
   • 复杂逻辑可拆分成多个存储过程

最佳实践场景

1. 定时任务：结合事件调度器实现日报
2. 数据迁移：游标+事务保证数据一致性
3. 权限控制：只开放存储过程调用权限，保护基表

-- 创建定时事件示例
CREATE EVENT auto_generate_report
ON SCHEDULE EVERY 1 DAY STARTS '2025-08-21 23:30:00'
DO CALL daily_order_report(CURRENT_DATE());

：存储过程就像SQL世界的瑞士军刀，游标则是精细操作的镊子，掌握它们，你就能：
✓ 告别重复SQL粘贴
✓ 提升数据处理安全性
✓ 实现复杂业务逻辑封装

下次见到需要循环处理的数据,不妨自信地说："放着，我用存储过程来解决！"

（注：本文基于MySQL 8.0语法，部分语法在旧版本可能不适用）