sqlserver遊標概念與執行個體全面解說

引言
 我們先不講遊標的什麼概念，步驟及文法，先來看一個例子：

           

       表一 OriginSalary                                                    表二 AddSalary

 

現在有2張表，一張是OriginSalary表--工資表，有三個欄位0_ID 員工號（NVARCHAR）、O_Name員工姓名（NVARCHAR）、O_Salary工資（FLOAT）。
另一張表AddSalary表—加薪表。有2個欄位，O_ID員工號、A_Salary增加工資。兩張表的O_ID是一一對應的，現在求將加薪的工資+原來的工資=現在的工資，也就是O_Salary=O_Salary+A_Salary,修改表OriginSalary的工資欄位。
對於一些不熟悉遊標的程式員來說，這個並不是什麼很難的問題，這個問題用程式來實現可能也很簡單。我先說說，用ASP.NET程式解決這個問題的思路：
1.       先獲得表OriginSalary的記錄數，寫個迴圈。
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.       迴圈3次，完成此功能。
還有一種方法就是寫預存程序，在這裡我就不列出來了。
我想大家在學習遊標之前好好想想這個問題，及一些批量處理的例子。可能有的人會說：“難道資料庫不能一行一行的處理資料嗎？將表AddSalary的資料逐行的取出，然後表 OriginSalary資料逐行的修改？”答案當然是肯定。這就是遊標概念。接下來的一章我們會好好的講講什麼是遊標？我會用遊標來解決剛才留給大家的問題。
 
1.1遊標的概念
 遊標(Cursor)它使使用者可逐行訪問由SQL Server返回的結果集。使用遊標(cursor)的一個主要的原因就是把集合操作轉換成單個記錄處理方式。用SQL語言從資料庫中檢索資料後，結果放在記憶體的一塊地區中，且結果往往是一個含有多個記錄的集合。遊標機制允許使用者在SQL server內逐行地訪問這些記錄，按照使用者自己的意願來顯示和處理這些記錄。
1.2 遊標的優點
從遊標定義可以得到遊標的如下優點，這些優點使遊標在實際應用中發揮了重要作用：
  1）允許程式對由查詢語句select返回的行集合中的每一行執行相同或不同的操作，而不是對整個行集合執行同一個操作。
  2）提供對基於遊標位置的表中的行進行刪除和更新的能力。
  3）遊標實際上作為面向集合的資料庫管理系統（RDBMS）和面向行的程式設計之間的橋樑，使這兩種處理方式通過遊標溝通起來。
1.3 遊標的使用
 講了這個多遊標的優點，現在我們就親自來揭開遊標的神秘的面紗。
 使用遊標的順序： 聲名遊標、開啟遊標、讀取資料、關閉遊標、刪除遊標。
1.3.1聲明遊標
最簡單遊標聲明：DECLARE <遊標名>CURSOR FOR<SELECT語句>；
其中select語句可以是簡單查詢，也可以是複雜的接連查詢和巢狀查詢
例子：[已表2 AddSalary為例子]
Declare mycursor cursor for select * from AddSalary
這樣我就對錶AddSalary申明了一個遊標mycursor
 
【進階備忘】
DECLARE <遊標名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT語句>
這裡我說一下遊標中級應用中的[INSENSITIVE]和[SCROLL]
INSENSITIVE
表明MS SQL SERVER 會將遊標定義所選取出來的資料記錄存放在一暫存資料表內（建立在tempdb 資料庫下）。對該遊標的讀取操作皆由暫存資料表來應答。因此，對基本表的修改並不影響遊標提取的資料，即遊標不會隨著基本表內容的改變而改變，同時也無法通過遊標來更新基本表。如果不使用該保留字，那麼對基本表的更新、刪除都會反映到遊標中。
另外應該指出，當遇到以下情況發生時，遊標將自動設定INSENSITIVE 選項。
a.在SELECT 語句中使用DISTINCT、 GROUP BY、 HAVING UNION 語句；
b.使用OUTER JOIN；
c.所選取的任意表沒有索引；
d.將實數值當作選取的列。
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用該保留字，那麼只能進行NEXT 提取操作。由此可見，SCROLL 極大地增加了提取資料的靈活性，可以隨意讀取結果集中的任一行資料記錄，而不必關閉再
重開遊標。
 
