星期五, 7月 29, 2011

[SQL] CURSOR

SQL Server DML 語法(SELECT、UPDATE 和 DELETE)是以資料集為資料處理單位,方便且有效率,而 Cursor 則是以記錄為資料處理單位,對於資料操作彈性較大。

SQL Cursor 的基礎架構
DECLARE curTemp CURSOR Local Fast_Forward -- 宣告 Cursor 及其資料來源
  FOR
      (
        SELECT Col1,Col2,Col3......
        FROM SourceTable
        WHERE Condition
      )
 
OPEN curTemp -- 打開 Cursor,並建立 Cursor 與資料表關連
 
DECLARE @var1 as .....
DECLARE @var2 as .....
 
FETCH NEXT FROM curTemp INTO @var1,@var2 -- 將資料存進變數中
  WHILE (@@FETCH_STATUS = 0) -- 檢查是否有讀取到資料
    BEGIN  
  
        .................... 
 
        FETCH NEXT FROM curTemp INTO @var1,@var2  
    END
 
CLOSE curTemp -- 關閉 Cursor,並關閉 Cursor 與資料表連結
DEALLOCATE curTemp -- 將 Cursor 物件移除
    使用 Cursor 會導致 SQLServer 效能不彰,應視為最後手段,但假如使用時請注意下列事項:
    1. 盡量由前往後讀取資料就好 FORWARD_ONLY 和 FETCH NEXT 為預設值,不要使用 SCROLL 和 FETCH PRIOR、FETCH FIRST、FETCH LAST 等語法,個人習慣通常都會設定 FAST_FORWARD 來提高效率。
    2. 不要利用 Cursor 來修改和刪除資料,能明確指定為 READ_ONLY 較好。
    3. 避免在 Cursor 中進行排序

    判斷 Cursor 中的最後一筆資料
     
    利用 @@CURSOR_ROWS 來查詢最近一次 OPEN 的 Cursor 中有多少筆資料。
    DECLARE @Temp Table (EmpNO char(5),EmpName nchar(8))
    INSERt INTO @Temp VALUES ('00001','張三')
    INSERt INTO @Temp VALUES ('00002','李四')
    INSERt INTO @Temp VALUES ('00003','王五')
    
    -- Cursor 的 T-SQL 延伸語法
    DECLARE curTemp CURSOR STATIC -- 宣告此 Cursor 為 STATIC
      FOR
         (
           SELECT EmpNO,EmpName
           FROM @Temp
           WHERE EmpNO IN ('00001','00002')
         )
    
    OPEN curTemp
     
    DECLARE @EmpNO as char(5),@EmpName nchar(8)
    DECLARE @Count as smallint 
    
    SET @Count = 0 -- 設定一個計數變數
    
    FETCH NEXT FROM curTemp INTO @EmpNO,@EmpName
      WHILE (@@FETCH_STATUS = 0)
        BEGIN  
          SET @Count = @Count + 1
     
          IF @Count = @@CURSOR_ROWS
              PRINT '最後一筆資料為 ' + @EmpName
       
          FETCH NEXT FROM curTemp INTO @EmpNO,@EmpName
        END
     
    CLOSE curTemp
    DEALLOCATE curTemp
    
      說明:
    1. 由於動態資料指標會反映所有變更,因此,Cursor 內資料列數會不斷改變,@@CURSOR_ROWS 永遠不可能明確指出 Cursor 內有多少資料列數,也因此必須把 Cursor 設為靜態指標(STATIC)。
    2. 宣告 Cursor 為 STATIC,會先把資料放進 TempDB 內,應盡量縮小資料量,以免 TempDB 瞬間爆增,影響效能。
    • 2013 DBA 天團試題

    沒有留言:

    張貼留言