[SQL]CURSOR 光標用法

說明:  

 CURSOR 為暫存資料，一次只針對一個資料集直接對資料庫進行操作搬移或其他操作

注意 FETCH取得全域變數狀態(@@FETCH_STATUS)

0:FETCH成功

-1:FETCH失敗

-2:資料不存在

--T2 table have 2 column(id,status)

DECLARE cur_Buf CURSOR

FOR

SELECT * FROM [dbo].T2

DECLARE @iBuf_Id INT = 0;

DECLARE @iBuf_Status INT = 0;

DECLARE @iShow_Id INT = 0;

DECLARE @iShow_Status INT = 0;

DECLARE @iShow_Count INT = 0;

 

PRINT '----START---'

OPEN cur_Buf

FETCH NEXT FROM cur_Buf into @iBuf_Id,@iBuf_Status

WHILE(@@FETCH_STATUS = 0) BEGIN

   SET @iShow_Id = @iBuf_Id

   SET @iShow_Status = @iBuf_Status

   SET @iShow_Count = @iShow_Count +1

   FETCH NEXT FROM cur_Buf into @iBuf_Id,@iBuf_Status

END

CLOSE cur_Buf

DEALLOCATE cur_Buf

PRINT '----OVER---'

SELECT @iShow_Id,@iShow_Status,@iShow_Count