儲存過程——遊標

• 目錄
  • 1. 遊標簡介
    • 1.0 理解定義
    • 1.1 遊標的主要作用
    • 1.2 遊標的優缺點
    • 1.3 遊標生命週期
    • 1.4 基本語法
  • 2. 遊標示例
    • 2.0 準備測試資料
    • 2.1 示例1-FORWARD_ONLY型別遊標
    • 2.2 示例2-SCROLL型別遊標
    • 2.3 示例3-使用遊標進行更新和刪除資料

2020年5月27日 21:10:00

---

1. 遊標簡介

1.0 理解定義

SQL遊標(cursor)是一個資料庫物件，用於從結果集中檢索某一行的資料。

遊標是系統為使用者開設的一個資料緩衝區，存放SQL語句的執行結果。每個遊標區都有一個名字,使用者可以用SQL語句逐一從遊標中獲取記錄，並賦給主變數，交由主語言進一步處理。

在程式設計中，我們使用諸如for或while之類的迴圈一次遍歷一項，遊標遵循相同的方法。當在SQL中，應用程式邏輯需要一次只處理一行，而不是一次處理整個結果集。可以使用遊標完成此操作。

怎麼理解“為了處理查詢的結果集中特定行的資料，我們使用遊標處理”? 其實，遊標的英文單詞是cursor,也可以翻譯為游標，其實類比我們編輯文件，當想要編輯具體的某一行的時候，我們需要使用游標移到該行進行編輯，在SQL中游標的作用是一樣的。

當然，本質上就是個定義在結果集上的指標，我們可以控制該指標遍歷結果集。

這裡補充一下：理論上SQL編寫是按照面向集合的思維模式，而我們使用遊標則又回到了程式導向的思維模式。此中思想非三言二語可說明白的，相關知識可以參考《SQL進階教程》2.6章節！

1.1 遊標的主要作用

1. 定位到結果集中的某一行。
2. 對當前位置的資料進行讀寫。
3. 可以對結果集中的資料單獨操作，而不是整行執行相同的操作。
4. 是面向集合的資料庫管理系統和麵向行的程式設計之間的橋樑。

1.2 遊標的優缺點

1. 優點：參考上文中游標的作用
2. 缺點：濫用遊標會影響系統效能。
   一般來說，有一個共識：能不用遊標就不要用遊標。
   事實上，編寫SQL語句的時候大多數的情形下是沒有必要使用遊標的。

1.3 遊標生命週期

遊標的生命週期：

1. 宣告遊標(Declare Cursor)
2. 開啟遊標(Open Cursor)
3. 提取遊標(Fetch Cursor)
4. 關閉遊標(Close Cursor)
5. 釋放遊標(Deallocate Cursor)

使用遊標的過程如下：

注：圖片來源 https://www.sqlservertutorial.net/sql-server-stored-procedures/sql-server-cursor/

1.4 基本語法

①完整的宣告遊標

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

【說明】方括號中的關鍵之是可選的，具體作用如下：

1. 作用域

   • Local：遊標作用域為區域性，只在定義它的批處理、函式和儲存過程中有效。
   • Global：遊標作用域為全域性，由連線執行的任何儲存過程或批處理中，都可以引用該遊標。
   • 預設值是Local

2. 遊標方向

   • Forward_Only:指定遊標智慧從第一行滾到最後一行，種遊標稱為：只進遊標。
   • Scroll：指定遊標在定義的資料集中向任何方向，或任何位置移動。
   • 預設是Forward_Only

