SQL SERVER 遊標的使用方法(ZT)

在這一步中，需要指定遊標的屬性和根據要求產生的結果集。有兩種方法可以指定一個遊標。 形式1（ANSI 92） DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY | UPDATE ][OF column_list]}] 形式2 DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] FOR select_statement [FOR {READ ONLY | UPDATE ][OF column_list]}] INSENSITIVE關鍵字指明要為檢索到的結果集建立一個臨時拷貝，以後的資料從這個臨時拷貝中擷取。如果在後來遊標處理的過程中，原有基表中資料發生了改變，那麼它們對於該遊標而言是不可見的。這種不敏感的遊標不允許資料更改。 SCROLL關鍵字指明遊標可以在任意方向上滾動。所有的fetch選項（first、last、next、relative、absolute）都可以在遊標中使用。如果忽略該選項，則遊標只能向前滾動（next）。 Select_statement指明SQL語句建立的結果集。Transact SQL語句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在遊標聲明的選擇語句中不允許使用。 READ ONLY指明在遊標結果集中不允許進行資料修改。 UPDATE關鍵字指明遊標的結果集可以修改。 OF column_list指明結果集中可以進行修改的列。預設情況下（使用UPDATE關鍵字），所有的列都可進行修改。 LOCAL關鍵字指明遊標是局部的，它只能在它所聲明的過程中使用。 GLOBAL關鍵字使得遊標對於整個串連全域可見。全域的遊標在串連啟用的任何時候都是可用的。只有當串連結束時，遊標才不再可用。 FORWARD_ONLY指明遊標只能向前滾動。 STATIC的遊標與INSENSITIVE的遊標是相同的。 KEYSET指明選取的行的順序。SQL Server將從結果集中建立一個臨時關鍵字集。如果對資料庫的非關鍵字列進行了修改，則它們對遊標是可見的。因為是固定的關鍵字集合，所以對關鍵字列進行修改或新插入列是不可見的。 DYNAMIC指明遊標將反映所有對結果集的修改。 SCROLL_LOCK是為了保證遊標操作的成功，而對修改或刪除加鎖。 OPTIMISTIC指明哪些通過遊標進行的修改或者刪除將不會成功。 注意： · 如果在SELECT語句中使用了DISTINCT、UNION、GROUP BY語句，且在選擇中包含了彙總運算式，則遊標自動為INSENSITIVE的遊標。 · 如果基表沒有唯一的索引，則遊標建立成INSENSITIVE的遊標。 ·  如果SELECT語句包含了ORDER BY，而被ORDER BY的列並非唯一的行標識，則DYNAMIC遊標將轉換成KEYSET遊標。如果 KEYSET遊標不能開啟，則將轉換成INSENSITIVE遊標。使用SQL ANSI-92文法定義的遊標同樣如此，只是沒有INSENSITIVE 關鍵字而已。 ii. 開啟遊標 開啟遊標就是建立結果集。遊標通過DECLARE語句定義，但其實際的執行是通過OPEN語句。文法如下： OPEN { { [GLOBAL] cursor_name } | cursor_variable_name} GLOBAL指明一個全域遊標。 Cursor_name是被開啟的遊標的名稱。       

