sql_server游標詳解

1、SQLSERVER游標詳解游標詳解游標和游標的優(yōu)點游標和游標的優(yōu)點在數(shù)據(jù)庫中，游標是一個十分重要的概念。游標提供了一種對從表中檢索出的數(shù)據(jù)進行操作的靈活手在數(shù)據(jù)庫中，游標是一個十分重要的概念。游標提供了一種對從表中檢索出的數(shù)據(jù)進行操作的靈活手段，就本質(zhì)而言，游標實際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機制。游標段，就本質(zhì)而言，游標實際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機制。游標總是與一條總是與一

2、條T_SQL選擇語句相關(guān)聯(lián)因為游標由結(jié)果集（可以是零條、一條或由相關(guān)的選擇語句檢索出選擇語句相關(guān)聯(lián)因為游標由結(jié)果集（可以是零條、一條或由相關(guān)的選擇語句檢索出的多條記錄）和結(jié)果集中指向特定記錄的游標位置組成。當(dāng)決定對結(jié)果集進行處理時，必須聲明一個指的多條記錄）和結(jié)果集中指向特定記錄的游標位置組成。當(dāng)決定對結(jié)果集進行處理時，必須聲明一個指向該結(jié)果集的游標。如果曾經(jīng)用向該結(jié)果集的游標。如果曾經(jīng)用C語言寫過對文件進行處理的程序，那么游標就像您打

3、開文件所得到的語言寫過對文件進行處理的程序，那么游標就像您打開文件所得到的文件句柄一樣，只要文件打開成功，該文件句柄就可代表該文件。對于游標而言，其道理是相同的?？晌募浔粯?，只要文件打開成功，該文件句柄就可代表該文件。對于游標而言，其道理是相同的?？梢娪螛四軌?qū)崿F(xiàn)按與傳統(tǒng)程序讀取平面文件類似的方式處理來自基礎(chǔ)表的結(jié)果集，從而把表中數(shù)據(jù)以平見游標能夠?qū)崿F(xiàn)按與傳統(tǒng)程序讀取平面文件類似的方式處理來自基礎(chǔ)表的結(jié)果集，從而把表中數(shù)據(jù)以平面文件

4、的形式呈現(xiàn)給程序。面文件的形式呈現(xiàn)給程序。我們知道關(guān)系數(shù)據(jù)庫管理系統(tǒng)實質(zhì)是面向集合的，在我們知道關(guān)系數(shù)據(jù)庫管理系統(tǒng)實質(zhì)是面向集合的，在MSSQLSERVER中并沒有一種描述表中單一中并沒有一種描述表中單一記錄的表達形式，除非使用記錄的表達形式，除非使用where子句來限制只有一條記錄被選中。因此我們必須借助于游標來進行面子句來限制只有一條記錄被選中。因此我們必須借助于游標來進行面向單條記錄的數(shù)據(jù)處理。向單條記錄的數(shù)據(jù)處理。由此可見，游標

5、允許應(yīng)用程序?qū)Σ樵冋Z句由此可見，游標允許應(yīng)用程序?qū)Σ樵冋Z句返回的行結(jié)果集中每一行進行相同或不同的操作，而返回的行結(jié)果集中每一行進行相同或不同的操作，而不是一次對整個結(jié)果集進行同一種操作；它還提供對基于游標位置而對表中數(shù)據(jù)進行刪除或更新的能力；不是一次對整個結(jié)果集進行同一種操作；它還提供對基于游標位置而對表中數(shù)據(jù)進行刪除或更新的能力；而且，正是游標把作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的程序設(shè)計兩者聯(lián)系起來，使兩個數(shù)據(jù)處理而且，正是游標把

6、作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的程序設(shè)計兩者聯(lián)系起來，使兩個數(shù)據(jù)處理方式能夠進行溝通。方式能夠進行溝通。游標種類游標種類MSSQLSERVER支持三種類型的游標：支持三種類型的游標：Transact_SQL游標，游標，API服務(wù)器游標和客戶游標。服務(wù)器游標和客戶游標。（1）Transact_SQL游標游標Transact_SQL游標是由游標是由DECLARECURS語法定義、主要用在語法定義、主要用在Transact_SQL腳本、

7、存儲過程腳本、存儲過程和觸發(fā)器中。和觸發(fā)器中。Transact_SQL游標主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的游標主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的Transact_SQL語句語句或是批處理、存儲過程、觸發(fā)器中的或是批處理、存儲過程、觸發(fā)器中的Transact_SQL進行管理。進行管理。Transact_SQL游標不支持提取數(shù)據(jù)塊游標不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)?；蚨嘈袛?shù)據(jù)。（2）API游標游標API游標支持在游標支持在OL

8、EDB，ODBC以及以及DB_library中使用游標函數(shù)，主要用在服務(wù)器上。每一次客中使用游標函數(shù)，主要用在服務(wù)器上。每一次客戶端應(yīng)用程序調(diào)用戶端應(yīng)用程序調(diào)用API游標函數(shù)，游標函數(shù)，MSSQLSEVER的OLEDB提供者、提供者、ODBC驅(qū)動器或驅(qū)動器或DB_library的動態(tài)鏈接庫（的動態(tài)鏈接庫（DLL）都會將這些客戶請求傳送給服務(wù)器以對都會將這些客戶請求傳送給服務(wù)器以對API游標進行處理。游標進行處理。（3）客戶游標客戶游標客

