學會使用遊標帶來的效率

1. 為何使用遊標：

 

         使用遊標(cursor)的一個主要的原因就是把集合操作轉換成單個記錄處理方式。用SQL語言從資料庫中檢索資料後，結果放在記憶體的一塊地區中，且結果往往是一個含有多個記錄的集合。遊標機制允許使用者在SQL server內逐行地訪問這些記錄，按照使用者自己的意願來顯示和處理這些記錄。

 

2. 如何使用遊標：

 

     一般地，使用遊標都遵循下列的常規步驟：

 

      (1)  聲明遊標。把遊標與T-SQL語句的結果集聯絡起來。
      (2)  開啟遊標。
      (3)  使用遊標操作資料。
      (4)  關閉遊標。

 

2.1. 聲明遊標

 

DECLARE CURSOR語句SQL-92標準文法格式：

 

 

DECLARE 遊標名 [ INSENSITIVE ] [ SCROLL ] CURSOR

 

FOR  sql-statement

 

Eg:

 

Declare MycrsrVar  Cursor

 

FOR Select *  FROM tbMyData

 

2.2  開啟遊標

 

OPEN MycrsrVar

 

當遊標被開啟時，行指標將指向該遊標集第1行之前，如果要讀取遊標集中的第1行資料，必須移動行指標使其指向第1行。就本例而言，可以使用下列操作讀取第1行資料：

 

     FETCH FIRST from E1cursor

 

     或 FETCH NEXT from E1cursor

 

 

2.3      使用遊標操作資料   

 

下面的樣本用@@FETCH_STATUS控制在一個WHILE迴圈中的遊標活動

 

/* 使用遊標讀取資料的操作如下。*/

 

DECLARE E1cursor cursor      /* 聲明遊標，預設為FORWARD_ONLY遊標 */

 

FOR SELECT * FROM c_example

 

OPEN E1cursor                /* 開啟遊標 */

 

FETCH NEXT from E1cursor     /* 讀取第1行資料*/

 

WHILE @@FETCH_STATUS = 0     /* 用WHILE迴圈控制遊標活動 */

 

BEGIN

 

          FETCH NEXT from E1cursor   /* 在迴圈體內將讀取其餘行資料 */

 

END

 

CLOSE E1cursor               /* 關閉遊標 */

 

DEALLOCATE E1cursor          /* 刪除遊標 */

 

2.4     關閉遊標

 

     使用CLOSE語句關閉遊標

 

CLOSE { { [ GLOBAL ] 遊標名 } | 遊標變數名 }

 

 

使用DEALLOCATE語句刪除遊標，其文法格式如下：

 

DEALLOCATE { { [ GLOBAL ] 遊標名 } | @遊標變數名

 

 

3.  FETCH操作的簡明文法如下：

 

   

 

FETCH

 

           [ NEXT | PRIOR | FIRST | LAST]

 

FROM

 

{ 遊標名  | @遊標變數名 } [ INTO @變數名 [，…] ]

 

 

參數說明：

 

NEXT   取下一行的資料，並把下一行作為當前行(遞增)。由於開啟遊標後，行指標是指向該遊標第1行之前，所以第一次執行FETCH NEXT操作將取得遊標集中的第1行資料。NEXT為預設的遊標提取選項。

 

INTO @變數名[,…]  把提取操作的列資料放到局部變數中。列表中的各個變數從左至右與遊標結果集中的相應列相關聯。各變數的資料類型必須與相應的結果列的資料類型匹配或是結果列資料類型所支援的隱性轉換。變數的數目必須與遊標挑選清單中的列的數目一致。

 

--------------------------------------------------------------------------------------------------------------------------------

 

每執行一個FETCH操作之後，通常都要查看一下全域變數@@FETCH_STATUS中的狀態值，以此判斷FETCH操作是否成功。該變數有三種狀態值：

 

・  0  表示成功執行FETCH語句。

 

・ -1  表示FETCH語句失敗，例如移動行指標使其超出了結果集。

 

・ -2  表示被提取的行不存在。

 

由於@@FETCH_STATU是全域變數，在一個串連上的所有遊標都可能影響該變數的值。因此，在執行一條FETCH語句後，必須在對另一遊標執行另一FETCH 語句之前測試該變數的值才能作出正確的判斷。