逐行处理数据时避免死循环-阿里云开发者社区

开发者社区> ikingsmart> 正文

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

简介:
+关注继续查看

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

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

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

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

相关文章
带你读《Java程序设计与计算思维》之二:认识数据处理与表达式
程序设计的过程就是一种计算思维的表现,《Java程序设计与计算思维》结合Java程序设计语言的教学特点,遵循计算思维的方式,图解重要概念,通过大量的范例程序讲解和上机编程实践来指导读者活用Java程序语法,兼顾培养计算思维和学习面向对象程序设计的双目标。
497 0
ML之DR之PCA:利用PCA对手写数字图片识别数据集进行降维处理(理解PCA)
ML之DR之PCA:利用PCA对手写数字图片识别数据集进行降维处理(理解PCA)
16 0
从 0 到 1 通过 Flink + Tablestore 进行大数据处理与分析
阿里云实时计算Flink版是一套基于 Apache Flink 构建的⼀站式实时大数据分析平台。在大数据场景下,实时计算 Flink 可提供端到端亚秒级实时数据流批处理能力。表格存储 Tablestore (又名 OTS)是阿里云自研的多模型结构化数据存储,可提供海量结构化数据的存储、查询分析服务。表格存储的双引擎架构支持千万TPS和毫秒级延迟的服务能力,可作为大数据计算的极佳上下游存储。
336 0
加密技术加强数据私密性确保云计算安全
本文讲的是加密技术加强数据私密性确保云计算安全,一名IBM研究员解决了一项棘手的数学问题,该问题自从几十年前公钥加密发明以来一直困扰着科学家们。该项创新为“隐私同态(privacy homomorphism)”或“全同态加密(fully homomorphic encryption)”领域的重要技术突破,使得加密信息,即刻意被打乱的数据仍能够被深入和无限的分析,而不会影响其保密性。
1118 0
python将AD7606B原始补码数据数组转换成原码数据数组并进行处理为电压值
python将AD7606B原始补码数据数组转换成原码数据数组并进行处理为电压值
41 0
+关注
99
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载