sql server 中游標的認識

 　　一、遇到的問題

　　實際上，也不算什麼太大的問題O(∩_∩)O：我們有時候可能希望在批處理或者存儲過程中直接對select結果集進行加工 ，這個時候，我們需要一種能夠讓我們逐條處理每一行記錄 的數據庫對象。

　　二、游標的概念

　　解決上面的問題，我們可以使用一種叫做“游標”的數據庫對象。

　　游標(Cursor) 可以看做一種數據類型，它可以用來遍歷結果集，相當於指針，或者是數組中的下標。它處理結果集的方法有以下幾種：

　　定位到結果集的某一行

　　從當前結果集的位置搜索一行或一部分行

　　對結果集中的當前行進行數據修改

　　三、游標的使用方法(創建、打開、讀取、關閉、刪除)

　　【創建游標】

　　和定義各種數據類型的方法有點像，但是注意，不要加“@”(實際上也有“游標類型的變量”，和“游標”的用法幾乎完全相同，而且定義時使用@符號)。下面是定義游標的語句：

　　declare 游標名 cursor [local|global] [forward_only|scroll]

　　for

　　select查詢語句

　　游標分為局部游標和全局游標兩種，local表示局部游標，global表示全局游標(默認值，可以省略)。當指定forward_only(默認值，可以省略)時，游標是只進的，也就是說只能從頭到尾地提取記錄，如果需要在行之間來回跳躍，需要指定為scroll。

　　【使用游標】

　　只創建游標但是不使用它，就沒有任何意義了。下面我們先舉個最簡單的例子來演示創建好游標之後的幾步使用過程：

　　--【創建游標】

　　declare C1 cursor for select xingming from yiren

　　declare @xingming varchar(20)

　　--【打開游標】

　　open C1

　　--【讀取游標】

　　fetch next from C1 into @xingming --while的特點就是要先寫一次

　　while(@@FETCH_STATUS=0)

　　begin

　　print '姓名：'+@xingming

　　fetch next from C1 into @xingming

　　end

　　--【關閉游標】

　　close C1

　　--【刪除游標】

　　deallocate C1

　　游標的使用方法是不是和Java中的 whle(rs.next()){}很像呢?實際上rs.next()執行時就直接在結果集中向後移動一條了，如果沒有到達結果集的末端，仍然會執行循環體。在這裡使用游標也是一樣，@@FETCH_STATUS的值為0時，游標尚未走到結尾。當它不為0了，游標就走到了結尾，將退出循環。

　　fetch next from 游標名 into 變量名列表 是一種固定形式的讀取游標內容的方法。當查詢語句選擇了多個字段的時候，讀取時也需要借助這句話向多個變量賦值。於是寫成變量名列表。

　　【全局游標和scroll游標】

　　前面提到全局游標和scroll游標，下面舉個例子：

　　if(CURSOR_STATUS('global','CURSOR_2')!=-3) deallocate CURSOR_2

　　declare CURSOR_2 cursor scroll --全局的scroll游標

　　for select xingming,nicheng,xingbie from yiren

　　--第一個T-SQL批開始

　　open CURSOR_2

　　declare @seq int,

　　@xingming varchar(20),@nicheng varchar(50),@xingbie nchar

　　set @seq=4

　　fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie

　　if(@@FETCH_STATUS=0)

　　begin

　　print '第'+cast(@seq as varchar)+'個藝人是：'+@xingming

　　print case @xingbie when '男' then '他' when '女' then '她' end

　　+'的昵稱是：'+@nicheng

　　end

　　close CURSOR_2

　　go

　　--第二個T-SQL批開始

　　open CURSOR_2

　　declare @seq int,

　　@xingming varchar(20),@nicheng varchar(50),@xingbie nchar

　　set @seq=5 --分成了兩個批，需要再次定義@seq

　　fetch absolute @seq from CURSOR_2 into @xingming,@nicheng,@xingbie

　　if(@@FETCH_STATUS=0)

　　begin

　　print '第'+cast(@seq as varchar)+'個藝人是：'+@xingming

