--聲明2個變量
declare @rownum nvarchar(20) ?
declare @num int
set?? @num =0
--聲明一個游標mycursor，select語句中參數(shù)的個數(shù)必須要和從游標取出的變量名相同
declare mycursor cursor for select row_number() over(order by Code) as rownum from T_RolePermisModule
?
--打開游標
open mycursor
?
--從游標里取出數(shù)據(jù)賦值到我們剛才聲明的2個變量中
fetch next from mycursor into @rownum
?
--判斷游標的狀態(tài)
--0 fetch語句成功?? ?
---1 fetch語句失敗或此行不在結(jié)果集中?? ?
---2被提取的行不存在
while (@@fetch_status=0)
begin
?
--顯示出我們每次用游標取出的值
?? print '游標成功取出一條數(shù)據(jù)'

?? set?? @num =@num+1
?? print @num
? ?--UPDATE T SET [iIndex] = @num

--05下有莫名奇妙的橫線，不顯示

UPDATE T SET [iIndex] =STUFF('TT000',6-LEN(@num),LEN(@num),@num)?
?? from (select *,row_number() over(order by [iIndex]) as rownum from T_RolePermisModule) T
?? where @rownum =rownum
?
--用游標去取下一條記錄
?? fetch next from mycursor into @rownum
end
--關閉游標
close mycursor
--撤銷游標
deallocate mycursor

(SQL游標) 根據(jù)行號更新某一列序號