Rock的SQL筆記本

我很認真的檢視該預存程序，當下並沒有發現有什問題會造成異常，但後來發現問題的癥結點居然是因為在Cursor中更新Clustered Index造成。 接下來我重現問題並分享給大家

如下圖所示我建立一張資料表叫tb1，該資料表有3個欄位，這張資料表我用ser_no欄位當Clustered Index。我在tb1資料表中先塞入6筆資料，ser_no則是1 ~ 6 。

 

下圖我利用tb1當來源去建立了一個Cursor，並利用此Cursor去更新tb1自己的ser_no欄位值。注意@ser_no起始值從10開始，所以正常跑完Cursor後，這6筆資料的ser_no應該會是10 ~ 15 (這裡我有設定當ser_no大於1000時就中斷Cursor，以防迴圈異常)。

 

跑完上述語法後我們去看一下tb1目前6筆資料的ser_no是多少呢? 如下圖紅色圈選處，居然不是 10 ~ 15 而是 995 ~ 1000 。這表示Cursor不是自己終止，而是被我設的中斷條件 (ser_no > 1000) 給終止的。

 

下圖是Cursor運作時原理，每FETCH INTO一次就向下一筆提取資料。

 

透過這一張圖就可以知道為何我剛剛的Cursor會一直跑到1000才終止。原因就是我不斷 遞 增 的去update ser_no，而ser_no又是tb1的Clustered Index。因此當tb1的第一筆資料ser_no=1被update成10時，該筆資料會被插入到tb1的最後一筆，因此資料便會不斷往資料表後面擺，所以FETCH就會一直有資料抓，導致無窮迴圈產生。

 

目前tb1最後一筆資料ser_no是1000，接下來我重跑一次剛剛的Cursor，但這一次我將@ser_no設為1 (如下圖紅色圈選處)。所以屆時資料在update後是往tb1的前面塞，因此迴圈應該只會跑6次就終止，而該6筆資料的ser_no應該是1 ~ 6。

 

如下圖所示果然ser_no最大值為6，表示該Cursor只跑了6次就終止。

 

但如果我的需求就是會更新Clustered Index且就是得遞增呀，那該怎麼只做完6筆資料就終止呢? 我們從Cursor官方文件中可以看見當我們在Declare Cursor時可以加入STATIC關鍵字，該關鍵字說明如下。

 

接下來我透過STATIC關鍵字宣告Cursor來看看是否還會有無限迴圈問題存在。此時我又將@ser_no起始值改成10，所以更新後的資料又會從目前資料的最後一筆的後面塞。

 

跑完上面語法後我們再次檢視一下tb1的6筆資料的ser_no欄位值，從下圖可以看見該資料表ser_no最大值為15，所以表示Cursor只跑了6次就終止。因此STATIC關鍵字是有效用的。

 

 

Lab 的 Code 如下，請自行修正。

--建立Table
Create Table tb1(ser_no int,name char(10),nums int)
GO
--用ser_no欄位建立Clustered Index
Create Clustered index [cix_tb1] on tb1(ser_no)
GO
--塞入6筆資料
Insert Into tb1 values
(1,'a',1)
,(2,'b',2)
,(3,'c',3)
,(4,'d',4)
,(5,'e',5)
,(6,'f',6)
GO


declare @nums int;
declare @ser_no int;
--將ser_no再改回由10開始
Set @ser_no=10;
DECLARE cur CURSOR STATIC FOR SELECT nums FROM tb1 
OPEN cur;
FETCH NEXT FROM cur INTO @nums
WHILE(@@FETCH_STATUS = 0)
    BEGIN
	 --更新資料表的Clustered Index欄位 
	 update tb1 set ser_no=@ser_no Where nums=@nums;
	 set @ser_no+=1;
	 --預防無窮迴圈，ser_no高於1000就跳出fetch
	 If @ser_no > 1000 break;
	 FETCH NEXT FROM cur INTO @nums
	End 
CLOSE cur
DEALLOCATE cur

 

我是ROCK

rockchang@mails.fju.edu.tw