3. 遊標讀取的資料和基表資料關係

   • Static表明：遊標一旦指定了select查詢出的結果集，之後任何對於基表（即：select語句所查詢的表）內資料的更改不會影響到遊標的內容。該種遊標稱為靜態遊標

   • Dynamic和Static完全相反的選項,當底層資料庫更改時，遊標的內容也隨之得到反映，在下一次fetch中，資料內容會隨之改變。該種遊標稱為動態遊標。

   • KeySet:指明當再遊標被開啟時遊標中的列的順序時固定的，遊標只維持其所依賴的基表的鍵

   • Fast_Forward:指明一個Forward_Only且Read_Only型遊標。注意：一旦宣告瞭Fast_Forward,則之前就不可以選擇Scroll型別的遊標。同樣，在之後也就不能使用Scroll_Locks和Optimistic選項。

   • 預設值是Dynamic

4. 遊標是否鎖定資料

   • Read_Only意味著宣告的遊標只能讀取資料,遊標不能做任何更新操作

   • Scroll_Locks是另一種極端，將讀入遊標的所有資料進行鎖定，防止其他程式進行更改，以確保更新的絕對成功

   • Optimistic是相對比較好的一個選擇，不鎖定任何資料，當需要在遊標中更新資料時,如果底層表資料更新，則遊標內資料更新或刪除會不成功，如果，底層表資料未更新，則遊標內表資料可以更新或刪除

5. Type_Warning:指明若遊標型別被修改成與使用者定義的型別不同時，將傳送一個警告資訊給客戶端。

6. Update[Of colunm_name[,...n]]:定義利用遊標可更新的列。若果列出了Of colunm_name[,...n]，則只允許修改列出的列

7. 其實，從上面可以看出遊標的宣告是有許多的可選項。
   但是一般來說，只要記住遊標宣告的預設值。一般實際開發中，如無必要則使用預設值即可。

②開啟遊標

OPEN cursor_name

③提取行資料到指定的變數列表中

--提取下一行資料
FETCH NEXT FROM cursor_name INTO variateList;
--提取上一行資料
FETCH PRIOR FROM cursor_name INTO variateList;
--提取第一行資料
FETCH FIRST FROM cursor_name INTO variateList;
--提取最後一行資料
FETCH LAST FROM cursor_name INTO variateList;
--提取第3行資料（提取指定的行）
FETCH ABSOLUTE 3 FROM cursor_name INTO variateList;
--提取當前行的上一行(複數為向後，正數為向前)
FETCH RELATIVE -1 FROM cursor_name INTO variateList;

【注意】：

• 遊標只有上述的6種移動方式，但是要注意的是：一旦在宣告遊標的時候，定義為Forward_Only(預設值),則提取行資料中時候，只能是Fetch next

• INTO列表中宣告的變數數目必須與所選列的數目相同。即：select的結果集中有幾列，則INTO後的變數就該有幾個。

④關閉遊標

CLOSE cursor_name

⑤釋放遊標

DEALLOCATE cursor_name

---

2. 遊標示例

2.0 準備測試資料

USE [db_Tome1]
GO

CREATE TABLE [dbo].[szmUser]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nchar](10) NULL
)

Insert into szmUser (UserName) values (N'張三'),(N'李四'),(N'王五'),(N'趙六'), (N'Tom'),(N'Jerry'),(N'Bob');

GO

2.1 示例1-FORWARD_ONLY型別遊標

使用FORWARD_ONLY宣告只進遊標，實現從頭到尾提取行資料

DECLARE test_cur CURSOR FORWARD_ONLY --宣告遊標，定義為FORWARD_ONLY型別
FOR  SELECT * FROM szmUser--遊標作用的結果集

OPEN test_cur --開啟遊標

DECLARE @userId INT ,@userName NCHAR(10)--宣告標量用於儲存行資料


WHILE ( @@fetch_status = 0 )          
    BEGIN
	FETCH NEXT FROM test_cur INTO @userId ,@userName--提取下一行資料並存入定義的變數中
	PRINT @userName--列印資料
    END

CLOSE test_cur--關閉遊標

DEALLOCATE test_cur--釋放遊標

訊息框列印資訊如下：

張三        
李四        
王五        
趙六        
Tom       
Jerry     
Bob       
Bob    

【注意】：

