i. 聲明游標(biāo)在這一步中，需要指定游標(biāo)的屬性和根據(jù)要求產(chǎn)生的結(jié)果集。有兩種方法可以指定一個(gè)游標(biāo)。
形式 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 ][OFcolumn_list]}]
INSENSITIVE關(guān)鍵字指明要為檢索到的結(jié)果集建立一個(gè)臨時(shí)拷貝，以后的數(shù)據(jù)從這個(gè)臨時(shí)拷貝中獲取。如果在后來(lái)游標(biāo)處理的過(guò)程中，原有基表中數(shù)據(jù)發(fā)生了改變，那么它們對(duì)于該游標(biāo)而言是不可見(jiàn)的。這種不敏感的游標(biāo)不允許數(shù)據(jù)更改。
SCROLL 關(guān)鍵字指明游標(biāo)可以在任意方向上滾動(dòng)。所有的 fetch 選項(xiàng)（ first 、 last 、 next 、 relative 、 absolute）都可以在游標(biāo)中使用。如果忽略該選項(xiàng)，則游標(biāo)只能向前滾動(dòng)（ next ）。
Select_statement 指明 SQL 語(yǔ)句建立的結(jié)果集。
TransactSQL 語(yǔ)句 COMPUTE 、 COMPUTE BY 、 FOR BROWSE 和 INTO 在游標(biāo)聲明的選擇語(yǔ)句中不允許使用。 READ ONLY指明在游標(biāo)結(jié)果集中不允許進(jìn)行數(shù)據(jù)修改。
UPDATE 關(guān)鍵字指明游標(biāo)的結(jié)果集可以修改。 OF column_list指明結(jié)果集中可以進(jìn)行修改的列。缺省情況下（使用 UPDATE 關(guān)鍵字），所有的列都可進(jìn)行修改。
LOCAL關(guān)鍵字指明游標(biāo)是局部的，它只能在它所聲明的過(guò)程中使用。 GLOBAL關(guān)鍵字使得游標(biāo)對(duì)于整個(gè)連接全局可見(jiàn)。全局的游標(biāo)在連接激活的任何時(shí)候都是可用的。只有當(dāng)連接結(jié)束時(shí)，游標(biāo)才不再可用。
FORWARD_ONLY指明游標(biāo)只能向前滾動(dòng)。
STATIC 的游標(biāo)與 INSENSITIVE 的游標(biāo)是相同的。 KEYSET 指明選取的行的順序。
SQL Server將從結(jié)果集中創(chuàng)建一個(gè)臨時(shí)關(guān)鍵字集。如果對(duì)數(shù)據(jù)庫(kù)的非關(guān)鍵字列進(jìn)行了修改，則它們對(duì)游標(biāo)是可見(jiàn)的。因?yàn)槭枪潭ǖ年P(guān)鍵字集合，所以對(duì)關(guān)鍵字列進(jìn)行修改或新插入列是不可見(jiàn)的。DYNAMIC 指明游標(biāo)將反映所有對(duì)結(jié)果集的修改。
SCROLL_LOCK 是為了保證游標(biāo)操作的成功，Examda提示:而對(duì)修改或刪除加鎖。
OPTIMISTIC指明哪些通過(guò)游標(biāo)進(jìn)行的修改或者刪除將不會(huì)成功。
注意： · 如果在 SELECT 語(yǔ)句中使用了 DISTINCT 、 UNION 、 GROUP BY語(yǔ)句，且在選擇中包含了聚合表鍤劍蠐偽曜遠(yuǎn)?INSENSITIVE 的游標(biāo)。
· 如果基表沒(méi)有的索引，則游標(biāo)創(chuàng)建成 INSENSITIVE 的游標(biāo)。
· 如果 SELECT 語(yǔ)句包含了 ORDER BY ，而被 ORDER BY 的列并非的行標(biāo)識(shí)，則 DYNAMIC 游標(biāo)將轉(zhuǎn)換成 KEYSET游標(biāo)。如果 KEYSET 游標(biāo)不能打開(kāi)，則將轉(zhuǎn)換成 INSENSITIVE 游標(biāo)。使用 SQL ANSI-92 語(yǔ)法定義的游標(biāo)同樣如此，只是沒(méi)有INSENSITIVE 關(guān)鍵字而已。
ii. 打開(kāi)游標(biāo)打開(kāi)游標(biāo)就是創(chuàng)建結(jié)果集。Examda提示:游標(biāo)通過(guò)DECLARE 語(yǔ)句定義，但其實(shí)際的執(zhí)行是通過(guò) OPEN 語(yǔ)句。
語(yǔ)法如下： OPEN { { [GLOBAL] cursor_name } |cursor_variable_name} GLOBAL 指明一個(gè)全局游標(biāo)。
Cursor_name 是被打開(kāi)的游標(biāo)的名稱。
Cursor_variable_name 是所引用游標(biāo)的變量名。該變量應(yīng)該為游標(biāo)類型。在游標(biāo)被打開(kāi)之后，系統(tǒng)變量 @@cursor_rows可以用來(lái)檢測(cè)結(jié)果集的行數(shù)。
@@cursor_rows 為負(fù)數(shù)時(shí)，表示游標(biāo)正在被異步遷移，其絕對(duì)值（如果 @@cursor_rows為－５，則絕對(duì)值為５）為當(dāng)前結(jié)果集的行數(shù)。異步游標(biāo)使用戶在游標(biāo)被完全遷移時(shí)仍然能夠訪問(wèn)游標(biāo)的結(jié)果。
iii. 從游標(biāo)中取值在從游標(biāo)中取值的過(guò)程中，可以在結(jié)果集中的每一行上來(lái)回移動(dòng)和處理。如果游標(biāo)定義成了可滾動(dòng)的（在聲明時(shí)使用SCROLL 關(guān)鍵字），則任何時(shí)候都可取出結(jié)果集中的任意行。對(duì)于非滾動(dòng)的游標(biāo)，只能對(duì)當(dāng)前行的下一行實(shí)施取操作。結(jié)果集可以取到局部變量中。
Fetch命令的語(yǔ)法如下： FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n| @nvar}] FROM [GLOBAL] cursor_name} | cursor_variable_name} [INTO@variable_name ][,……n]] NEXT 指明從當(dāng)前行的下一行取值。
PRIOR 指明從當(dāng)前行的前一行取值。
FIRST 是結(jié)果集的第一行。
LAST 是結(jié)果集的最后一行。
ABSOLUTE n 表示結(jié)果集中的第 n 行，該行數(shù)同樣可以通過(guò)一個(gè)局部變量傳播。行號(hào)從 0 開(kāi)始，所以 n 為 0時(shí)不能得到任何行。
RELATIVE n 表示要取出的行在當(dāng)前行的前 n 行或后 n 行的位置上。如果該值為正數(shù)，則要取出的行在當(dāng)前行前 n行的位置上，如果該值為負(fù)數(shù)，則返回當(dāng)前行的后 n 行。
INTO @cursor_variable_name表示游標(biāo)列值存儲(chǔ)的地方的變量列表。該列表中的變量數(shù)應(yīng)該與 DECLARE語(yǔ)句中選擇語(yǔ)句所使用的變量數(shù)相同。變量的數(shù)據(jù)類型也應(yīng)該與被選擇列的數(shù)據(jù)類型相同。直到下一次使用 FETCH 語(yǔ)句之前，變量中的值都會(huì)一直保持。每一次 FETCH的執(zhí)行都存儲(chǔ)在系統(tǒng)變量 @@fetch_status 中。
如果 FETCH 成功，則 @@fetch_status 被設(shè)置成 0 。@@fetch_status 為 -1 表示已經(jīng)到達(dá)了結(jié)果集的一部分（例如，在游標(biāo)被打開(kāi)之后，基表中的行被刪除）。
@@fetch_status可以用來(lái)構(gòu)造游標(biāo)處理的循環(huán)。
例如： DECLARE @iname char(20), @fname char(20) OPEN author_cur FETCHFIRST 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. 關(guān)閉游標(biāo) CLOSE 語(yǔ)句用來(lái)關(guān)閉游標(biāo)并釋放結(jié)果集。游標(biāo)關(guān)閉之后，不能再執(zhí)行 FETCH 操作。如果還需要使用 FETCH語(yǔ)句，則要重新打開(kāi)游標(biāo)。
語(yǔ)法如下： CLOSE [GLOBAL] cursor_name |cursor_variable_name
v. 釋放游標(biāo)游標(biāo)使用不再需要之后，要釋放游標(biāo)。 DEALLOCATE 語(yǔ)句釋放數(shù)據(jù)結(jié)構(gòu)和游標(biāo)所加的鎖。
語(yǔ)法如下： DEALLOCATE [GLOBAL]cursor_name | cursor_variable_name
下面給出游標(biāo)的一個(gè)完整的例子：
USEmaster
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
DECLAREtable_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 indexesfor table ” +@TableName+ ” … ”
PRINT @msg
SELECT@cmd = “ DBCC DBREINDEX ( ‘” +@TableName+ ”’ ) ”
EXEC(@cmd)
PRINT “ “
FETCH NEXT FROM table_curINTO @TableName
END
DEALLOCATE table_cur
GO
下面的腳本將為PUBS 數(shù)據(jù)庫(kù)執(zhí)行 sp_BuildIndexes
USE pubs
GO
EXECap_BuildIndexes
注意：上面也是創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過(guò)程的示例。
使用臨時(shí)表
臨時(shí)表是在 TempDB中創(chuàng)建的表。臨時(shí)表的名稱都以“ #”開(kāi)頭。臨時(shí)表的范圍為創(chuàng)建臨時(shí)表的連接。因?yàn)椋R時(shí)表不能在兩個(gè)連接之間共享，一旦連接關(guān)閉，臨時(shí)表就會(huì)被丟棄。如果臨時(shí)表被創(chuàng)建于存儲(chǔ)過(guò)程之中，則臨時(shí)表的范圍在存儲(chǔ)過(guò)程之中，或者被該存儲(chǔ)過(guò)程調(diào)用的任何存儲(chǔ)過(guò)程之中。如果需要在連接之間共享臨時(shí)表，則需要使用全局的臨時(shí)表。全局的臨時(shí)表以“## ”符號(hào)開(kāi)頭，它將一直存在于數(shù)據(jù)庫(kù)中，直到 SQL Server重新啟動(dòng)。一旦這類臨時(shí)表創(chuàng)建之后，考試大提示:所有的用戶都可以訪問(wèn)到。在臨時(shí)表上不能明確地指明權(quán)限。
臨時(shí)表提供了存儲(chǔ)中間結(jié)果的能力。有時(shí)候，臨時(shí)表還能通過(guò)將一個(gè)復(fù)雜的查詢分解成兩個(gè)查詢而獲得性能的改善。這可以通過(guò)首先將第一個(gè)查詢的結(jié)果存在臨時(shí)表中，然后在第二個(gè)查詢中使用臨時(shí)表來(lái)實(shí)現(xiàn)。當(dāng)一個(gè)大表中的某個(gè)子集在一個(gè)在座過(guò)程中使用多次時(shí)，建議使用臨時(shí)表。在這種情況下，在臨時(shí)表中保持?jǐn)?shù)據(jù)的子集，以在隨后的連接中使用，這樣能大大改善性能。還可以在臨時(shí)表中創(chuàng)建索引。
形式 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 ][OFcolumn_list]}]
INSENSITIVE關(guān)鍵字指明要為檢索到的結(jié)果集建立一個(gè)臨時(shí)拷貝，以后的數(shù)據(jù)從這個(gè)臨時(shí)拷貝中獲取。如果在后來(lái)游標(biāo)處理的過(guò)程中，原有基表中數(shù)據(jù)發(fā)生了改變，那么它們對(duì)于該游標(biāo)而言是不可見(jiàn)的。這種不敏感的游標(biāo)不允許數(shù)據(jù)更改。
SCROLL 關(guān)鍵字指明游標(biāo)可以在任意方向上滾動(dòng)。所有的 fetch 選項(xiàng)（ first 、 last 、 next 、 relative 、 absolute）都可以在游標(biāo)中使用。如果忽略該選項(xiàng)，則游標(biāo)只能向前滾動(dòng)（ next ）。
Select_statement 指明 SQL 語(yǔ)句建立的結(jié)果集。
TransactSQL 語(yǔ)句 COMPUTE 、 COMPUTE BY 、 FOR BROWSE 和 INTO 在游標(biāo)聲明的選擇語(yǔ)句中不允許使用。 READ ONLY指明在游標(biāo)結(jié)果集中不允許進(jìn)行數(shù)據(jù)修改。
UPDATE 關(guān)鍵字指明游標(biāo)的結(jié)果集可以修改。 OF column_list指明結(jié)果集中可以進(jìn)行修改的列。缺省情況下（使用 UPDATE 關(guān)鍵字），所有的列都可進(jìn)行修改。
LOCAL關(guān)鍵字指明游標(biāo)是局部的，它只能在它所聲明的過(guò)程中使用。 GLOBAL關(guān)鍵字使得游標(biāo)對(duì)于整個(gè)連接全局可見(jiàn)。全局的游標(biāo)在連接激活的任何時(shí)候都是可用的。只有當(dāng)連接結(jié)束時(shí)，游標(biāo)才不再可用。
FORWARD_ONLY指明游標(biāo)只能向前滾動(dòng)。
STATIC 的游標(biāo)與 INSENSITIVE 的游標(biāo)是相同的。 KEYSET 指明選取的行的順序。
SQL Server將從結(jié)果集中創(chuàng)建一個(gè)臨時(shí)關(guān)鍵字集。如果對(duì)數(shù)據(jù)庫(kù)的非關(guān)鍵字列進(jìn)行了修改，則它們對(duì)游標(biāo)是可見(jiàn)的。因?yàn)槭枪潭ǖ年P(guān)鍵字集合，所以對(duì)關(guān)鍵字列進(jìn)行修改或新插入列是不可見(jiàn)的。DYNAMIC 指明游標(biāo)將反映所有對(duì)結(jié)果集的修改。
SCROLL_LOCK 是為了保證游標(biāo)操作的成功，Examda提示:而對(duì)修改或刪除加鎖。
OPTIMISTIC指明哪些通過(guò)游標(biāo)進(jìn)行的修改或者刪除將不會(huì)成功。
注意： · 如果在 SELECT 語(yǔ)句中使用了 DISTINCT 、 UNION 、 GROUP BY語(yǔ)句，且在選擇中包含了聚合表鍤劍蠐偽曜遠(yuǎn)?INSENSITIVE 的游標(biāo)。
· 如果基表沒(méi)有的索引，則游標(biāo)創(chuàng)建成 INSENSITIVE 的游標(biāo)。
· 如果 SELECT 語(yǔ)句包含了 ORDER BY ，而被 ORDER BY 的列并非的行標(biāo)識(shí)，則 DYNAMIC 游標(biāo)將轉(zhuǎn)換成 KEYSET游標(biāo)。如果 KEYSET 游標(biāo)不能打開(kāi)，則將轉(zhuǎn)換成 INSENSITIVE 游標(biāo)。使用 SQL ANSI-92 語(yǔ)法定義的游標(biāo)同樣如此，只是沒(méi)有INSENSITIVE 關(guān)鍵字而已。
ii. 打開(kāi)游標(biāo)打開(kāi)游標(biāo)就是創(chuàng)建結(jié)果集。Examda提示:游標(biāo)通過(guò)DECLARE 語(yǔ)句定義，但其實(shí)際的執(zhí)行是通過(guò) OPEN 語(yǔ)句。
語(yǔ)法如下： OPEN { { [GLOBAL] cursor_name } |cursor_variable_name} GLOBAL 指明一個(gè)全局游標(biāo)。
Cursor_name 是被打開(kāi)的游標(biāo)的名稱。
Cursor_variable_name 是所引用游標(biāo)的變量名。該變量應(yīng)該為游標(biāo)類型。在游標(biāo)被打開(kāi)之后，系統(tǒng)變量 @@cursor_rows可以用來(lái)檢測(cè)結(jié)果集的行數(shù)。
@@cursor_rows 為負(fù)數(shù)時(shí)，表示游標(biāo)正在被異步遷移，其絕對(duì)值（如果 @@cursor_rows為－５，則絕對(duì)值為５）為當(dāng)前結(jié)果集的行數(shù)。異步游標(biāo)使用戶在游標(biāo)被完全遷移時(shí)仍然能夠訪問(wèn)游標(biāo)的結(jié)果。
iii. 從游標(biāo)中取值在從游標(biāo)中取值的過(guò)程中，可以在結(jié)果集中的每一行上來(lái)回移動(dòng)和處理。如果游標(biāo)定義成了可滾動(dòng)的（在聲明時(shí)使用SCROLL 關(guān)鍵字），則任何時(shí)候都可取出結(jié)果集中的任意行。對(duì)于非滾動(dòng)的游標(biāo)，只能對(duì)當(dāng)前行的下一行實(shí)施取操作。結(jié)果集可以取到局部變量中。
Fetch命令的語(yǔ)法如下： FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE {n | @nvar} | RELATIVE {n| @nvar}] FROM [GLOBAL] cursor_name} | cursor_variable_name} [INTO@variable_name ][,……n]] NEXT 指明從當(dāng)前行的下一行取值。
PRIOR 指明從當(dāng)前行的前一行取值。
FIRST 是結(jié)果集的第一行。
LAST 是結(jié)果集的最后一行。
ABSOLUTE n 表示結(jié)果集中的第 n 行，該行數(shù)同樣可以通過(guò)一個(gè)局部變量傳播。行號(hào)從 0 開(kāi)始，所以 n 為 0時(shí)不能得到任何行。
RELATIVE n 表示要取出的行在當(dāng)前行的前 n 行或后 n 行的位置上。如果該值為正數(shù)，則要取出的行在當(dāng)前行前 n行的位置上，如果該值為負(fù)數(shù)，則返回當(dāng)前行的后 n 行。
INTO @cursor_variable_name表示游標(biāo)列值存儲(chǔ)的地方的變量列表。該列表中的變量數(shù)應(yīng)該與 DECLARE語(yǔ)句中選擇語(yǔ)句所使用的變量數(shù)相同。變量的數(shù)據(jù)類型也應(yīng)該與被選擇列的數(shù)據(jù)類型相同。直到下一次使用 FETCH 語(yǔ)句之前，變量中的值都會(huì)一直保持。每一次 FETCH的執(zhí)行都存儲(chǔ)在系統(tǒng)變量 @@fetch_status 中。
如果 FETCH 成功，則 @@fetch_status 被設(shè)置成 0 。@@fetch_status 為 -1 表示已經(jīng)到達(dá)了結(jié)果集的一部分（例如，在游標(biāo)被打開(kāi)之后，基表中的行被刪除）。
@@fetch_status可以用來(lái)構(gòu)造游標(biāo)處理的循環(huán)。
例如： DECLARE @iname char(20), @fname char(20) OPEN author_cur FETCHFIRST 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. 關(guān)閉游標(biāo) CLOSE 語(yǔ)句用來(lái)關(guān)閉游標(biāo)并釋放結(jié)果集。游標(biāo)關(guān)閉之后，不能再執(zhí)行 FETCH 操作。如果還需要使用 FETCH語(yǔ)句，則要重新打開(kāi)游標(biāo)。
語(yǔ)法如下： CLOSE [GLOBAL] cursor_name |cursor_variable_name
v. 釋放游標(biāo)游標(biāo)使用不再需要之后，要釋放游標(biāo)。 DEALLOCATE 語(yǔ)句釋放數(shù)據(jù)結(jié)構(gòu)和游標(biāo)所加的鎖。
語(yǔ)法如下： DEALLOCATE [GLOBAL]cursor_name | cursor_variable_name
下面給出游標(biāo)的一個(gè)完整的例子：
USEmaster
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
DECLAREtable_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 indexesfor table ” +@TableName+ ” … ”
PRINT @msg
SELECT@cmd = “ DBCC DBREINDEX ( ‘” +@TableName+ ”’ ) ”
EXEC(@cmd)
PRINT “ “
FETCH NEXT FROM table_curINTO @TableName
END
DEALLOCATE table_cur
GO
下面的腳本將為PUBS 數(shù)據(jù)庫(kù)執(zhí)行 sp_BuildIndexes
USE pubs
GO
EXECap_BuildIndexes
注意：上面也是創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過(guò)程的示例。
使用臨時(shí)表
臨時(shí)表是在 TempDB中創(chuàng)建的表。臨時(shí)表的名稱都以“ #”開(kāi)頭。臨時(shí)表的范圍為創(chuàng)建臨時(shí)表的連接。因?yàn)椋R時(shí)表不能在兩個(gè)連接之間共享，一旦連接關(guān)閉，臨時(shí)表就會(huì)被丟棄。如果臨時(shí)表被創(chuàng)建于存儲(chǔ)過(guò)程之中，則臨時(shí)表的范圍在存儲(chǔ)過(guò)程之中，或者被該存儲(chǔ)過(guò)程調(diào)用的任何存儲(chǔ)過(guò)程之中。如果需要在連接之間共享臨時(shí)表，則需要使用全局的臨時(shí)表。全局的臨時(shí)表以“## ”符號(hào)開(kāi)頭，它將一直存在于數(shù)據(jù)庫(kù)中，直到 SQL Server重新啟動(dòng)。一旦這類臨時(shí)表創(chuàng)建之后，考試大提示:所有的用戶都可以訪問(wèn)到。在臨時(shí)表上不能明確地指明權(quán)限。
臨時(shí)表提供了存儲(chǔ)中間結(jié)果的能力。有時(shí)候，臨時(shí)表還能通過(guò)將一個(gè)復(fù)雜的查詢分解成兩個(gè)查詢而獲得性能的改善。這可以通過(guò)首先將第一個(gè)查詢的結(jié)果存在臨時(shí)表中，然后在第二個(gè)查詢中使用臨時(shí)表來(lái)實(shí)現(xiàn)。當(dāng)一個(gè)大表中的某個(gè)子集在一個(gè)在座過(guò)程中使用多次時(shí)，建議使用臨時(shí)表。在這種情況下，在臨時(shí)表中保持?jǐn)?shù)據(jù)的子集，以在隨后的連接中使用，這樣能大大改善性能。還可以在臨時(shí)表中創(chuàng)建索引。