　　print case @xingbie when '男' then '他' when '女' then '她' end

　　+'的昵稱是：'+@nicheng

　　end

　　close CURSOR_2

　　go

　　--在第三個批中刪除游標

　　deallocate CURSOR_2

　　當開啟了scroll選項後，fetch可以用於讀next(後移)、prior(前移)、first(第一行)、last(最後一行)、absolute(以數值定位到絕對行)、relative(以數值定位到相對行) 。

　　全局游標一旦被定義就會一直存在，所以每個批處理都能看到它。直到使用deallocate來刪除它，它才會消失。CURSOR_STATUS('global','CURSOR_2')可以檢查它的狀態。

　　【游標的嵌套】

　　由於大大影響系統性能，簡單了解一下即可。

　　if(CURSOR_STATUS('global','CURSOR_3')!=-3) deallocate CURSOR_3

　　declare CURSOR_3 cursor for

　　select yanchuid from yanchu

　　open CURSOR_3

　　declare @ycid int

　　fetch next from CURSOR_3

　　into @ycid

　　while(@@FETCH_STATUS=0)

　　begin

　　print '參加第'+cast(@ycid as varchar)+'次演出的藝人是：'

　　declare CURSOR_4 cursor for

　　select xingming from yiren where yirenid in

　　(select yirenid from yanchuyiren where yanchuid=@ycid)

　　--這句使用了子查詢，實際上可以再嵌套一個游標

　　declare @xingming varchar(50)

　　open CURSOR_4

　　fetch next from CURSOR_4 into @xingming

　　while(@@FETCH_STATUS=0)

　　begin

　　print @xingming

　　fetch next from CURSOR_4 into @xingming

　　end

　　close CURSOR_4

　　deallocate CURSOR_4

　　fetch next from CURSOR_3

　　into @ycid

　　print ''

　　end

　　close CURSOR_3

　　deallocate CURSOR_3

　　【游標變量】

　　游標變量是真正的把游標當做數據類型來使用的一種方法，游標變量和游標對象的區別就在於是否有@。創建游標變量的時候，首先declare @游標變量名 cursor，然後set @游標變量名=cursorfor select語句。

　　declare @c1 CURSOR

　　set @c1=cursor for select xingming from yiren

　　open @c1

　　declare @xingming varchar(50)

　　fetch next from @c1 into @xingming

　　print @xingming

　　close @c1

　　deallocate @c1

　　四、游標的注意事項

　　【游標的缺點】

　　使用游標會把結果集一條條取出來處理，增加了服務器的負擔，再者使用游標的效率遠遠沒有使用默認結果集的效率高。所以，能不用游標就盡量不要用。

　　【游標的補充說明】

　　當我們剛剛打開一個游標的時候，它並不指向第一條記錄，而是指向第一條記錄的前邊。我們可以拿書做比喻，游標不僅僅可以指向記錄集中的記錄(書內容的每一頁)，也可以指向記錄集外部沒有記錄的地方(書的封面和封底)。

　　@@fetch_status有3種取值：0表示fetch 正常執行、-1表示fetch超出了結果集、-2表示fetch所指向的行已經不存在了。

　　五、修改分頁查詢存儲過程，使用游標

　　將第一個分支修改成如下代碼：

　　if @currentpage >1

　　begin

　　if @currentpage>@totalpages

　　begin

　　set @currentpage = @totalpages

　　end

　　declare @start int,@count int

　　set @count = 0

　　set @start = @currentpage*@pagesize+1

　　set @sql='declare cursor_1 cursor scroll for select * from '

　　+@tablename+' order by '+@idname

　　exec(@sql)

　　open cursor_1

　　fetch relative @start,@pagesize from cursor_1

　　while @@fetch_status=0

　　begin

　　set @count = @count+1

　　fetch next from cursor_1

　　if @count=@pagesize-1

　　break

　　end

　　close cursor_1

　　deallocate cursor_1

　　end

　　並去掉原來go前面的

　　exec(@sql)

　　即可。如果不去掉這句，會在存儲過程的最後額外再執行一次這句話，從而錯誤地再次生成@cursor_1游標。