張潤++方繼才

摘要：針對(duì)一些數(shù)據(jù)操作直接使用SQL語句無法處理的問題，在深入分析此類問題遍歷操作的基礎(chǔ)上，文中結(jié)合SQL中的游標(biāo)相關(guān)知識(shí)和使用規(guī)則，給出了一種基于SQL游標(biāo)的數(shù)據(jù)處理方法，最后在SQL Server 2012環(huán)境的基礎(chǔ)上，對(duì)研究所述進(jìn)行了相關(guān)驗(yàn)證，驗(yàn)證結(jié)果表明此類方法的有效性。

關(guān)鍵詞： SQL Server； 游標(biāo)； 結(jié)果集； 遍歷

中圖分類號(hào)： TP391

文獻(xiàn)標(biāo)志碼： A

文章編號(hào)：2095-2163（2016）06-0084-04

0引言

SQL Server 2012是一款功能完善、且設(shè)計(jì)高端的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)操作通常都是面向整個(gè)結(jié)果集的，而結(jié)果集又是由Select、Update、Insert等語句返回的所有滿足Where子句條件的行。在數(shù)據(jù)庫的開發(fā)過程中，常常使用T-SQL語句將整個(gè)結(jié)果集作為一個(gè)單元來進(jìn)行處理，然而實(shí)際上很多時(shí)候用戶需要對(duì)結(jié)果集中的某一行或者多行分設(shè)相同或不同的操作，例如要從結(jié)果集中逐一讀取每一條記錄、更新或者刪除結(jié)果集對(duì)應(yīng)表中某一行的數(shù)據(jù)等等，此時(shí)若僅是運(yùn)用T-SQL語句則將無法滿足這一功能需求，為此就需要借助于游標(biāo)機(jī)制來實(shí)現(xiàn)逐條記錄的數(shù)據(jù)處理＼[1＼]。而且，還有很多應(yīng)用程序，尤其是將SQL 嵌入到其它開發(fā)語言時(shí)，這些語言或程序并不能把整個(gè)結(jié)果集作為一個(gè)單元來開展有效的控制處理，而只能處理一行或者部分行，這時(shí)候就更加需要使用游標(biāo)。

[BT4]1SQL游標(biāo)的概述

游標(biāo)作為數(shù)據(jù)庫的重要對(duì)象，不僅表現(xiàn)為一種數(shù)據(jù)訪問機(jī)制，同時(shí)也是一種數(shù)據(jù)處理方法，具有對(duì)結(jié)果集進(jìn)行逐行處理的能力；而且，游標(biāo)還提供了一種對(duì)從表中檢索出的數(shù)據(jù)定制各類操作的靈活手段。就本質(zhì)而言，游標(biāo)實(shí)際上即是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制＼[2＼]。游標(biāo)就好比C語言中的指針，通過與某個(gè)查詢結(jié)果構(gòu)建技術(shù)聯(lián)系，可以指定結(jié)果集中的任何位置，然后允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理，以達(dá)到用戶處理數(shù)據(jù)的復(fù)雜目的需求，如：在結(jié)果集中一次一行或者多行向前或向后瀏覽數(shù)據(jù)的操作。

在游標(biāo)的實(shí)現(xiàn)上，則總是與一條T-SQL語句相關(guān)聯(lián)。這是因?yàn)橛螛?biāo)是由結(jié)果集和結(jié)果集中指向特定記錄的游標(biāo)位置集結(jié)聯(lián)合所組成，組成示意如圖1所示。其中，游標(biāo)結(jié)果集是定義游標(biāo)的SQL語句返回的行的集合，游標(biāo)位置則是指向這個(gè)行集合中某一行的當(dāng)前指針。在SQL Server中，用戶可以通過SQL語句逐一地從游標(biāo)中讀取記錄，還可以使用游標(biāo)處理結(jié)果集中的數(shù)據(jù)，具體包括了更新、刪除等操作。

2SQL游標(biāo)的作用及優(yōu)勢(shì)

在SQL Server中，游標(biāo)主要是用來對(duì)結(jié)果集引入“行集”性質(zhì)的相關(guān)操作。也就是說，當(dāng)在存儲(chǔ)過程、觸發(fā)器和批處理中使用T-SQL語句返回結(jié)果集的多條記錄時(shí)，游標(biāo)則給出了一個(gè)可以對(duì)記錄進(jìn)行逐條處理的有效方法。

研究可得，使用游標(biāo)的重點(diǎn)效應(yīng)優(yōu)勢(shì)可作如下闡析：