9、戶游標主要是當(dāng)在客戶機上緩存結(jié)果集時才使用。在客戶游標中，有一個缺省的結(jié)果集被用來在客客戶游標主要是當(dāng)在客戶機上緩存結(jié)果集時才使用。在客戶游標中，有一個缺省的結(jié)果集被用來在客戶機上緩存整個結(jié)果集?？蛻粲螛藘H支持靜態(tài)游標而非動態(tài)游標。由于服務(wù)器游標并不支持所有的戶機上緩存整個結(jié)果集?？蛻粲螛藘H支持靜態(tài)游標而非動態(tài)游標。由于服務(wù)器游標并不支持所有的TransactSQL語句或批處理，所以客戶游標常常僅被用作服務(wù)器游標的輔助。因為在一般情況下

10、，服務(wù)語句或批處理，所以客戶游標常常僅被用作服務(wù)器游標的輔助。因為在一般情況下，服務(wù)器游標能支持絕大多數(shù)的游標操作。器游標能支持絕大多數(shù)的游標操作。由于由于API游標和游標和TransactSQL游標使用在服務(wù)器端，所以被稱為服務(wù)器游標，也被稱為后臺游標，游標使用在服務(wù)器端，所以被稱為服務(wù)器游標，也被稱為后臺游標，而客戶端游標被稱為前臺游標。在本章中我們主要講述服務(wù)器（后臺）游標。而客戶端游標被稱為前臺游標。在本章中我們主要講述服務(wù)器（

11、后臺）游標。每一個游標必須有四個組成部分這四個關(guān)鍵部分必須符合下面的順序；每一個游標必須有四個組成部分這四個關(guān)鍵部分必須符合下面的順序；1.DECLARE游標游標2.OPEN游標游標3.從一個游標中從一個游標中FETCH信息信息4.CLOSE或DEALLOCATE游標游標通常我們使用通常我們使用DECLARE來聲明一個游標聲明一個游標主要包括以下主要內(nèi)容：來聲明一個游標聲明一個游標主要包括以下主要內(nèi)容：游標名字游標名字數(shù)據(jù)來源（表和列）

12、數(shù)據(jù)來源（表和列）庫選項，為了與以前的版本兼容，該選項常設(shè)置為庫選項，為了與以前的版本兼容，該選項常設(shè)置為FALSE。FWARD_ONLY選項指明在從游標中提取數(shù)據(jù)記錄時，只能按照從第一行到最后一行的順序，此時只能選用選項指明在從游標中提取數(shù)據(jù)記錄時，只能按照從第一行到最后一行的順序，此時只能選用FETCHNEXT操作。除非使用操作。除非使用STATIC，KEYSET和DYNAMIC關(guān)鍵字，否則如果未指明是使用關(guān)鍵字，否則如果未指明是使

13、用FWARD_ONLY還是使用還是使用SCROLL，那么那么FWARD_ONLY將成為缺省選項，因為若使用將成為缺省選項，因為若使用STATICKEYSET和DYNAMIC關(guān)鍵字，則變成了關(guān)鍵字，則變成了SCROLL游標。另外如果使用了游標。另外如果使用了FWARD_ONLY，便不能使用便不能使用FAST_FWARD。STATIC選項的含義與選項的含義與INSENSITIVE選項一樣，選項一樣，MSSQLSERVER會將游標定義所選取出

14、來的數(shù)據(jù)記錄存放在會將游標定義所選取出來的數(shù)據(jù)記錄存放在一臨時表內(nèi)（建立在一臨時表內(nèi)（建立在tempdb數(shù)據(jù)庫下）。對該游標的讀取操作皆由臨時表來應(yīng)答。因此對基本表的修數(shù)據(jù)庫下）。對該游標的讀取操作皆由臨時表來應(yīng)答。因此對基本表的修改并不影響游標中的數(shù)據(jù)，即游標不會隨著基本表內(nèi)容的改變而改變，同時也無法通過游標來更新基本改并不影響游標中的數(shù)據(jù)，即游標不會隨著基本表內(nèi)容的改變而改變，同時也無法通過游標來更新基本表。表。KEYSET指出當(dāng)游

15、標被打開時，游標中列的順序是固定的，并且指出當(dāng)游標被打開時，游標中列的順序是固定的，并且MSSQLSERVER會在會在tempdb內(nèi)建立一個表，內(nèi)建立一個表，該表即為該表即為KEYSET的鍵值可惟一識別游標中的某行數(shù)據(jù)。當(dāng)游標擁有者或其它用戶對基本表中的非鍵值的鍵值可惟一識別游標中的某行數(shù)據(jù)。當(dāng)游標擁有者或其它用戶對基本表中的非鍵值數(shù)據(jù)進行修改時，這種變化能夠反映到游標中，所以游標用戶或所有者可以通過滾動游標這顯示這些數(shù)數(shù)據(jù)進行修改時，

