逐行处理数据时避免死循环－－避免死循环2

逐行处理数据时避免死循环

Alexander Kuznetsov

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/23/when-you-process-your-rows-one-by-one-avoid-infinite-loops.aspx

当在一个循环体内逐行处理数据时，你必须确保该循环不会无休止的执行下去。在此，我将描述三个死循环的场景。已经有很多论文论及循环和基于集合的解决方式之间可行性的优劣，这些讨论我就不再赘述。这里我们假定你必须使用某种循环。

 还要说明一点：我在此举例说明有些情况下可能会出现死循环，而且尽可能的举出最简单的例子。在这些场景中，我并没有说明循环的方法优于基于集合的解决方法，所以请不要对我所举出的简短例子作如此理解。

 使用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将只被处理一次。