sqlserver游標(biāo)概念與實(shí)例全面解說

引言
 我們先不講游標(biāo)的什么概念，步驟及語法，先來看一個例子：

       表一 OriginSalary                                                    表二 AddSalary

現(xiàn)在有2張表，一張是OriginSalary表--工資表，有三個字段0_ID 員工號（NVARCHAR）、O_Name員工姓名（NVARCHAR）、O_Salary工資（FLOAT）。
另一張表AddSalary表—加薪表。有2個字段，O_ID員工號、A_Salary增加工資。兩張表的O_ID是一一對應(yīng)的，現(xiàn)在求將加薪的工資+原來的工資=現(xiàn)在的工資，也就是O_Salary=O_Salary+A_Salary,修改表OriginSalary的工資字段。
對于一些不熟悉游標(biāo)的程序員來說，這個并不是什么很難的問題，這個問題用程序來實(shí)現(xiàn)可能也很簡單。我先說說，用ASP.NET程序解決這個問題的思路：
1.       先獲得表OriginSalary的記錄數(shù)，寫個循環(huán)。
2.       寫SQL語句“select * from dbo.OriginSalary as A left join dbo.AddSalary as B on A.O_ID=B.O_ID”獲得視圖。
3.       使用Dataset獲得O_Salary=O_Salary+A_Salary。
4.       寫UPDATE語句“update OriginSalary set O_Salary=”相加的值” where O_ID=”獲得值”
5.       循環(huán)3次，完成此功能。
還有一種方法就是寫存儲過程，在這里我就不列出來了。
我想大家在學(xué)習(xí)游標(biāo)之前好好想想這個問題，及一些批量處理的例子?？赡苡械娜藭f：“難道數(shù)據(jù)庫不能一行一行的處理數(shù)據(jù)嗎？將表AddSalary的數(shù)據(jù)逐行的取出，然后表 OriginSalary數(shù)據(jù)逐行的修改？”答案當(dāng)然是肯定。這就是游標(biāo)概念。接下來的一章我們會好好的講講什么是游標(biāo)？我會用游標(biāo)來解決剛才留給大家的問題。
 
1.1游標(biāo)的概念
 游標(biāo)(Cursor)它使用戶可逐行訪問由SQL Server返回的結(jié)果集。使用游標(biāo)(cursor)的一個主要的原因就是把集合操作轉(zhuǎn)換成單個記錄處理方式。用SQL語言從數(shù)據(jù)庫中檢索數(shù)據(jù)后，結(jié)果放在內(nèi)存的一塊區(qū)域中，且結(jié)果往往是一個含有多個記錄的集合。游標(biāo)機(jī)制允許用戶在SQL server內(nèi)逐行地訪問這些記錄，按照用戶自己的意愿來顯示和處理這些記錄。
1.2 游標(biāo)的優(yōu)點(diǎn)
從游標(biāo)定義可以得到游標(biāo)的如下優(yōu)點(diǎn)，這些優(yōu)點(diǎn)使游標(biāo)在實(shí)際應(yīng)用中發(fā)揮了重要作用：
  1）允許程序?qū)τ刹樵冋Z句select返回的行集合中的每一行執(zhí)行相同或不同的操作，而不是對整個行集合執(zhí)行同一個操作。
  2）提供對基于游標(biāo)位置的表中的行進(jìn)行刪除和更新的能力。
  3）游標(biāo)實(shí)際上作為面向集合的數(shù)據(jù)庫管理系統(tǒng)（RDBMS）和面向行的程序設(shè)計之間的橋梁，使這兩種處理方式通過游標(biāo)溝通起來。
1.3 游標(biāo)的使用
 講了這個多游標(biāo)的優(yōu)點(diǎn)，現(xiàn)在我們就親自來揭開游標(biāo)的神秘的面紗。
 使用游標(biāo)的順序： 聲名游標(biāo)、打開游標(biāo)、讀取數(shù)據(jù)、關(guān)閉游標(biāo)、刪除游標(biāo)。
