MSSQL 循環(huán)(游標(biāo)循環(huán)及類似For的循環(huán))

利用游標(biāo)循環(huán)：

DECLARE My_Cursor CURSOR --定義游標(biāo)
FOR (SELECT * FROM dbo.Table) --查出需要的集合放到游標(biāo)中
OPEN My_Cursor; --打開游標(biāo)
FETCH NEXT FROM My_Cursor ; --讀取第一行數(shù)據(jù)
WHILE @@FETCH_STATUS = 0
    BEGIN
        --UPDATE dbo.Table SET 字段1 =‘***’ 　WHERE CURRENT OF My_Cursor; --更新
        --DELETE FROM dbo.Table WHERE CURRENT OF My_Cursor; --刪除
        FETCH NEXT FROM My_Cursor; --讀取下一行數(shù)據(jù)
    END
CLOSE My_Cursor; --關(guān)閉游標(biāo)
DEALLOCATE My_Cursor; --釋放游標(biāo)

利用游標(biāo)賦值循環(huán)：

declare @參數(shù)1 參數(shù)1類型,@參數(shù)2 參數(shù)2類型
DECLARE MyCursor CURSOR --定義游標(biāo)(利用游標(biāo)循環(huán))
FOR (select 字段1,字段2 from Table) --查出需要的集合放到游標(biāo)中
                order by 字段1              --排序語句放在括號(hào)外
OPEN MyCursor; --打開游標(biāo)
FETCH NEXT FROM MyCursor INTO @參數(shù)1 ,@參數(shù)2;  --讀取第一行數(shù)據(jù)
WHILE @@FETCH_STATUS = 0
Begin
        if 條件成立 
        begin            --如需跳過當(dāng)前循環(huán)，需先賦值,再continue，否則會(huì)進(jìn)入死循環(huán)
                FETCH NEXT FROM MyCursor INTO @參數(shù)1 ,@參數(shù)2;
                continue;  --跳過當(dāng)前循環(huán)，進(jìn)入下一循環(huán)
        end

        if 條件成立 
        begin
                    Break;             --跳出整個(gè)循環(huán)
        end
        /* 需要在循環(huán)內(nèi)處理的*** */
                    --PRINT @參數(shù)1,參數(shù)2; --打印參數(shù)值(調(diào)試)
                    --UPDATE Table set 字段3=*,字段4=* where 字段1=@參數(shù)1 and 字段2=@參數(shù)2
                    --Insert into Table1(字段1，字段2) values(參數(shù)1,參數(shù)2)
                    --Delete from table where 字段1=參數(shù)1 and 字段2=參數(shù)2
        FETCH NEXT FROM MyCursor INTO @參數(shù)1 ,@參數(shù)2;           --賦值后進(jìn)入下一循環(huán)
End
CLOSE MyCursor;     --關(guān)閉游標(biāo)
DEALLOCATE MyCursor;    --釋放游標(biāo)

類似For循環(huán)的ＳＱＬ循環(huán):

declare @itemnumber int --定義需要循環(huán)的次數(shù)  
 declare @tagint int --定義標(biāo)志字段，用于結(jié)束循環(huán)  
 set @tagint=1 
 select @itemnumber = count(distinct Creater) from Demo_TestTable where isnull(Creater,'')<>'' And   
　  DATEDIFF(DAY,CreatDate,GETDATE())<1 
   if(@itemnumber>0)  
   begin  
     while @tagint<=@itemnumber  
         begin  
              waitfor delay '00:00:01' --每隔一秒再執(zhí)行 可用參數(shù)變量替換  
             Update Demo_TestTable set CreatDate=GETDATE() where Creater =(  
             Select Creater from (  
                 select Creater,ROW_NUMBER() over(order by Creater) as RowID from Demo_TestTable where   
isnull(Creater,'')<>'' And DATEDIFF(DAY,CreatDate,GETDATE())<1 group by Creater  
             ) TableA  
              where  TableA.RowID=@tagint  
              )  
              set @tagint=@tagint+1  
        end  
   end