1）游標(biāo)允許用戶訪問或處理一個(gè)集合里的單獨(dú)數(shù)據(jù)行，而不受其他行的限制約束，這也是游標(biāo)設(shè)計(jì)上的鮮明優(yōu)勢(shì)?；诖?，用戶就可以使用這些數(shù)據(jù)生成SQL代碼并立即執(zhí)行或輸出，這樣就能降低系統(tǒng)開銷和潛在的阻隔情況。

2）使用游標(biāo)查看或處理數(shù)據(jù)使得用戶的操作過程更加靈活、高效、方便。

3）在SQL Server數(shù)據(jù)庫系統(tǒng)中，游標(biāo)可分為服務(wù)器游標(biāo)和客戶游標(biāo)。相對(duì)于客戶游標(biāo)而言，服務(wù)器游標(biāo)可以大大減少網(wǎng)絡(luò)數(shù)據(jù)傳輸?shù)拈_銷，從而提高應(yīng)用程序訪問數(shù)據(jù)庫的速度和效率。

4）作為面向集合的數(shù)據(jù)庫管理系統(tǒng)（RDBMS）和面向行的程序設(shè)計(jì)之間的設(shè)計(jì)媒介，游標(biāo)可使得這2種處理方式能夠獲得有效拓展連接＼[3＼]。

[BT4]3SQL游標(biāo)的使用

SQL游標(biāo)一般用于存儲(chǔ)過程、觸發(fā)器和Transact-SQL腳本中。在SQL Server中使用游標(biāo)處理數(shù)據(jù)時(shí)需要遵循一定的規(guī)則，研究給出其使用流程如圖2所示。

從圖 2 中可以看出，SQL游標(biāo)的使用流程可具體表述如下：首先聲明游標(biāo)，然后打開游標(biāo)，再從游標(biāo)中提取數(shù)據(jù)應(yīng)用于相關(guān)操作，直至所有記錄均已標(biāo)明處理，由此即關(guān)閉并釋放游標(biāo)。

[BT5]3.1游標(biāo)的聲明

與變量一樣，在游標(biāo)使用前也要預(yù)先設(shè)定游標(biāo)聲明。通過聲明來定義游標(biāo)的名稱、游標(biāo)指向的結(jié)果集和游標(biāo)的屬性。簡單的示例語句為：

Declare cur_xsxx Cursor For Select xh，xm，xb，sfz From xsxx；

該語句定義了一個(gè)名為cur_xsxx 的游標(biāo)，且指向一個(gè)從xsxx（學(xué)生信息表）表中查詢出xh、xm、xb和sfz字段內(nèi)容的結(jié)果集。

在上述示例語句中，游標(biāo)名稱Cursor_name（游標(biāo)變量@Cursor_name）是用戶此后若涉及到該游標(biāo)時(shí)將會(huì)使用的名字，關(guān)鍵字Cursor指明此變量是游標(biāo)類型，關(guān)鍵字For和Select語句定義了游標(biāo)的內(nèi)容。此外，聲明游標(biāo)的T-SQL語句還提供了一些更多的關(guān)鍵字選項(xiàng)，如Scroll、Static、Read_Only等等，各個(gè)選項(xiàng)也將呈現(xiàn)各不相同的作用。

[BT5]3.2游標(biāo)的打開

游標(biāo)在發(fā)出聲明后，必須使用Open語句來打開游標(biāo)，才能展開進(jìn)一步的數(shù)據(jù)提取。打開游標(biāo)的示例語句為：

Open cur_xsxx。

打開游標(biāo)時(shí)，需要注意兩點(diǎn)：

1）若打開的是全局游標(biāo)，則必須添加關(guān)鍵字Global，否則打開的就將是默認(rèn)的局部游標(biāo)。

2）游標(biāo)的打開可能會(huì)產(chǎn)生問題，SQL Server即是通過@@Error全局變量的值來判斷游標(biāo)打開的成功與否：為0表示成功，為1表示失敗。

[BT5]3.3游標(biāo)的提取

打開游標(biāo)后，就可以讀取游標(biāo)中的數(shù)據(jù)了。提取游標(biāo)的示例語句為：

Fetch Next From cur_xsxx into @xh，@xm。

該語句表示將游標(biāo)結(jié)果集所指的記錄中字段xh和xm的值賦給局部變量@xh和@xm中。在讀取游標(biāo)時(shí)，數(shù)據(jù)所在的記錄位置總共設(shè)有6個(gè)關(guān)鍵字（Next、Prior、First、Last、Absolute和Relative）用于條件選擇。