1.3.2 開啟遊標
非常簡單，我們就開啟剛才我們聲明的遊標mycursor
OPEN mycursor
 
1.3.3讀取資料
FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 遊標名  | @遊標變數名 } [ INTO @變數名 [，…] ]
參數說明：
NEXT   取下一行的資料，並把下一行作為當前行(遞增)。由於開啟遊標後，行指標是指向該遊標第1行之前，所以第一次執行FETCH NEXT操作將取得遊標集中的第1行資料。NEXT為預設的遊標提取選項。
INTO @變數名[,…]  把提取操作的列資料放到局部變數中。列表中的各個變數從左至右與遊標結果集中的相應列相關聯。各變數的資料類型必須與相應的結果列的資料類型匹配或是結果列資料類型所支援的隱性轉換。變數的數目必須與遊標挑選清單中的列的數目一致。
 
現在我們就取出mycursor遊標的資料吧！
 
當遊標被開啟時，行指標將指向該遊標集第1行之前，如果要讀取遊標集中的第1行資料，必須移動行指標使其指向第1行。就本例而言，可以使用下列操作讀取第1行資料：
Eg: Fetch next from mycursor 或則 Fetch first from mycursor
這樣我就取出了遊標裡的資料，但是光光這樣可不夠，我們還需要將取出的資料賦給變數
//聲明2個變數
declare @O_ID NVARCHAR(20)
declare @A_Salary float
//將取出的值傳入剛才聲明的2個變數
Fetch next from mycursor into @ O_ID,@ A_Salary
 
1.3.4關閉遊標
CLOSE mycursor   
         
1.3.5刪除遊標
DEALLOCATE mycursor         
 
1.3.6 執行個體訓練
如上我介紹完了遊標使用的5個步驟，那現在我們就來上上手，練慣用遊標取出表2 AddSalary的資料。
為了運行我們自己建立的遊標，我們將遊標寫在預存程序裡，方便我們看到遊標的整個使用過程。
在sqlserver2000中建立一個預存程序：
CREATE PROCEDURE PK_Test
AS
//聲明2個變數
declare @O_ID nvarchar(20)  
declare @A_Salary float
 
//聲明一個遊標mycursor，select語句中參數的個數必須要和從遊標取出的變數名相同
declare mycursor cursor for select O_ID,A_Salary from AddSalary
 
//開啟遊標
open mycursor
 
//從遊標裡取出資料賦值到我們剛才聲明的2個變數中
fetch next from mycursor into @O_ID,@A_Salary
 
//判斷遊標的狀態
//0 fetch語句成功    
//-1 fetch語句失敗或此行不在結果集中    
//-2被提取的行不存在
while (@@fetch_status=0)
begin
 
//顯示出我們每次用遊標取出的值
   print '遊標成功取出一條資料'
   print @O_ID
   print @A_Salary
 
//用遊標去取下一條記錄
   fetch next from mycursor into @O_ID,@A_Salary
end
//關閉遊標
close mycursor
//撤銷遊標
deallocate mycursor
GO
 
通過上面的注釋，我想大家都明白了整個遊標的建立過程了吧。但是我們現在還是一個抽象的瞭解，我們學任何知識，都要用於實踐，這樣才能使抽象的東西變的具體。
那我們就運行這個預存程序，看看遊標到底是怎麼取值的：
我們開啟SQLSERVER2000的查詢分析器，制定好資料庫後，我們執行預存程序
Exec PK_Test

讓我看看效果吧（）

 

通過執行個體我們可以看到遊標逐行逐行都把值都取出來了。那麼我請大家先不看下面的答案，在引言部分我剛才留個大家的問題試一下能不能解決？
現在我們寫一個預存程序解決剛才我留下來的問題吧
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
 
按照老方法，我們用查詢分析器來執行我們的預存程序，看看結果是怎麼樣的？
Exec PK_SalaryAdd
讓我看看效果吧（）

  

執行預存程序，看到我們影響了3行資料

  
用sql語句，看看錶OriginSalary現在的結果：                                                       

 

 

1.4 結束語
 很高興大家能把這個教程看完，其實這隻是遊標的最最基礎的一個應用，顯示生活的邏輯的關係中，可能有更複雜的遊標。但是我們只有學會走路，才能跑步嘛，o(∩_∩)o…。