MySQL存储过程中实现游标进行FIFO库存更新的正确方法

你的存储过程存在多处语法和逻辑错误，以下是问题点及修正后的完整代码：

核心问题分析 #

• DECLARE语句位置错误：MySQL规定所有变量、游标、处理器的DECLARE必须放在BEGIN块的最开头，不能在SET、IF等执行语句之后。
• 参数与列名冲突：输入参数item_id与表中列名重名，导致sum(qty)的子查询逻辑错误（会匹配所有item_id的记录，而非指定的输入值）。
• 未声明FETCH变量：in_id和in_qty没有提前声明，FETCH操作会失败。
• 语法不完整：多处缺少分号、END IF闭合，比如IF分支未用END IF结束，UPDATE/SET语句未加分号。
• 扣减逻辑错误：循环中误用全局库存av_stock判断，应基于当前游标取出的单条记录库存计算扣减量。

修正后的存储过程代码 #

DELIMITER $$
CREATE PROCEDURE updstock (IN p_item_id INT, IN p_issue_qty INT)
proc_label:
BEGIN
    -- 所有DECLARE必须放在最开头
    DECLARE av_stock INT DEFAULT 0;
    DECLARE finished INT DEFAULT 0;
    DECLARE in_id INT;
    DECLARE in_qty INT;
    
    DECLARE result CURSOR FOR
        SELECT id, qty
        FROM master_stock
        WHERE item_id = p_item_id
        ORDER BY id; -- FIFO按id升序，保证先入库的先扣减
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    -- 计算总可用库存
    SELECT SUM(qty) INTO av_stock
    FROM master_stock
    WHERE item_id = p_item_id;

    -- 可用库存不足，直接退出
    IF av_stock < p_issue_qty THEN
        LEAVE proc_label;
    END IF;

    OPEN result;

result_loop:
    LOOP
        FETCH result INTO in_id, in_qty;
    
        IF finished = 1 THEN
            LEAVE result_loop;
        END IF;

        IF p_issue_qty <= in_qty THEN
            -- 当前记录库存足够扣减，直接扣减剩余需求
            UPDATE master_stock 
            SET qty = qty - p_issue_qty
            WHERE id = in_id;
            
            SET p_issue_qty = 0;
            LEAVE result_loop; -- 需求已满足，提前退出循环
        ELSE
            -- 当前记录库存不足，扣减至0，剩余需求继续下一条
            UPDATE master_stock
            SET qty = 0
            WHERE id = in_id;
            
            SET p_issue_qty = p_issue_qty - in_qty;
        END IF;

    END LOOP result_loop;

    CLOSE result;
END$$
DELIMITER ;

关键修正说明 #

1. 参数重命名：将输入参数改为p_item_id、p_issue_qty，避免与表列名冲突。
2. DECLARE语句前置：所有变量、游标、处理器的声明移至BEGIN块最开头，符合MySQL语法要求。
3. 补充变量声明：添加in_id和in_qty的DECLARE，匹配游标FETCH的字段类型。
4. 修复语法闭合：补全所有分号、END IF，确保语句结构完整。
5. 优化扣减逻辑：
   • 用当前记录的in_qty判断是否足够扣减，而非全局av_stock。
   • 当需求满足时提前退出循环，提升效率。
   • 使用SELECT ... INTO替代SET赋值，更符合MySQL最佳实践。

内容的提问来源于stack exchange，提问作者Maddy