Sql Server系列：遊標

libingql發表於2014-12-15
SQLServer

1. 遊標簡介

　　遊標是一種處理資料的方法，主要用於儲存過程、觸發器和Transact-SQL指令碼中。SELECT語句返回的是一個結果集，遊標能夠從包含多條資料記錄的結果集中每次提取一條記錄。

　　遊標的特點：

　　◊ 允許對由SELECT查詢語句返回的行集中的每一行執行相同或者不同的操作，而不是對整個集合執行同一個操作。

　　◊ 提供對基於遊標位置的表中的行進行刪除和更新。

2. 遊標的基本操作

2.1 宣告遊標

　　宣告遊標語法：

ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]

2.2 開啟遊標

　　在使用遊標之前，必須開啟遊標。

　　開啟遊標的語法格式：

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

2.3 讀取遊標中的資料

　　開啟遊標之後，可以讀取遊標中的資料。FETCH用來讀取遊標中的某一行資料。

　　FETCH語句的語法格式：

FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ]

2.4 關閉遊標

　　在不使用遊標的時候，可以將其關閉，以釋放遊標所佔用的伺服器資源。

　　關閉遊標CLOSE語法格式：

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

2.5 釋放遊標

　　遊標操作的結果集空間雖然被釋放了，但是遊標結構本身也會佔用一定的資源，所以在使用完遊標之後，為了收回被遊標佔用的資源，應該講遊標釋放。

　　釋放遊標DEALLOCATE語法格式：

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

3. 遊標應用

3.1 使用遊標變數

DECLARE @CUR_Product CURSOR                        -- 宣告遊標變數

DECLARE CUR_Product CURSOR FOR                     -- 建立遊標
SELECT [ProductID],[ProductName] FROM [dbo].[Product]

OPEN CUR_Product                                   -- 開啟遊標
SET @CUR_Product = CUR_Product                     -- 為遊標變數賦值

FETCH NEXT FROM @CUR_Product                       -- 從遊標變數中讀取值
WHILE @@FETCH_STATUS = 0                           -- 判斷FETCH語句是否獲取到記錄
    BEGIN
        FETCH NEXT FROM @CUR_Product               -- 讀取遊標變數中的資料
    END
CLOSE @CUR_Product                                 -- 關閉遊標
DEALLOCATE @CUR_Product                            -- 釋放遊標

3.2 使用遊標為變數賦值

DECLARE @ProductID INT

DECLARE CUR_Product CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [ProductID] FROM [dbo].[Product]
ORDER BY [ProductID] DESC

OPEN CUR_Product
FETCH NEXT FROM CUR_Product INTO @ProductID
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @ProductID
        FETCH NEXT FROM CUR_Product INTO @ProductID
    END
CLOSE CUR_Product
DEALLOCATE CUR_Product
DECLARE @ProductID INT
DECLARE @ProductName VARCHAR(50)

DECLARE CUR_Product CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [ProductID],[ProductName] FROM [dbo].[Product]

OPEN CUR_Product
FETCH NEXT FROM CUR_Product INTO @ProductID,@ProductName
WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT CONVERT(VARCHAR, @ProductID) + ' ' + @ProductName
        FETCH NEXT FROM CUR_Product INTO @ProductID,@ProductName
    END
CLOSE CUR_Product
DEALLOCATE CUR_Product

相關文章