1.3.1聲明游標(biāo)
最簡單游標(biāo)聲明：DECLARE <游標(biāo)名>CURSOR FOR<SELECT語句>；
其中select語句可以是簡單查詢，也可以是復(fù)雜的接連查詢和嵌套查詢
例子：[已表2 AddSalary為例子]
Declare mycursor cursor for select * from AddSalary
這樣我就對表AddSalary申明了一個游標(biāo)mycursor
 
【高級備注】
DECLARE <游標(biāo)名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT語句>
這里我說一下游標(biāo)中級應(yīng)用中的[INSENSITIVE]和[SCROLL]
INSENSITIVE
表明MS SQL SERVER 會將游標(biāo)定義所選取出來的數(shù)據(jù)記錄存放在一臨時表內(nèi)（建立在tempdb 數(shù)據(jù)庫下）。對該游標(biāo)的讀取操作皆由臨時表來應(yīng)答。因此，對基本表的修改并不影響游標(biāo)提取的數(shù)據(jù)，即游標(biāo)不會隨著基本表內(nèi)容的改變而改變，同時也無法通過游標(biāo)來更新基本表。如果不使用該保留字，那么對基本表的更新、刪除都會反映到游標(biāo)中。
另外應(yīng)該指出，當(dāng)遇到以下情況發(fā)生時，游標(biāo)將自動設(shè)定INSENSITIVE 選項。
a.在SELECT 語句中使用DISTINCT、 GROUP BY、 HAVING UNION 語句；
b.使用OUTER JOIN；
c.所選取的任意表沒有索引；
d.將實(shí)數(shù)值當(dāng)作選取的列。
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用該保留字，那么只能進(jìn)行NEXT 提取操作。由此可見，SCROLL 極大地增加了提取數(shù)據(jù)的靈活性，可以隨意讀取結(jié)果集中的任一行數(shù)據(jù)記錄，而不必關(guān)閉再
重開游標(biāo)。
 
1.3.2 打開游標(biāo)
非常簡單，我們就打開剛才我們聲明的游標(biāo)mycursor
OPEN mycursor
 
1.3.3讀取數(shù)據(jù)
FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游標(biāo)名  | @游標(biāo)變量名 } [ INTO @變量名 [，…] ]
參數(shù)說明：
NEXT   取下一行的數(shù)據(jù)，并把下一行作為當(dāng)前行(遞增)。由于打開游標(biāo)后，行指針是指向該游標(biāo)第1行之前，所以第一次執(zhí)行FETCH NEXT操作將取得游標(biāo)集中的第1行數(shù)據(jù)。NEXT為默認(rèn)的游標(biāo)提取選項。
INTO @變量名[,…]  把提取操作的列數(shù)據(jù)放到局部變量中。列表中的各個變量從左到右與游標(biāo)結(jié)果集中的相應(yīng)列相關(guān)聯(lián)。各變量的數(shù)據(jù)類型必須與相應(yīng)的結(jié)果列的數(shù)據(jù)類型匹配或是結(jié)果列數(shù)據(jù)類型所支持的隱性轉(zhuǎn)換。變量的數(shù)目必須與游標(biāo)選擇列表中的列的數(shù)目一致。
 
現(xiàn)在我們就取出mycursor游標(biāo)的數(shù)據(jù)吧！
 
當(dāng)游標(biāo)被打開時，行指針將指向該游標(biāo)集第1行之前，如果要讀取游標(biāo)集中的第1行數(shù)據(jù)，必須移動行指針使其指向第1行。就本例而言，可以使用下列操作讀取第1行數(shù)據(jù)：
Eg: Fetch next from mycursor 或則 Fetch first from mycursor
這樣我就取出了游標(biāo)里的數(shù)據(jù)，但是光光這樣可不夠，我們還需要將取出的數(shù)據(jù)賦給變量
//聲明2個變量
declare @O_ID NVARCHAR(20)
declare @A_Salary float
//將取出的值傳入剛才聲明的2個變量
Fetch next from mycursor into @ O_ID,@ A_Salary
 
