詳解什么是SQL Server中的游標及相關實例

作者：金剛波 2010-04-02 09:51:37

本文將講解的是SQL Server中的游標，希望通過作者的實例講解，能讓大家對游標有更深刻的了解。

引言

我們先不講游標的什么概念，步驟及語法，先來看一個例子：

例子2

表一 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