SQL游标未返回首行数据且插入行数与预期不符如何解决

AI 小北

2026-10-2

问题根因

游标逻辑顺序错误：打开游标后首次FETCH获取了第一行数据，进入WHILE循环后第一行代码再次执行FETCH，直接丢弃了首行数据，导致首行永远不会被插入到目标表，这是CMF200M统计值少2条的直接原因。
统计条件不一致：统计原表时使用了PROMISE <= @pdate的过滤条件，但游标定义的查询语句没有该条件，游标会处理所有符合初始条件的行，导致出现H200F多2条的异常。

修复后代码

DECLARE curCURSOR CURSOR 
FOR 
SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE , SALES_ORDER , LINE_NUMBER, DESCRIPTION, CARRIER FROM T_tmpShipReporCommAssy
WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @pSite 
AND FG_AVAILABLE > 0
-- 补充和统计逻辑一致的过滤条件，可按需保留
AND PROMISE <= @pdate
ORDER BY  PROMISE, SALES_ORDER, LINE_NUMBER

OPEN curCURSOR
-- 清空目标表逻辑移到游标操作前，避免无效重复判断
IF EXISTS(SELECT 1 FROM AvailToShipPalletsSN)
BEGIN
    DELETE FROM AvailToShipPalletsSN
    DBCC CHECKIDENT ('[AvailToShipPalletsSN]', RESEED, 0);
END

DECLARE @e INT
SET @e = 0
-- 首次获取首行数据
FETCH curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 先处理当前行数据，再获取下一行
    WHILE (@e < @required_quantity)
    BEGIN
        INSERT INTO AvailToShipPalletsSN (item, city, required_quantity, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER) 
        VALUES (@ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER)
        SET @e = @e + 1
    END
    SET @e = 0
    -- 处理完当前行后再获取下一行数据
    FETCH FROM curCURSOR INTO @ITEM, @CITY, @required_quantity, @MODEL, @PROMISE, @SALES_ORDER, @LINE_NUMBER, @DESCRIPTION, @CARRIER
END

CLOSE curCURSOR
DEALLOCATE curCURSOR

优化建议

这种按数量拆分行的场景不需要使用游标，使用递归CTE实现性能更高，逻辑更简洁，示例如下：

WITH SplitCTE AS (
    SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER, 1 AS idx
    FROM T_tmpShipReporCommAssy
    WHERE [DESCRIPTION]  like 'COM%ASSY%' AND CITY = @pSite 
    AND FG_AVAILABLE > 0 AND PROMISE <= @pdate
    UNION ALL
    SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER, idx + 1
    FROM SplitCTE
    WHERE idx < OPEN_QUANTITY
)
INSERT INTO AvailToShipPalletsSN (item, city, required_quantity, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER)
SELECT ITEM, CITY, OPEN_QUANTITY, MODEL, PROMISE, SALES_ORDER, LINE_NUMBER, DESCRIPTION, CARRIER
FROM SplitCTE
OPTION (MAXRECURSION 0)

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