SQL游标仅执行首行：如何遍历多结果行执行子查询？

需要对多行查询结果的每一行执行后续操作，使用游标时仅处理了首行，原代码及问题如下：

/*Order nubmer*/
DECLARE @orderVar AS INT
SET @orderVar = 1002308304
/*Print order*/
PRINT 'Broj shipmenta je '+CAST(@orderVar as varchar)
/*SELECT shipmentID*/
DECLARE @shipmentIDVar AS INT
SELECT @shipmentIDVar = ID FROM APSisak.ImportExport.Shipment where OrderNo= @orderVar
PRINT 'Broj shipmentID je '+CAST(@shipmentIDVar as varchar)
/*SELECT laneShipmentID*/
DECLARE @laneShipmentIDVar as INT
DECLARE laneCursor CURSOR FOR
SELECT LaneID
FROM APSisak.Queue.LaneShipment
WHERE APSisak.Queue.LaneShipment.LaneID = @shipmentIDVar    
OPEN laneCursor
SELECT @laneShipmentIDVar = ID FROM APSisak.Queue.LaneShipment
PRINT CAST(@laneShipmentIDVar as varchar)/*testni print*/
WHILE (@@FETCH_STATUS=0)    
BEGIN/*LOOP for laneShipmentID*/
/*Print laneShipmentID*/
PRINT 'LaneShipmentID je '+CAST(@laneShipmentIDVar as varchar)
FETCH NEXT FROM laneCursor 
INTO @laneShipmentIDVar/*Next laneShipmentID*/
/*Select vehicleID*/    
DECLARE @vehicleIDVar as INT
END/*END lane*/    
CLOSE laneCursor
DEALLOCATE laneCursor

当前仅输出LaneShipmentID je 16725，预期输出两行对应结果。

错误原因 #

1. 游标初始化后未通过FETCH NEXT从游标中获取首行数据，而是直接从表中查询赋值，导致初始变量值并非游标第一行
2. WHILE循环的初始@@FETCH_STATUS状态未正确设置，循环逻辑顺序错误
3. 游标查询的WHERE条件疑似字段错误（LaneID = @shipmentIDVar不符合业务关联逻辑，应为关联Shipment的字段）

正确实现代码 #

/*Order number*/
DECLARE @orderVar AS INT
SET @orderVar = 1002308304
/*Print order*/
PRINT 'Broj shipmenta je '+CAST(@orderVar as varchar)
/*SELECT shipmentID*/
DECLARE @shipmentIDVar AS INT
SELECT @shipmentIDVar = ID FROM APSisak.ImportExport.Shipment where OrderNo= @orderVar
PRINT 'Broj shipmentID je '+CAST(@shipmentIDVar as varchar)
/*SELECT laneShipmentID*/
DECLARE @laneShipmentIDVar as INT

-- 修正游标查询：获取LaneShipment的ID，关联ShipmentID字段
DECLARE laneCursor CURSOR FOR
SELECT ID
FROM APSisak.Queue.LaneShipment
WHERE ShipmentID = @shipmentIDVar
    
OPEN laneCursor
-- 先从游标中获取第一行数据
FETCH NEXT FROM laneCursor INTO @laneShipmentIDVar

WHILE (@@FETCH_STATUS=0)    
BEGIN
    /*Print laneShipmentID*/
    PRINT 'LaneShipmentID je '+CAST(@laneShipmentIDVar as varchar)
    
    /*此处添加针对当前行的后续查询逻辑，例如获取vehicleID*/
    DECLARE @vehicleIDVar as INT
    -- SELECT @vehicleIDVar = ... （根据需求编写具体查询）
    
    -- 获取下一行数据，为下一次循环做准备
    FETCH NEXT FROM laneCursor INTO @laneShipmentIDVar
END

CLOSE laneCursor
DEALLOCATE laneCursor

关键修复点 #

• 修正游标查询的字段和关联条件：确保游标返回需要处理的多行数据，关联字段替换为实际关联Shipment的字段（如ShipmentID）
• 调整游标执行顺序：打开游标后先执行一次FETCH NEXT INTO获取首行，再进入循环
• 循环内先处理当前行数据，再获取下一行，保证所有行都被遍历处理

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