1.3.4關(guān)閉游標(biāo)
CLOSE mycursor   
         
1.3.5刪除游標(biāo)
DEALLOCATE mycursor         
 
1.3.6 實(shí)例訓(xùn)練
如上我介紹完了游標(biāo)使用的5個步驟，那現(xiàn)在我們就來上上手，練習(xí)用游標(biāo)取出表2 AddSalary的數(shù)據(jù)。
為了運(yùn)行我們自己創(chuàng)建的游標(biāo)，我們將游標(biāo)寫在存儲過程里，方便我們看到游標(biāo)的整個使用過程。
在sqlserver2000中新建一個存儲過程：
CREATE PROCEDURE PK_Test
AS
//聲明2個變量
declare @O_ID nvarchar(20)  
declare @A_Salary float
 
//聲明一個游標(biāo)mycursor，select語句中參數(shù)的個數(shù)必須要和從游標(biāo)取出的變量名相同
declare mycursor cursor for select O_ID,A_Salary from AddSalary
 
//打開游標(biāo)
open mycursor
 
//從游標(biāo)里取出數(shù)據(jù)賦值到我們剛才聲明的2個變量中
fetch next from mycursor into @O_ID,@A_Salary
 
//判斷游標(biāo)的狀態(tài)
//0 fetch語句成功    
//-1 fetch語句失敗或此行不在結(jié)果集中    
//-2被提取的行不存在
while (@@fetch_status=0)
begin
 
//顯示出我們每次用游標(biāo)取出的值
   print '游標(biāo)成功取出一條數(shù)據(jù)'
   print @O_ID
   print @A_Salary
 
//用游標(biāo)去取下一條記錄
   fetch next from mycursor into @O_ID,@A_Salary
end
//關(guān)閉游標(biāo)
close mycursor
//撤銷游標(biāo)
deallocate mycursor
GO
 
通過上面的注釋，我想大家都明白了整個游標(biāo)的創(chuàng)建過程了吧。但是我們現(xiàn)在還是一個抽象的了解，我們學(xué)任何知識，都要用于實(shí)踐，這樣才能使抽象的東西變的具體。
那我們就運(yùn)行這個存儲過程，看看游標(biāo)到底是怎么取值的：
我們打開SQLSERVER2000的查詢分析器，制定好數(shù)據(jù)庫后，我們執(zhí)行存儲過程
Exec PK_Test

讓我看看效果吧（如圖）

通過實(shí)例我們可以看到游標(biāo)逐行逐行都把值都取出來了。那么我請大家先不看下面的答案，在引言部分我剛才留個大家的問題試一下能不能解決？
現(xiàn)在我們寫一個存儲過程解決剛才我留下來的問題吧
CREATE PROCEDURE PK_SalaryAdd
AS
declare @O_ID nvarchar(20),@A_Salary float
declare mycursor cursor for select O_ID,A_Salary from AddSalary
open mycursor
fetch next from mycursor into @O_ID,@A_Salary
while(@@fetch_status = 0)
begin
Update OriginSalary set O_Salary=O_Salary+@A_Salary where O_ID=@O_ID
fetch next from mycursor into @O_ID,@A_Salary
end
close mycursor
deallocate mycursor
GO
 
按照老方法，我們用查詢分析器來執(zhí)行我們的存儲過程，看看結(jié)果是怎么樣的？
Exec PK_SalaryAdd
讓我看看效果吧（如圖）

執(zhí)行存儲過程，看到我們影響了3行數(shù)據(jù)

  
用sql語句，看看表OriginSalary現(xiàn)在的結(jié)果：                                                       

1.4 結(jié)束語
 很高興大家能把這個教程看完，其實(shí)這只是游標(biāo)的最最基礎(chǔ)的一個應(yīng)用，顯示生活的邏輯的關(guān)系中，可能有更復(fù)雜的游標(biāo)。但是我們只有學(xué)會走路，才能跑步嘛。