[BT5]3.4游標(biāo)的關(guān)閉和釋放

在游標(biāo)使用進(jìn)程結(jié)束后，要及時(shí)關(guān)閉和釋放游標(biāo)，以利于服務(wù)器釋放曾分配給游標(biāo)占用的SQL Server系統(tǒng)資源。關(guān)閉游標(biāo)的示例語句為：

Close cur_xsxx；

釋放游標(biāo)的示例語句為：

Deallocate cur_xsxx。

游標(biāo)被釋放后就不能重新打開了，除非再次重新聲明游標(biāo)。

[BT4]4SQL游標(biāo)的應(yīng)用實(shí)例與分析

游標(biāo)的應(yīng)用重點(diǎn)可表現(xiàn)在2個(gè)方面：

1）使用游標(biāo)從結(jié)果集中檢索數(shù)據(jù)，以實(shí)現(xiàn)對(duì)數(shù)據(jù)的逐行查看，也就是平時(shí)經(jīng)常遇到的一種操作——遍歷。

2）使用游標(biāo)可以對(duì)結(jié)果集中的數(shù)據(jù)進(jìn)行更新或刪除＼[4＼]。改變游標(biāo)中的數(shù)據(jù)，自然會(huì)影響到數(shù)據(jù)庫中基礎(chǔ)表的數(shù)據(jù)，若要使游標(biāo)中的數(shù)據(jù)不致發(fā)生修改，則可以在聲明游標(biāo)時(shí)添加Insensitive選項(xiàng)。

在此，將以某個(gè)學(xué)校學(xué)費(fèi)收繳管理系統(tǒng)中的學(xué)校收費(fèi)數(shù)據(jù)庫為背景，研究給出2個(gè)具體實(shí)例來闡釋說明SQL Server中游標(biāo)的使用方法和應(yīng)用實(shí)現(xiàn)。

[BT5]4.1游標(biāo)的遍歷

4.1.1應(yīng)用說明

所謂遍歷，就是指沿著某條搜索路線，依次對(duì)樹中的每個(gè)結(jié)點(diǎn)均將經(jīng)歷一次的訪問。而對(duì)于游標(biāo)的遍歷而言，游標(biāo)所對(duì)應(yīng)的結(jié)果集是指樹，結(jié)果集中的每一條記錄（每一行）是指每個(gè)結(jié)點(diǎn)，游標(biāo)的遍歷實(shí)際上就是指游標(biāo)的逐行取數(shù)操作。

在SQL Server中，游標(biāo)的使用主要集中在對(duì)游標(biāo)所指結(jié)果集的遍歷操作。通常在執(zhí)行Fetch語句（讀取游標(biāo)數(shù)據(jù)）的時(shí)候，可以借助于全局變量@@Fetch_Status（返回游標(biāo)當(dāng)前的狀態(tài)）的值來判斷提取數(shù)據(jù)的操作是否成功。若值為0，表明讀取數(shù)據(jù)成功；若值為-1，表明執(zhí)行失敗或此行不在結(jié)果集中；若值為-2，表明讀取的數(shù)據(jù)行不存在。在實(shí)際研發(fā)中，F(xiàn)etch語句經(jīng)常和While語句結(jié)合使用來生成對(duì)結(jié)果集中的每一行數(shù)據(jù)實(shí)現(xiàn)遍歷的操作。下面則通過一個(gè)完整實(shí)例來直觀展現(xiàn)游標(biāo)便利的功能應(yīng)用流程。

4.1.2實(shí)例分析

[HT5”H][ST5”HZ]實(shí)例一[HT5”SS][ST5”BZ]利用游標(biāo)遍歷從xxsf數(shù)據(jù)庫的xsxx（學(xué)生信息表）和zysf（專業(yè)收費(fèi)表）中顯示所有文學(xué)院學(xué)生的專業(yè)代碼（zydm）、專業(yè)名稱（zymc）、隸屬院系（lsyx）、學(xué)號(hào)（xh）和姓名（xm），并以報(bào)表的形式提供結(jié)果輸出。

當(dāng)前數(shù)據(jù)庫的基礎(chǔ)表xsxx和zysf，表的結(jié)構(gòu)可如表1和表2，字段zydm、zymc和lsyx來源于表2，字段zydm、xh和xm來源于表1。2張表將通過公共字段zydm建立連接。

4.2使用游標(biāo)更新數(shù)據(jù)

4.2.1應(yīng)用說明