Cursor_variable_name是所引用遊標的變數名。該變數應該為遊標類型。 在 遊標被開啟之後，系統變數@@cursor_rows可以用來檢測結果集的行數。@@cursor_rows為負數時，表示遊標正在被非同步遷移，其絕對值 （如果@@cursor_rows為－５，則絕對值為５）為當前結果集的行數。非同步遊標使使用者在遊標被完全遷移時仍然能夠訪問遊標的結果。 iii. 從遊標中取值 在 從遊標中取值的過程中，可以在結果集中的每一行上來回移動和處理。如果遊標定義成了可滾動的（在聲明時使用SCROLL關鍵字），則任何時候都可取出結果 集中的任意行。對於非滾動的遊標，只能對當前行的下一行實施取操作。結果集可以取到局部變數中。Fetch命令的文法如下： FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n | @nvar}] FROM [GLOBAL] cursor_name} | cursor_variable_name} [INTO @variable_name ][,……n]] NEXT指明從當前行的下一行取值。 PRIOR指明從當前行的前一行取值。 FIRST是結果集的第一行。 LAST是結果集的最後一行。 ABSOLUTE n表示結果集中的第n行，該行數同樣可以通過一個局部變數傳播。行號從0開始，所以n為0時不能得到任何行。 RELATIVE n表示要取出的行在當前行的前n行或後n行的位置上。如果該值為正數，則要取出的行在當前行前n行的位置上，如果該值為負數，則返回當前行的後n行。 INTO @cursor_variable_name 表示遊標列值儲存的地方的變數列表。該列表中的變數數應該與DECLARE語句中選擇語句所使用的變數數相同。變數的資料類型也應該與被選擇列的資料類型 相同。直到下一次使用FETCH語句之前，變數中的值都會一直保持。 每一次FETCH的執行都儲存在系統變數@@fetch_status 中。如果FETCH成功，則@@fetch_status被設定成0。@@fetch_status為-1表示已經到達了結果集的一部分（例如，在遊標被 開啟之後，基表中的行被刪除）。@@fetch_status可以用來構造遊標處理的迴圈。 例如： DECLARE @iname char(20), @fname char(20) OPEN author_cur FETCH FIRST FROM author_cur INTO @iname, @fname WHILE @@fetch_status = 0 BEGIN IF @fname = ‘Albert’ PRINT “Found Albert Ringer” ELSE Print “Other Ringer” FETCH NEXT FROM author_cur INTO @iname, @fname END iv. 關閉遊標 CLOSE語句用來關閉遊標並釋放結果集。遊標關閉之後，不能再執行FETCH操作。如果還需要使用FETCH語句，則要重新開啟遊標。文法如下： CLOSE [GLOBAL] cursor_name | cursor_variable_name v. 釋放遊標 遊標使用不再需要之後，要釋放遊標。DEALLOCATE語句釋放資料結構和遊標所加的鎖。文法如下： DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name  下面給出遊標的一個完整的例子： USE master GO CREATE PROCEDURE sp_BuildIndexes AS DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100) DECLARE table_cur CURSOR FOR SELECT name FROM sysobjects WHERE type=’u’ OPEN table_cur FETCH NEXT FROM table_cur INTO @TableName WHILE @@fetch_status = 0 BEGIN IF @@fetch_status = -2 CONTINUE SELECT @msg = “Building indexes for table”+@TableName+”…” PRINT @msg SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”’)” EXEC (@cmd) PRINT “ “ FETCH NEXT FROM table_cur INTO @TableName END DEALLOCATE table_cur GO 下面的指令碼將為PUBS資料庫執行sp_BuildIndexes USE pubs GO EXEC ap_BuildIndexes 注意：上面也是建立使用者定義的系統預存程序的樣本。 使用暫存資料表 臨 時表是在TempDB中建立的表。暫存資料表的名稱都以“#”開頭。暫存資料表的範圍為建立暫存資料表的串連。因為，暫存資料表不能在兩個串連之間共用，一旦串連關閉，臨 時表就會被丟棄。如果暫存資料表被建立於預存程序之中，則暫存資料表的範圍在預存程序之中，或者被該預存程序調用的任何預存程序之中。如果需要在串連之間共用臨時 表，則需要使用全域的暫存資料表。全域的暫存資料表以“##”符號開頭，它將一直存在於資料庫中，直到SQL Server重新啟動。一旦這類暫存資料表建立之後，所 有的使用者都可以訪問到。在暫存資料表上不能明確地指明許可權。 暫存資料表提供了儲存中間結果的能力。有時候，暫存資料表還能通過將一個複雜的查詢分解成兩個查詢而獲得 效能的改善。這可以通過首先將第一個查詢的結果存在暫存資料表中，然後在第二個查詢中使用暫存資料表來實現。當一個大表中的某個子集在一個在座過程中使用多次時， 建議使用暫存資料表。在這種情況下，在暫存資料表中保持資料的子集，以在隨後的串連中使用，這樣能大大改善效能。還可以在暫存資料表中建立索引。