时间戳

首页 > 解决方案 > 如何防止此 SQL 脚本进入无限循环？

sql - 如何防止此 SQL 脚本进入无限循环？

问题描述

我有两个带列的表

我想将所有 id（userId、linkedIn 等）传输到users表列，如果恢复表中没有一个 Id，则将 true 存储在 excludeprofile 中。

我为此编写了以下脚本

BEGIN TRANSACTION

DECLARE @userId Int
DECLARE @linkedIn varchar(max)
DECLARE @StackOverflow varchar(max)
DECLARE @HackerRank varchar(max)
DECLARE @Bitbucket varchar(max)
DECLARE @Github varchar(max)
DECLARE @x int

SET @x = 0

WHILE @x <> (SELECT COUNT(*) FROM Users)
BEGIN
    IF NOT EXISTS (SELECT id FROM Users 
                   ORDER BY Id OFFSET @x ROWS   
                               FETCH NEXT 1 ROWS ONLY)
    BEGIN
        SET @userId = (SELECT TOP 1 id FROM Users)
        SET @x = @x + 1;
    END

    IF (@userId <> NULL)
    BEGIN
        IF EXISTS (SELECT LinkedInProfile FROM Resumes WHERE Resumes.UserId = @userId)
        BEGIN
            SET @linkedIn = (SELECT LinkedInProfile FROM Resumes WHERE Resumes.UserId = @userId)
        END 

    if exists(select Github from Resumes where Resumes.UserId=@userId)
    begin
    set @Github =(select Github from Resumes where Resumes.UserId=@userId)
    end
    if exists(select Bitbucket from Resumes where Resumes.UserId=@userId)
    begin
    set @Bitbucket =(select Bitbucket from Resumes where Resumes.UserId=@userId)
    end
    if exists(select HackerRank from Resumes where Resumes.UserId=@userId)
    begin
    set @HackerRank =(select HackerRank from Resumes where Resumes.UserId=@userId)
    end
    if exists(select StackOverflow  from Resumes where Resumes.UserId=@userId)
    begin
    set @StackOverflow =(select StackOverflow from Resumes where Resumes.UserId=@userId)
    end
    end
END

    if(@linkedIn!=null)
    begin
    update Users set LinkedInProfile=@linkedIn
    where Users.Id = @userId;
    end
    if(@linkedIn <> null or @Bitbucket <> null or @Github = null or @StackOverflow = null or @HackerRank = null)
    begin
    update Resumes set ExcludeProfiles=0
    where Resumes.UserId = @userId;
    end
    if(@linkedIn = null and @Bitbucket = null and @Github = null and @StackOverflow = null and @HackerRank = null)
    begin
    update Resumes set ExcludeProfiles=0
    where Resumes.UserId = @userId;
    end
COMMIT TRANSACTION

我认为循环是无限的，因为执行没有停止。

标签： sqlsql-serverdatabase

解决方案

就像评论中的每个人已经指出的那样：这些操作不需要WHILE循环或光标。

创建目标表

declare @User table
(
    Id int,    --User
    LI_id int, --LinkedIn
    SO_id int, --StackOverflow
    BB_id int, --BitBucket
    GH_id int  --GitHub
);

declare @Resume table
(
    Id int,
    Us_id int, --User (1*)
    LI_id int, --LinkedIn (2*)
    SO_id int, --StackOveflow (3*)
    BB_id int, --BitBucket (4*)
    GH_id int, --GitHub (5*)
    Excl bit   --ExcludeProfile
);

解决方案 1

将恢复行标记为默认不排除 ( Excl = 0)。执行此操作的正确方法是在表定义中使用列默认值。

insert into @Resume (Id, Us_id, LI_id, SO_id, BB_id, GH_id, Excl) values
(1,  101,  201,  301,  401,  501, 0),
(2,  102, null,  302,  402,  502, 0),
(3,  103,  203,  303,  403,  503, 0),
(4, null, null, null, null, null, 0); --resume without id's

然后可以通过两个查询来实现该解决方案。如果您想避免一个查询在没有另一个查询的情况下成功运行，那么您应该在它们周围添加一个事务。

  1. 将有效行从Resume复制到User。
  2. 将Resume中的所有无效行标记为已排除。

执行：

insert into @User (Id, LI_id, SO_id, BB_id, GH_id)
select Us_id, LI_id, SO_id, BB_id, GH_id
from @Resume
where not (     Us_id is null
            and LI_id is null
            and SO_id is null
            and BB_id is null
            and GH_id is null);

update @Resume
set Excl = 1
where Us_id is null
  and LI_id is null
  and SO_id is null
  and BB_id is null
  and GH_id is null;

解决方案 2

另一种方法是在默认情况下使用排除用户标记Resume行 ( Excl = 1)。同样，您可以为此使用列默认值。

insert into @Resume (Id, Us_id, LI_id, SO_id, BB_id, GH_id, Excl) values
(1,  101,  201,  301,  401,  501, 1),
(2,  102, null,  302,  402,  502, 1),
(3,  103,  203,  303,  403,  503, 1),
(4, null, null, null, null, null, 1); --resume without id's

使用这种方法，现在可以使用输出子句在单个查询中复制（插入）和更新有效的Resume行。这也意味着现在不需要事务，因为查询现在是单个操作。

update r
set r.Excl = 0
output  inserted.Us_id,
        inserted.LI_id,
        inserted.SO_id,
        inserted.BB_id,
        inserted.GH_id
into @User
from @Resume r
where not (     r.Us_id is null
            and r.LI_id is null
            and r.SO_id is null
            and r.BB_id is null
            and r.GH_id is null);

结果

两种解决方案的输出相同。

select * from @resume;

Id          Us_id       LI_id       SO_id       BB_id       GH_id       Excl
----------- ----------- ----------- ----------- ----------- ----------- -----
1           101         201         301         401         501         0
2           102         NULL        302         402         502         0
3           103         203         303         403         503         0
4           NULL        NULL        NULL        NULL        NULL        1


select * from @user;

Id          LI_id       SO_id       BB_id       GH_id
----------- ----------- ----------- ----------- -----------
101         201         301         401         501
102         NULL        302         402         502
103         203         303         403         503

推荐阅读