萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> sql server 中游標的認識

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

copyright © 萬盛學電腦網 all rights reserved