613
逐行處理數據時避免死循環
當在一個循環體內逐行處理數據時，你必須確保該循環不會無休止的執行下去。在此，我將描述三個死循環的場景。已經有很多論文論及循環和基於集合的解決方式之間可行性的優劣，這些討論我就不再贅述。這裏我們假定你必須使用某種循環。
還要說明一點：我在此舉例說明有些情況下可能會出現死循環，而且盡可能的舉出最簡單的例子。在這些場景中，我並沒有說明循環的方法優於基於集合的解決方法，所以請不要對我所舉出的簡短例子作如此理解。
使用SET和SELECT都可能變量賦值失敗
請讀者自行分析，下麵的例子就證明了這兩種情況：
DECLARE @i1 INT, @i2 INT, @i3 INT;
SELECT @i1=-1, @i2=-1, @i3=-1;
PRINT 'the following SELECT will not change the value of @i1'
PRINT 'because the result set is empty'
SELECT @i1=1 WHERE 1=2;
SELECT @i1 AS [@i1];
PRINT 'the following SET will change the value of @i2 to NULL'
PRINT 'because the result set is empty'
SET @i2=(SELECT 1 WHERE 1=2);
SELECT @i2 AS [@i2];
PRINT 'the following SELECT will not change the value of @i3'
PRINT 'because it will raise an exception'
SET @i3=(SELECT 1 UNION ALL SELECT 2);
SELECT @i3 AS [@i3];
由於返回空的數據集，下麵的SELECT語句將不會改變@i1的值。
@i1
-----------
-1
(1 row(s) affected)
由於返回空的數據集，下麵的SET語句將不會改變@i2的值。
@i2
-----------
NULL
(1 row(s) affected)
由於產生以異常（返回多行記錄），下麵的SELECT語句將不會改變@i3的值。
Msg 512, Level 16, State 1, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
@i3
-----------
-1
(1 row(s) affected)
這是預期的結果，但你必須意識到這一點，否則你的循環可能無休止的執行下去。
Make sure you have read Tony Rogerson's post on the topic.
確保你已經閱讀了Tony Rogerson關於此主題的論文。
當SELECT語句改變變量值失敗時，可能導致死循環
請看下列表、樣例數據和存儲過程：
CREATE TABLE [data].[Orders](
[OrderID] [int] NOT NULL,
[OrderDate] [datetime] NOT NULL,
IsProcessed CHAR(1) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
DELETE FROM [data].[Orders];
INSERT [data].[Orders](
[OrderID],
[OrderDate],
IsProcessed)
SELECT 1, '20090420', 'N' UNION ALL
SELECT 2, '20090421', 'N' UNION ALL
SELECT 3, '20090422', 'N';
CREATE PROCEDURE dbo.ProcessNOrders
@IDsIntervalSize INT
AS
DECLARE @minID INT, @ID INT;
SELECT @minID=MIN([OrderID]), @ID=MIN([OrderID]) FROM [data].[Orders];
WHILE @ID<(@minID+@IDsIntervalSize) BEGIN
UPDATE [data].[Orders]
SET IsProcessed='Y'
WHERE [OrderID] = @ID;
SELECT TOP 1 @ID=[OrderID]
FROM [data].[Orders]
WHERE IsProcessed='N'
ORDER BY [OrderID];
PRINT @ID;
END;
-- 本次調用成功
EXEC dbo.ProcessNOrders 2;
GO
-- 恢複數據
UPDATE [data].[Orders]
SET IsProcessed='N';
GO
-- 本次調用處理了3條orders數據後陷入死循環
-- 取消執行
EXEC dbo.ProcessNOrders 10;
解決辦法很簡單，如下所示——隻要確保賦值前變量值被改變
ALTER PROCEDURE dbo.ProcessNOrders
@IDsIntervalSize INT
AS
DECLARE @minID INT, @ID INT;
SELECT @minID=MIN([OrderID]), @ID=MIN([OrderID]) FROM [data].[Orders];
WHILE @ID<(@minID+@IDsIntervalSize) BEGIN
UPDATE [data].[Orders]
SET IsProcessed='Y'
WHERE [OrderID] = @ID;
SET @ID = NULL;
SELECT TOP 1 @ID=[OrderID]
FROM [data].[Orders]
WHERE IsProcessed='N'
ORDER BY [OrderID];
PRINT @ID;
END;
GO
UPDATE [data].[Orders]
SET IsProcessed='N';
GO
-- 本次調用處理了3條orders數據後完成處理
EXEC dbo.ProcessNOrders 10;
你也可以使用SET替換SELECT，那樣也可以解決這個問題：
ALTER PROCEDURE dbo.ProcessNOrders
@IDsIntervalSize INT
AS
DECLARE @minID INT, @ID INT;
SELECT @minID=MIN([OrderID]), @ID=MIN([OrderID]) FROM [data].[Orders];
WHILE @ID<(@minID+@IDsIntervalSize) BEGIN
UPDATE [data].[Orders]
SET IsProcessed='Y'
WHERE [OrderID] = @ID;
SET @ID = (
SELECT TOP 1 [OrderID]
FROM [data].[Orders]
WHERE IsProcessed='N'
ORDER BY [OrderID]);
PRINT @ID;
END;
當賦值產生異常而導致對變量值的改變失敗時
創建如下對象：
CREATE VIEW dbo.LongestWaitingNotProcessedOrder
AS
SELECT
[OrderID],
[OrderDate],
IsProcessed
FROM [data].[Orders]
WHERE [OrderDate] = (SELECT MIN([OrderDate]) FROM [data].[Orders] WHERE IsProcessed='N')
AND IsProcessed='N'
GO
CREATE PROCEDURE dbo.ProcessOrder @OrderID SMALLINT
AS
SET NOCOUNT ON;
UPDATE [data].[Orders] SET IsProcessed='Y'
WHERE [OrderID]=@OrderID;
RETURN @@ERROR;
GO
當未處理的order中沒有相同的order date時，下麵的循環會正確執行。
UPDATE [data].[Orders]
SET IsProcessed='N';
DECLARE @ID INT;
SET @ID=-1;
WHILE @ID IS NOT NULL BEGIN
SET @ID=(SELECT [OrderID]
FROM dbo.LongestWaitingNotProcessedOrder);
PRINT @ID;
EXEC dbo.ProcessOrder @OrderID=@ID;
END
當orders中存在兩條相同order date的未處理記錄時，對變量值的賦值過程（改變變量值）會失敗，從而導致死循環。運行這個腳本後回到上麵的循環語句，你會發現該循環變成了死循環。
UPDATE [data].[Orders]
SET IsProcessed='N';
INSERT [data].[Orders](
[OrderID],
[OrderDate],
IsProcessed)
SELECT 4, '20090421', 'N';
同樣，解決起來很簡單。你隻需把循環嵌入TRY…CATCH塊中，如下所示：
DECLARE @ID INT;
SET @ID=-1;
BEGIN TRY
WHILE @ID IS NOT NULL BEGIN
SET @ID=(SELECT [OrderID]
FROM dbo.LongestWaitingNotProcessedOrder);
PRINT @ID;
EXEC dbo.ProcessOrder @OrderID=@ID;
END
END TRY BEGIN CATCH
SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH
現在循環會在第一次出現異常後結束執行。然而，如果存儲過程處理一筆order失敗，你仍可能陷入死循環。
重新考慮基於集合的解決方法
你可以看出，在Transact SQL中開發健壯的循環是相當複雜的，其中確有一些技巧。你也許可以重新考慮使用基於集合的解決方式來替換循環方法。
使用表變量
下麵的方法也相當健壯：
DECLARE @orderIDs TABLE(n INT, ID INT);
INSERT INTO @orderIDs(n, ID)
SELECT ROW_NUMBER() OVER(ORDER BY [OrderDate]), [OrderID]
FROM [data].[Orders] WHERE IsProcessed='N';
DECLARE @ID INT, @n INT, @MaxN INT;
SELECT @n=1, @MaxN = MAX(n) FROM @orderIDs;
WHILE @n <= @MaxN BEGIN
SELECT @ID = ID FROM @orderIDs WHERE n=@n;
PRINT @ID;
EXEC dbo.ProcessOrder @OrderID=@ID;
SET @n=@n+1;
END
即使存儲過程處理一筆order時可能會失敗，循環仍然能夠完成，因為每筆order將隻被處理一次。
最後更新：2017-04-02 00:06:46