• 全域性變數@@Fetch_Status的值表示遊標提取狀態資訊，該狀態用於判斷Fetch語句返回資料的有效性。
  當執行一條Fetch語句之後，@@Fetch_Status可能出現3種值：

  狀態碼	含義
  0	Fetch語句成功
  -1	Fetch語句失敗或行不在結果集中
  -2	提取的行不存在

• 這裡宣告的遊標定義為FORWARD_ONLY型別，所以只能使用FETCH NEXTQ提取資料，若是使用其他的提取資料的方式則會報錯，比如使用FETCH LAST，則報錯：
  fetch: 提取型別 last 不能與只進遊標一起使用。

2.2 示例2-SCROLL型別遊標

使用SCROLL宣告遊標，實現讀取特定行資料

DECLARE test_cur CURSOR scroll --宣告遊標，定義為FORWARD_ONLY型別
FOR  SELECT * FROM szmUser--遊標作用的結果集

OPEN test_cur --開啟遊標

DECLARE @userId INT ,@userName NCHAR(10)--宣告標量用於儲存行資料

FETCH FIRST FROM test_cur INTO　@userId, @userName--提取當前結果集的第一行
PRINT CAST(@userId as varchar)+':'+@userName

FETCH LAST FROM test_cur INTO @userId ,@userName--提取當前結果集的最後一行
PRINT CAST(@userId as varchar)+':'+@userName

FETCH prior From test_cur INTO @userId ,@userName--提取當前遊標指向的上一行資料
PRINT CAST(@userId as varchar)+':'+@userName

FETCH ABSOLUTE 2 FROM test_cur INTO @userId ,@userName--提取當前結果集中的第二行資料
PRINT CAST(@userId as varchar)+':'+@userName

FETCH RELATIVE 1 FROM test_cur INTO @userId ,@userName--提取當前遊標指向的下一行資料
PRINT CAST(@userId as varchar)+':'+@userName

FETCH RELATIVE -1 FROM test_cur INTO @userId ,@userName--提取當前遊標指向的上一行資料
PRINT CAST(@userId as varchar)+':'+@userName

CLOSE test_cur--關閉遊標

DEALLOCATE test_cur--釋放遊標

訊息框列印資訊如下：

1:張三        
7:Bob       
6:Jerry     
2:李四        
3:王五        
2:李四        

2.3 示例3-使用遊標進行更新和刪除資料

使用遊標對結果集中資料進行更改和刪除

示例：刪除SELECT * FROM szmUser結果集中的名叫張三的的人，同時將該結果集中名叫李四的名字改為李四四

DECLARE	test_cur CURSOR SCROLL 
FOR  SELECT * FROM szmUser


OPEN test_cur

DECLARE @userId int ,@userName nchar(10)

FETCH First FROM test_cur INTO @userId,@userName--定位遊標到第一行（注意這裡，一定要將遊標首先定位到某一行）

WHILE (@@FETCH_STATUS=0)
BEGIN 
	IF @userName='李四'
		BEGIN 
		Update szmUser Set UserName='李四四' WHERE CURRENT OF  test_cur  --修改當前行
		END

	IF @userName='張三'
		BEGIN 
		DELETE szmUser  WHERE CURRENT OF  test_cur  --刪除當前行
		END

     FETCH NEXT FROM test_cur INTO @userId ,@userName  --移動遊標
 END

 CLOSE test_cur

 DEALLOCATE test_cur

【注意】：

• 在這裡使用while迴圈一定要首先將定位遊標的起始位置，類比其它型別的程式語言中迴圈語句，迴圈就要有起始位置，步長，結束位置

• 注意：一開始，使用的測試表雖然定義了標識規範及標識增量，但是沒有定義主鍵，測試的時候報錯：遊標是隻讀的。 語句已終止。,其實只是因為表沒有主鍵或唯一性約束，所以CURRENT OF test_cur會報錯
  當然，也是可以在更新或刪除語句中使用where指定具體的記錄。

