844      阿裏雲  技術社區[雲棲]

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遊標。

最後更新：2017-04-03 16:49:11

  上一篇： 拚圖遊戲的數學原理

  下一篇： J2EE中獲取IP地址