t-sql 中实现循环处理

1.使用游标:

DECLARE @profileId AS BIGINT;
DECLARE C_ProfileID CURSOR FOR SELECT ID FROM dbo.Profile WHERE status<> 'deleted';
OPEN C_ProfileID;
FETCH NEXT FROM C_ProfileID INTO @profileId;
WHILE @@FETCH_STATUS =0
BEGIN
  UPDATE dayreportItemKeyword 
                        SET isNonBid = 1 
                        WHERE
	                    profileId = @profileId 
	                    AND isNonBid = 0 
	                    AND NOT EXISTS ( SELECT 1 FROM Keyword WHERE profileId = @profileId AND keywordText = searchedKeyword  AND matchType <> 'exact');
 FETCH NEXT FROM C_ProfileID INTO @profileId;
END
CLOSE C_ProfileID
DEALLOCATE C_ProfileID

2.使用表变量:

DECLARE @profile TABLE(
profileId BIGINT
);

INSERT INTO @profile(profileId) SELECT ID FROM dbo.Profile WHERE status<> 'deleted';
DECLARE @profileId BIGINT
WHILE EXISTS(SELECT profileId FROM @profile)
BEGIN
SELECT TOP 1 @profileId=  profileId FROM @profile;
PRINT @profileId;
DELETE FROM @profile WHERE profileId=@profileId;
END