开发者社区> ikingsmart> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

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

 使用SETSELECT都可能变量赋值失败

 请读者自行分析,下面的例子就证明了这两种情况:

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

 

-- 本次调用处理了3orders数据后陷入死循环

-- 取消执行

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

 

-- 本次调用处理了3orders数据后完成处理

 

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
C#中使用语句和循环来控制程序流总程序....
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/2896261 using System;using System.
435 0
分批读取文件中数据的程序流程及其C代码实现
一、概述 在实际的软件开发项目中,经常需要处理大量的文件。某些文件中包含了相当多的数据记录数,如作者本人参与过的项目中,一个文件中有好几十万条记录。
972 0
当生活进入了死循环,便需要一个终止循环的条件
很多时候不想写一些心情上的事了,单是一些技术上的觉悟、技巧之类的随笔。 老婆也在问我:怎么不像以前那样常写些东西了呢。我的解释是:在郁闷的时候、心情不好的时候才会有更多的感悟,如果开心了,生活优越了,让我感动的事也就越来越少。
599 0
for循环诡异情况
&lt;pre name="code" class="java"&gt;for(int i=0; i&lt;matrix.length; i++){ for(int j=0; j&lt;i; j++){ if(matrix[i][j] &gt; MIN_VALUE){ writerEdge(writer, matrix, j, i); } }
566 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载