16、這種變化能夠反映到游標中，所以游標用戶或所有者可以通過滾動游標這顯示這些數(shù)據(jù)。據(jù)。當(dāng)其它用戶增加一條新的符合所定義的游標范圍的數(shù)據(jù)時，無法由此游標讀到該數(shù)據(jù)。因為當(dāng)其它用戶增加一條新的符合所定義的游標范圍的數(shù)據(jù)時，無法由此游標讀到該數(shù)據(jù)。因為TransactSQL服務(wù)器游標不支持服務(wù)器游標不支持語句。語句。如果在游標中的某一行被刪除掉，那么當(dāng)通過游標來提取該刪除行時，如果在游標中的某一行被刪除掉，那么當(dāng)通過游標來提取該刪除行時，@@FE

17、TCH_STATUS的返回的返回值為值為2。@@FETCH_STATUS是用來判斷讀取游標是否成功的系統(tǒng)全局變量。是用來判斷讀取游標是否成功的系統(tǒng)全局變量。由于更新操作包括兩部分：刪除原數(shù)據(jù)插入新數(shù)據(jù)，所以如果讀取原數(shù)據(jù)，由于更新操作包括兩部分：刪除原數(shù)據(jù)插入新數(shù)據(jù)，所以如果讀取原數(shù)據(jù)，@@FETCH_STATUS的返回值為返回值為2；而且無法通過游標來讀取新插入的數(shù)據(jù)。但是如果使用了而且無法通過游標來讀取新插入的數(shù)據(jù)。但是如果使用了W

18、HERECURRENTOF子句時，子句時，該新插入行數(shù)據(jù)便是可見的。該新插入行數(shù)據(jù)便是可見的。注意：如果基礎(chǔ)表未包含惟一的索引或主鍵，則一個注意：如果基礎(chǔ)表未包含惟一的索引或主鍵，則一個KEYSET游標將回復(fù)成游標將回復(fù)成STATIC游標。游標。DYNAMIC指明基礎(chǔ)表的變化將反映到游標中，使用這個選項會最大程度上保證數(shù)據(jù)的一致性。然而，與指明基礎(chǔ)表的變化將反映到游標中，使用這個選項會最大程度上保證數(shù)據(jù)的一致性。然而，與KEYSET和S

19、TATIC類型游標相比較，此類型游標需要大量的游標資源。類型游標相比較，此類型游標需要大量的游標資源。FAST_FWARD指明一個指明一個FWARD_ONLYREAD_ONLY型游標。此選項已為執(zhí)行進行了優(yōu)化。如果型游標。此選項已為執(zhí)行進行了優(yōu)化。如果SCROLL或F_UPDATE選項被定義，則選項被定義，則FAST_FWARD選項不能被定義。選項不能被定義。SCROLL_LOCKS指明鎖被放置在游標結(jié)果集所使用的數(shù)據(jù)上。數(shù)據(jù)被讀入游標

20、中時，就會出現(xiàn)鎖。這個選項確保對一個指明鎖被放置在游標結(jié)果集所使用的數(shù)據(jù)上。數(shù)據(jù)被讀入游標中時，就會出現(xiàn)鎖。這個選項確保對一個游標進行的更新和刪除操作總能被成功執(zhí)行。如果游標進行的更新和刪除操作總能被成功執(zhí)行。如果FAST_FWARD選項被定義，則不能選擇該選項。選項被定義，則不能選擇該選項。另外，由于數(shù)據(jù)被游標鎖定，所以當(dāng)考慮數(shù)據(jù)并發(fā)處理時，應(yīng)避免使用該選項。另外，由于數(shù)據(jù)被游標鎖定，所以當(dāng)考慮數(shù)據(jù)并發(fā)處理時，應(yīng)避免使用該選項。OPT

21、IMISTIC指明在數(shù)據(jù)被讀入游標后，如果游標中某行數(shù)據(jù)已發(fā)生變化，那么對游標數(shù)據(jù)進行更新或刪除可能會導(dǎo)指明在數(shù)據(jù)被讀入游標后，如果游標中某行數(shù)據(jù)已發(fā)生變化，那么對游標數(shù)據(jù)進行更新或刪除可能會導(dǎo)致失敗。如果使用了致失敗。如果使用了FAST_FWARD選項，則不能使用該選項。選項，則不能使用該選項。TYPE_WARNING指明若游標類型被修改成與用戶定義的類型不同時，將發(fā)送一個警告信息給客戶端。指明若游標類型被修改成與用戶定義的類型不同時

22、，將發(fā)送一個警告信息給客戶端。注意：不可以將注意：不可以將SQL_92的游標語法規(guī)則與的游標語法規(guī)則與MSSQLSERVER的游標擴展用法混合在一起使用。的游標擴展用法混合在一起使用。下面我們將總結(jié)一下聲明游標時應(yīng)注意的一些問題。下面我們將總結(jié)一下聲明游標時應(yīng)注意的一些問題。如果在如果在CURS前使用了前使用了SCROLL或INSENSITIVE保留字，則不能在保留字，則不能在CURS和F_statement之間使用任何的保留字。反之同