2.4 示例4-靜態遊標和動態遊標演示

2.4.0 說明

遊標在宣告的時候，可以定義是靜態遊標還是動態遊標，遊標預設是動態遊標。

靜態遊標在開啟時會將資料集儲存在tempdb中，因此顯示的資料與遊標開啟時的資料集保持一致，在遊標開啟以後對資料庫的更新不會顯示在遊標中。

動態遊標在開啟後會反映對資料庫的更改。所有UPDATE、INSERT 和 DELETE 操作都會顯示在遊標的結果集中，結果集中的行資料值、順序和成員在每次提取時都會改變。

簡而言之：靜態遊標的資料是固定的，不會因為基表的改變而改變；動態遊標的資料是隨著基表變化而變化的。

2.4.1 示例-靜態遊標

DECLARE @userId INT , @userName NCHA(10)                    --宣告變數，儲存行資料
DECLARE test_cur CURSOR STATIC				    --宣告靜態遊標
FOR SELECT  * FROM    szmUser				    --遊標遍歷的結果集
OPEN test_cur					            --開啟遊標
FETCH NEXT FROM test_cur INTO @userId,@userName             --取資料
WHILE ( @@fetch_status = 0)                                 --判斷是否還有據
    BEGIN
        PRINT RTRIM(@userId) +':'+ @userName
		UPDATE szmUser SET UserName='測試' WHEREid=4   --測試靜態動態用
        FETCH NEXT FROM test_cur INTO @userId,@userName        --遊標進入下一行
    END
CLOSE test_cur
DEALLOCATE test_cur

執行結果：

2:李四        
3:王五        
4:趙六        
5:Tom       
6:Jerry     
7:Bob       
8:Mark      

【說明】：我們定義的是靜態遊標，所以一旦當結果集進遊標區後，基表的資料發生改變遊標讀取資料依舊是最初入遊標區的資料。
所以在這裡，當遊標提取一行資料後，我們就把基表中id=的userName改為“測試”，但是遊標繼續執行，讀取的還是初進入遊標區的資料，即id=4,userName=趙六

2.4.2 示例-動態遊標

宣告遊標的時候，預設就是動態遊標，所以這裡我們只要把上面的程式碼中的STATIC刪除即可，執行結果如下,你好發現在基表中對資料的修改，直接是反應到已宣告的遊標中。我們修改的id=4的使用者名稱，直接顯示在遊標的資料中。

2:李四        
3:王五        
4:測試  --修改基表資料直接作用在已宣告的遊標中      
5:Tom       
6:Jerry     
7:Bob       
8:Mark      

2.4.3 動態和靜態區別

• 宣告遊標預設是動態遊標，對基表中資料的改變影響已宣告的動態遊標，不影響已宣告的靜態遊標。

  原則是應該儘量避免使用靜態遊標

• 動態遊標的開啟速度比靜態遊標的開啟速度快。當開啟靜態遊標時，必須生成內部臨時工作表，而動態遊標則不需要。

• 在聯接中，靜態遊標的速度可能比動態遊標的速度快。因為動態遊標在滾動時反應對結果集內的各行資料所做的更改，它會消耗資源去檢測基表的更改，因此對於複雜的查詢，且不需要反映基表的更新的遊標的處理應將其定義為靜態遊標。

---

3. 使用原則

• Rule 1：能不用遊標則不用遊標
• 用完之後是一定要及時的關閉和釋放遊標
• 不要在有大量資料的結果集中定義遊標
• 儘量避免使用靜態遊標
• 儘量不要在遊標上更新資料
• 只進遊標（First-Forward)若是隻讀，可以使用Fast-Forward定義遊標

---

4. 參考

• Cursors In SQL Server

• SQL Server CURSOR

• SQL遊標(cursor)詳細說明及內部迴圈使用示例

• T-SQL查詢進階-10分鐘理解遊標

• SQL SERVER 遊標使用

• SQL中的遊標

• SQL Server遊標