SQL Server之遊標

標籤：column   記錄   sql   https   串連   過程   com   智能   變數   

部分參考自：https://www.cnblogs.com/knowledgesea/p/3699851.html

一、什麼是遊標

　　遊標是取用一組資料並能夠一次與一個單獨的記錄進行互動的方法，可以定位到結果集中的某一行，多資料進行讀寫，也可以移動遊標定位到你所需要的行中進行操作資料。有時，確實不能通過在整個行集中修改或者甚至選取資料來獲得所需要的結果，故需要逐一進行處理。

　　主要用處（預存程序）：

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

二、遊標的生命週期：

• 　　聲明遊標
• 　　開啟遊標
• 　　使用或導航遊標
• 　　關閉遊標
• 　　釋放遊標

三、遊標的使用

　　1. 聲明遊標

　　　　文法：

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 ] ] ]

　　　　參數說明：

• cursor_name：遊標名稱。
• Local：範圍為局部，只在定義它的批處理，預存程序或觸發器中有效。
• Global：範圍為全域，由串連執行的任何預存程序或批處理中，都可以引用該遊標。
• [Local | Global]:預設為local。
• Forward_Only:指定遊標智能從第一行滾到最後一行。Fetch Next是唯一支援的提取選項。如果在指定Forward_Only是不指定Static、KeySet、Dynamic關鍵字，預設為Dynamic遊標。如果Forward_Only和Scroll沒有指定，Static、KeySet、Dynamic遊標預設為Scroll，Fast_Forward預設為Forward_Only
• Static:靜態資料指標
• KeySet：鍵集遊標
• Dynamic：動態資料指標，不支援Absolute提取選項
• Fast_Forward：指定啟用了效能最佳化的Forward_Only、Read_Only遊標。如果指定啦Scroll或For_Update，就不能指定他啦。
• Read_Only:不能通過遊標對資料進行刪改。
• Scroll_Locks：將行讀入遊標是，鎖定這些行，確保刪除或更新一定會成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
• Optimistic：指定如果行自讀入遊標以來已得到更新，則通過遊標進行的定點更新或定位刪除不成功。當將行讀入遊標時，sqlserver不鎖定行，它改用timestamp列值的比較結果來確定行讀入遊標後是否發生了修改，如果表不行timestamp列，它改用校正和值進行確定。如果已修改改行，則嘗試進行的定點更新或刪除將失敗。如果指定啦Fast_Forward,則不能指定他。
• Type_Warning:指定將遊標從所請求的類型隱式轉換為另一種類型時向用戶端發送警告資訊。
• For Update[of column_name ,....] :定義遊標中可更新的列。

　　2. 開啟遊標

　　　　文法：

OPEN [ Global ] cursor_name | cursor_variable_name

　　3. 使用和導航遊標

　　　　文法：

FETCH[ [Next|prior|Frist|Last|Absoute n|Relative n ]from ][Global] cursor_name[into @variable_name[,....]]

　　　　參數說明：

• Frist：結果集的第一行
• Prior：當前位置的上一行
• Next：當前位置的下一行
• Last：最後一行
• Absoute n：從遊標的第一行開始數，第n行。
• Relative n：從當前位置數，第n行。
• Into @variable_name[,...] : 將提取到的資料存放到變數variable_name中。

　　　　例如：FETCH NEXT FROM 遊標名稱 INTO 變數名1

　　　　意為發出第一個FETCH，即表明要檢索特定記錄的命令，並將該值放置在哪一個變數中。

　　　　每當提取一行時，就會更新@@FETCH_STATUS，通過檢測全域變數@@Fetch_Status的值，獲得提取狀態資訊。
　　　　其可能的值是：
　　　　0 Fetch語句成功——一切正常；
　　　　-1 Fetch語句失敗——找不到記錄（還沒有到達遊標的末尾，但自開啟遊標以後，記錄已經被刪除）；
　　　　-2 Fetch語句失敗——這一次是由於已經超出了遊標中的最後一條（或者第一條）記錄。

　　4. 關閉遊標

　　　　 遊標開啟後，伺服器會專門為遊標分配一定的記憶體空間存放遊標操作的資料結果集，同時使用遊標也會對某些資料進行封鎖。所以遊標一旦用過，應及時關閉，避免伺服器資源浪費。

CLOSE [ Global ] cursor_name | cursor_variable_name

　　5. 釋放遊標

　　　　刪除遊標，釋放資源

DEALLOCATE [ Global ] cursor_name | cursor_variable_name

 

四、遊標使用執行個體：

    DECLARE sc_cursor CURSOR     FOR SELECT Grade FROM SC WHERE Cno=@course_cno    OPEN sc_cursor    FETCH NEXT FROM sc_cursor INTO @stu_grade    IF(@@FETCH_STATUS<>0)        PRINT ‘沒有考該課程的學生‘    WHILE @@FETCH_STATUS=0    BEGIN        IF(@stu_grade<60)            SET @stu_ccount+=1        IF(@stu_grade>=60 AND @stu_grade<90)            SET @stu_bcount+=1        IF(@stu_grade>=90 AND @stu_grade<=100)            SET @stu_acount+=1        FETCH NEXT FROM sc_cursor INTO @stu_grade    END    CLOSE sc_cursor    DEALLOCATE sc_cursor

 

SQL Server之遊標