游標(biāo)在聲明的時(shí)候若定義為可更新的，則可用Update語句來修改基礎(chǔ)表中某行的數(shù)據(jù)，當(dāng)然也可以執(zhí)行刪除某行的操作，但不能插入新行。

一般情況下，在Where子句中給定了條件才能修改或刪除數(shù)據(jù)，但鑒于游標(biāo)不能自動(dòng)對(duì)行實(shí)現(xiàn)更新或刪除的原因，因此在聲明游標(biāo)的時(shí)候使用了For Update Of語句，這樣就可以在Update或Delete命令中利用Where Current Of關(guān)鍵字直接修改或刪除當(dāng)前游標(biāo)中存儲(chǔ)的數(shù)據(jù)，而不必使用Where子句重新給出指定條件＼[5＼]。

4.2.2實(shí)例分析

[HT5”H][ST5”HZ]實(shí)例二[HT5”SS][ST5”BZ]利用游標(biāo)將數(shù)據(jù)表xsxx中第3位同學(xué)的入學(xué)年份（rxnf）改為2012年（這里只顯示xsxx表中的rxnf、xh、xm、xb和sfz字段）。

關(guān)鍵代碼如下：

DECLARE cur_xg SCROLL CURSOR FOR

SELECT rxnf，xh，xm，xb，sfz from xsxx

FOR UPDATE OF rxnf --定義表中需要修改的數(shù)據(jù)列

OPEN cur_xg

FETCH ABSOLUTE 3 FROM cur_xg--返回第3行并將其變?yōu)楫?dāng)前行

UPDATE xsxx SET rxnf=2012--更新當(dāng)前行的列值

WHERE CURRENT OF cur_xg--當(dāng)前游標(biāo)指針?biāo)傅漠?dāng)前行數(shù)據(jù)

CLOSE cur_xg

DEALLOCATE cur_xg

這里需要注意的是，游標(biāo)的第二種應(yīng)用是一種不規(guī)范的更新數(shù)據(jù)的途徑，很容易造成數(shù)據(jù)的不一致，因此通常狀況下并不選用游標(biāo)來設(shè)計(jì)更新數(shù)據(jù)表中的數(shù)據(jù)。

另外，還需提及的就是，本文實(shí)例所用的數(shù)據(jù)表均基于xxsf（學(xué)校收費(fèi)數(shù)據(jù)庫）中的2個(gè)表，分別是：

xsxx（rxnf，xh，xm，xb，ksh，sfz，zydm，zsid）

zysf（zydm，zymc，lsyx，pycc，xxnx，xfbz，jcfbz）

[BT4]5結(jié)束語

SQL Server中的游標(biāo)在原理上具有C語言指針一樣的語言結(jié)構(gòu)，相應(yīng)地則設(shè)計(jì)提供了一種在服務(wù)器內(nèi)部處理結(jié)果集的方法。使用游標(biāo)可以通過遍歷操作逐一地從結(jié)果集中實(shí)現(xiàn)數(shù)據(jù)讀取，也可以對(duì)結(jié)果集中某些數(shù)據(jù)重點(diǎn)加設(shè)更新或刪除的操作。本文以學(xué)校收費(fèi)數(shù)據(jù)庫為背景并結(jié)合具體的實(shí)例來闡述游標(biāo)的使用，游標(biāo)可以面向結(jié)果集中的每一行進(jìn)行相同或不同的操作，這不僅提升了SQL語句處理復(fù)雜查詢的能力，而且還降低了系統(tǒng)開銷和潛在的阻隔情況，在一定程度上解決了許多應(yīng)用程序不能把整個(gè)結(jié)果集作為一個(gè)單元來處理的問題。

參考文獻(xiàn)：

趙慧玲，毛應(yīng)爽，孟憲穎. 基于SQL游標(biāo)的研究與應(yīng)用[J]. 科技創(chuàng)新導(dǎo)報(bào)，2012（28）：31-32.

[2] 劉志成，寧云智，劉釗，編著. SQL Server實(shí)例教程[M]. 北京：電子工業(yè)出版社，2013.

[3] 薛麗香，汪東芳. 淺談SQL Server數(shù)據(jù)庫中游標(biāo)的使用[J]. 福建電腦，2016（6）：157-158.

[4] 陳芳勤. SQL Server 2000中游標(biāo)的應(yīng)用[J]. 中國科技信息，2008（13）：96，99.

[5] 黃龍軍. 游標(biāo)在Online Judge中的應(yīng)用[J]. 紹興文理學(xué)院學(xué)報(bào)（自然科學(xué)），2012，32（8）：26-29.