函數(shù)游標(biāo)過(guò)稱

函數(shù)游標(biāo)過(guò)稱第一頁(yè)，共七十頁(yè)，2022年，8月28日學(xué)習(xí)目標(biāo)：通過(guò)本章學(xué)習(xí)，你能夠?qū)W會(huì)：在進(jìn)行SQLServer程序設(shè)計(jì)時(shí)涉及的批處理、事務(wù)、局部變量、全局變量的概念及使用方法在進(jìn)行SQLServer程序設(shè)計(jì)時(shí)涉及到的程序控制語(yǔ)句的使用方法在進(jìn)行SQLServer程序設(shè)計(jì)時(shí)還會(huì)用到游標(biāo)，在本章中還可以學(xué)到游標(biāo)的使用方法在進(jìn)行SQLServer程序設(shè)計(jì)時(shí)為使編程簡(jiǎn)潔清晰還會(huì)用到用戶自定義函數(shù)，在本章將會(huì)學(xué)到用戶如何定義自己的函數(shù)第二頁(yè)，共七十頁(yè)，2022年，8月28日11．1程序注釋語(yǔ)句

前面介紹的Transact-SQL語(yǔ)句以及一些編程的方法和技巧在處理一些簡(jiǎn)單的查詢和實(shí)際應(yīng)用時(shí)還可以完全勝任，但是，在有些時(shí)候，這些工具無(wú)法滿足一些特殊要求，例如，要實(shí)現(xiàn)與用戶的交互操作等。那么，了解和掌握如何使用Transact-SQL語(yǔ)句進(jìn)行程序設(shè)計(jì)是非常必要的。第三頁(yè)，共七十頁(yè)，2022年，8月28日在程序設(shè)計(jì)的過(guò)程中，注釋語(yǔ)句的主要作用是幫助他人了解程序的具體內(nèi)容，同時(shí)也便于我們對(duì)程序結(jié)構(gòu)有所了解和掌握。試想如果你編了一段100行左右的程序，如果一個(gè)月后在來(lái)瀏覽，在沒(méi)有注釋的情況下，即使記憶力再好的程序員，也得花一番功夫才能把這段程序側(cè)底弄清楚。因此經(jīng)常書寫程序注釋語(yǔ)句是一種非常好的編程習(xí)慣。第四頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.1]下面是一個(gè)關(guān)于創(chuàng)建學(xué)生表的程序，請(qǐng)大家注意注釋語(yǔ)句的書寫操作步驟：通過(guò)開始菜單打開查詢分析器。在查詢分析器的編輯窗口輸入程序設(shè)計(jì)語(yǔ)句，如下圖所示第五頁(yè)，共七十頁(yè)，2022年，8月28日批處理是一個(gè)以GO為結(jié)束標(biāo)志的SQL語(yǔ)句組。批中的所有SQL語(yǔ)句作為一個(gè)整體編譯成一個(gè)執(zhí)行單元后一次提交給SQLServer服務(wù)器執(zhí)行。有多個(gè)批時(shí),系統(tǒng)按批地順序依次執(zhí)行。11．2批處理

第六頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.2]使用批處理選擇Pubs數(shù)據(jù)庫(kù)，然后創(chuàng)建一個(gè)視圖，最后使用視圖進(jìn)行查詢。操作步驟： 通過(guò)開始菜單打開查詢分析器。在查詢分析器中輸入如下語(yǔ)句執(zhí)行結(jié)果如圖11.2所示第七頁(yè)，共七十頁(yè)，2022年，8月28日?qǐng)D11.2批處理執(zhí)行結(jié)果第八頁(yè)，共七十頁(yè)，2022年，8月28日由上面的例子可以看出來(lái)，所有的批處理命令都使用GO作為結(jié)束標(biāo)志。當(dāng)編譯器讀到GO時(shí)，它就會(huì)把GO前面所有的語(yǔ)句當(dāng)作一個(gè)批處理，而打包成一個(gè)數(shù)據(jù)包發(fā)送到服務(wù)器。GO本身并不是Transact-SQL語(yǔ)句組部分，它只是一個(gè)用于表示批處理結(jié)束的前端指令。當(dāng)有多個(gè)批時(shí),系統(tǒng)按批地順序依次執(zhí)行,關(guān)于批中有如下幾點(diǎn)需要注意：

(1)一個(gè)批處理中若有多個(gè)存儲(chǔ)過(guò)程,則從第二個(gè)存儲(chǔ)過(guò)程開始,每個(gè)存儲(chǔ)過(guò)程的前面都要加關(guān)鍵字EXECUTE或EXEC.(2)一般地，一個(gè)CREATE語(yǔ)句必須獨(dú)自占用一個(gè)批處理，且必須以CREATE語(yǔ)句開始。

(3)若使用ALTERTABLE語(yǔ)句修改表的結(jié)構(gòu)，則不能在同一個(gè)批中引用新定義或修改的列。第九頁(yè)，共七十頁(yè)，2022年，8月28日?qǐng)?zhí)行一個(gè)批時(shí)，若其中有一個(gè)語(yǔ)句出現(xiàn)編譯錯(cuò)誤，則SQLServer將取消該批內(nèi)所有語(yǔ)句的執(zhí)行。但若一個(gè)批通過(guò)了編譯而在運(yùn)行時(shí)出現(xiàn)錯(cuò)誤，則批內(nèi)發(fā)生錯(cuò)誤之前的語(yǔ)句將被執(zhí)行，發(fā)生錯(cuò)誤的語(yǔ)句不被執(zhí)行，發(fā)生錯(cuò)誤之后的語(yǔ)句視具體情況可能被執(zhí)行或不被執(zhí)行。因此，對(duì)于僅執(zhí)行了一部分的批，可能引起整個(gè)過(guò)程發(fā)生錯(cuò)誤或產(chǎn)生數(shù)據(jù)垃圾。第十頁(yè)，共七十頁(yè)，2022年，8月28日為防止這類情況出現(xiàn)，可以在SQLServer編程中引入“事務(wù)”（將在下節(jié)講到），將批處理放在一個(gè)事務(wù)中，并設(shè)置批處理發(fā)生錯(cuò)誤時(shí)引發(fā)事務(wù)回滾，使所有尚未提交的操作都回滾到發(fā)生錯(cuò)誤之前的狀態(tài)。第十一頁(yè)，共七十頁(yè)，2022年，8月28日事務(wù)是SQLServer的一個(gè)工作單元，事務(wù)通常以BEGINTRANSACTION開始，以COMMITTRANSACTION結(jié)束。如果在一個(gè)事務(wù)模塊中有一個(gè)SQL語(yǔ)句執(zhí)行失敗或發(fā)生不該有的結(jié)果，可以通過(guò)設(shè)置回滾命令ROLLBACKTRANSACTION取消該語(yǔ)句導(dǎo)致的錯(cuò)誤操作結(jié)果(例如取消已插入的數(shù)據(jù)或恢復(fù)已被修改的數(shù)據(jù))，使數(shù)據(jù)庫(kù)恢復(fù)到該錯(cuò)誤語(yǔ)句執(zhí)行前的狀態(tài)。11．3事務(wù)

第十二頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.3]利用事務(wù)在第4章中的學(xué)生庫(kù)的學(xué)生信息表中插入兩行數(shù)據(jù)，在插入第2行數(shù)據(jù)后回滾到兩次插入數(shù)據(jù)之間，然后查詢。步驟：?jiǎn)?dòng)“查詢分析器”，輸入SQL語(yǔ)句：得到如下執(zhí)行結(jié)果如圖11.3所示11.3利用事務(wù)執(zhí)行語(yǔ)句第十三頁(yè)，共七十頁(yè)，2022年，8月28日然后對(duì)“課程”表進(jìn)行查詢，執(zhí)行結(jié)果如下，如圖11.4所示。圖11.4事務(wù)執(zhí)行后的查詢結(jié)果第十四頁(yè)，共七十頁(yè)，2022年，8月28日這個(gè)例子一共執(zhí)行了兩次插入操作，第一次操作完成后，設(shè)置了一個(gè)保存點(diǎn)，第二次插入操作執(zhí)行完后，程序執(zhí)行了事務(wù)回滾，使第二次的插入操作被取消，但是第一次插入操作依然有效，這樣查詢后就只能得到一行插入數(shù)據(jù)。如果不設(shè)置保存點(diǎn)，則默認(rèn)回滾整個(gè)事務(wù)，兩次插入操作都將被取消。第十五頁(yè)，共七十頁(yè)，2022年，8月28日從上面的例子也可以看出，在一個(gè)事務(wù)中可以包含多個(gè)批處理，當(dāng)然一個(gè)批處理也可以包含多個(gè)事務(wù)。兩者的區(qū)別是，批語(yǔ)句的組合發(fā)生在編譯階段，而事務(wù)語(yǔ)句的組合發(fā)生在執(zhí)行階段。一個(gè)批若在編譯階段發(fā)生語(yǔ)法錯(cuò)誤，則在運(yùn)行階段整個(gè)批都不能執(zhí)行。若編譯通過(guò)而在運(yùn)行時(shí)發(fā)生錯(cuò)誤，如違反主鍵惟一性約束等，則有兩種情況：若沒(méi)有設(shè)置事物回滾，默認(rèn)時(shí)系統(tǒng)僅回滾發(fā)生錯(cuò)誤的語(yǔ)句；若設(shè)置了事務(wù)回滾，則系統(tǒng)回滾事務(wù)。第十六頁(yè)，共七十頁(yè)，2022年，8月28日由上面的例子我們可以看出，建立一個(gè)事務(wù)包含四個(gè)語(yǔ)句，即開始事務(wù)、設(shè)置保存點(diǎn)、回滾事務(wù)、提交事務(wù)。

(1)開始(或啟動(dòng))一個(gè)事務(wù)單元。命令格式：

BEGINTRANSACTION單元名稱

(2)在事務(wù)中設(shè)置一個(gè)保存點(diǎn)。保存點(diǎn)的設(shè)置可以使得回滾事務(wù)語(yǔ)句回滾到該保存點(diǎn)，以便取消該事務(wù)中的部分操作對(duì)數(shù)據(jù)庫(kù)所引起的變動(dòng)。命令格式：

SAVETRANSACTION保存點(diǎn)名稱第十七頁(yè)，共七十頁(yè)，2022年，8月28日(3)回滾一個(gè)事務(wù)單元。該語(yǔ)句用于取消事務(wù)中的部分或全部操作，使得這些操作對(duì)數(shù)據(jù)庫(kù)所作的改動(dòng)恢復(fù)為改動(dòng)前的狀態(tài)。命令格式：

ROLLBACKTRANSACTION單元名稱|保存點(diǎn)名稱

(4)提交(或完成)一個(gè)事務(wù)單元。提交一個(gè)事物后，在該事物中對(duì)數(shù)據(jù)庫(kù)所做的變動(dòng)被保存到數(shù)據(jù)庫(kù)中。命令格式：

COMMITTRANSACTION單元名稱第十八頁(yè)，共七十頁(yè)，2022年，8月28日注意，一旦執(zhí)行了COMMITTRANSACTION語(yǔ)句，則事務(wù)被提交，此時(shí)將不再能夠回滾事務(wù)。在一個(gè)事務(wù)內(nèi)允許有重復(fù)的保存點(diǎn)名稱，但若在ROLLBACKTRANSACTION語(yǔ)句中使用重復(fù)的保存點(diǎn)名稱，則事物只回滾到離它最近的使用該保存點(diǎn)名稱的SAVETRANSACTION語(yǔ)句處。第十九頁(yè)，共七十頁(yè)，2022年，8月28日

Transact-SQL語(yǔ)言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。在使用方法以及具體意義上，這兩種變量都有著較大的區(qū)別，本節(jié)將分別介紹這兩種變量的使用方法。11．4變量

第二十頁(yè)，共七十頁(yè)，2022年，8月28日局部變量是在批處理（也可以是存儲(chǔ)過(guò)程或觸發(fā)器）內(nèi)由用戶定義并使用的變量。用戶在使用局部變量前必須事先聲明，而它的使用范圍也僅限于聲明它的批處理（也可以是存儲(chǔ)過(guò)程或觸發(fā)器）以內(nèi)。[實(shí)例11.4]

利用局部變量在數(shù)據(jù)庫(kù)學(xué)生庫(kù)的學(xué)生信息表中查詢學(xué)生“和平”的年齡。步驟：?jiǎn)?dòng)查詢分析器，輸入如下SQL語(yǔ)句：按“F5”鍵或單擊工具欄“執(zhí)行查詢”圖標(biāo)執(zhí)行。執(zhí)行結(jié)果如圖11.5所示局部變量

第二十一頁(yè)，共七十頁(yè)，2022年，8月28日?qǐng)D11.5使用局部變量查詢的結(jié)果第二十二頁(yè)，共七十頁(yè)，2022年，8月28日從上面的例子，我們可以看出，通過(guò)聲明局部變量可以查詢學(xué)生信息表中任意的學(xué)生信息，只要修改賦給局部變量的值就可以。在使用局部變量查詢需要做到兩點(diǎn)：一是聲明局部變量，二是給局部變量賦值。第二十三頁(yè)，共七十頁(yè)，2022年，8月28日（1）聲明局部變量命令格式：

DECLARE(@局部變量名數(shù)據(jù)類型[，….n])

命令說(shuō)明：DECLARE：該關(guān)鍵字表示將要聲明變量。數(shù)據(jù)類型可以是除text、ntext和image外的數(shù)據(jù)類型或用戶定義的數(shù)據(jù)類型。[，….n]：表示一個(gè)DECLARE語(yǔ)句中可以聲明多個(gè)局部變量，這些變量用逗號(hào)相隔。(2)局部變量的賦值聲明局部變量后，系統(tǒng)自動(dòng)為變量賦初值NULL。若需要另外賦值，可以使用SET或SELECT語(yǔ)句。賦值語(yǔ)法如下：命令格式：

SELECT@局部變量名=表達(dá)式

[FROM表名[,….n]WHEREclause][+·n)

或SET@局部變量名=表達(dá)式第二十四頁(yè)，共七十頁(yè)，2022年，8月28日命令說(shuō)明：表達(dá)式可以是一個(gè)具體的數(shù)據(jù)如數(shù)字、字符串等，也可以是一個(gè)表達(dá)式或另一個(gè)局部變量或全局變量，還可以是從一個(gè)查詢語(yǔ)句中查詢出來(lái)的數(shù)據(jù)。FROM子句用于向變量所賦的值源于由一個(gè)表中查詢所得數(shù)據(jù)的情形。一個(gè)SELECT語(yǔ)句可以為多個(gè)變量賦值，但一個(gè)SET語(yǔ)句僅能為一個(gè)變量賦值。若使用SELECT語(yǔ)句為變量賦值，則不能與其查詢功能同時(shí)使用。若使用SELECT語(yǔ)句從表中取數(shù)為變量賦值，則其返回的數(shù)據(jù)必須惟一，否則僅將最后一個(gè)數(shù)據(jù)賦給變量。第二十五頁(yè)，共七十頁(yè)，2022年，8月28日下面的例子是使用SELECT語(yǔ)句進(jìn)行賦值，從表格中查詢出一條數(shù)據(jù)賦值給已經(jīng)聲明的變量：

USEpubsGODECLARE@authorvarchar(20)SELECT@author=au_idFROMauthorsWHEREstate=’MI’GO第二十六頁(yè)，共七十頁(yè)，2022年，8月28日11.4.2全局變量

在SQLServer中全局變量作為一種特殊函數(shù)由系統(tǒng)預(yù)先定義，因此有的教材把全局變量放到函數(shù)部分介紹，稱為SQLServer的內(nèi)置函數(shù)。常用的全局變量有三十多個(gè)，用于記錄數(shù)據(jù)庫(kù)服務(wù)器的工作信息及性能數(shù)據(jù)。全局變量的作用范圍是整個(gè)系統(tǒng)，通常利用全局變量來(lái)檢測(cè)系統(tǒng)的設(shè)置值或執(zhí)行查詢命令后的狀態(tài)值。第二十七頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.5]

使用全局變量來(lái)查詢SQLServer實(shí)例當(dāng)前使用的語(yǔ)言和當(dāng)前所使用語(yǔ)言的標(biāo)識(shí)符。第二十八頁(yè)，共七十頁(yè)，2022年，8月28日

由上例可以看出全局變量以@@開頭，無(wú)需定義直接使用即可。除了上面的全局變量外，還有如@@CONNECTIONS返回自上次啟動(dòng)SQLServer數(shù)據(jù)庫(kù)服務(wù)器以來(lái)連接或試圖連接的次數(shù)，@@ERROR返回最近一次執(zhí)行T-SQL語(yǔ)句的錯(cuò)誤代碼，@@version表示SQLServer的版本號(hào)等等，其他全局變量可參見幫助。第二十九頁(yè)，共七十頁(yè)，2022年，8月28日

SQLServer支持結(jié)構(gòu)化的編程方法，可以進(jìn)行順序結(jié)構(gòu)、選擇結(jié)構(gòu)、循環(huán)結(jié)構(gòu)的編程。本節(jié)結(jié)合例題介紹SQLServer2000的程序控制語(yǔ)句。

11．5程序控制語(yǔ)句

第三十頁(yè)，共七十頁(yè)，2022年，8月28日11．5．1BEGIN…END語(yǔ)句

BEGIN…END語(yǔ)句相當(dāng)于其他計(jì)算機(jī)語(yǔ)言中的大括號(hào)或復(fù)合語(yǔ)句，它將多條T-SQL語(yǔ)句封裝成為一個(gè)整體的語(yǔ)句塊，T-SQL中允許使用嵌套的BEGIN…END語(yǔ)句。通常把BEGIN…END語(yǔ)句放在語(yǔ)句IF…ELSE或者WHILE中，使其中的語(yǔ)句作為一個(gè)整體來(lái)執(zhí)行。第三十一頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.6]在學(xué)生成績(jī)表期中成績(jī)中檢查全體學(xué)生某門課程(課程編號(hào)為1001)的期中成績(jī)。如果某個(gè)學(xué)生的成績(jī)低于60分，則顯示文本：“1001科目的分?jǐn)?shù)低于60分”，并顯示所有未及格學(xué)生的學(xué)號(hào)、姓名與期中成績(jī)分?jǐn)?shù)。第三十二頁(yè)，共七十頁(yè)，2022年，8月28日命令格式：

BEGINT-SQL語(yǔ)句塊

END

命令說(shuō)明：BEGIN、END分別表示語(yǔ)句塊的開始和結(jié)束。T-SQL語(yǔ)句塊可以是一條或一條以上的T-SQL語(yǔ)句。第三十三頁(yè)，共七十頁(yè)，2022年，8月28日11．5．2IF…ELSE語(yǔ)句

若希望在滿足某種條件的情況下執(zhí)行一些語(yǔ)句，而在該條件不滿足的情況下執(zhí)行另外一些語(yǔ)句。在這種情況下，我們就需要使用條件語(yǔ)句來(lái)在執(zhí)行T-SQL語(yǔ)句時(shí)強(qiáng)加條件，來(lái)控制語(yǔ)句執(zhí)行的順序。第三十四頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.7]在學(xué)生信息表中統(tǒng)計(jì)學(xué)生的平均年齡，如果平均年齡小于20則輸出年齡組合正常，否則輸出大于平均年齡的學(xué)生的信息。第三十五頁(yè)，共七十頁(yè)，2022年，8月28日由上例我們可歸納出IF...ELSE語(yǔ)句的命令格式：

IF返回布爾表達(dá)式{T-SQL語(yǔ)句|BEGIN…END語(yǔ)句塊}[ELSE{T-SQL語(yǔ)句|BEGIN…END語(yǔ)句塊}]命令說(shuō)明：返回“真”(TRUE)或“偽”(FALSE)的布爾表達(dá)式。如果布爾表達(dá)式中含有SELECT語(yǔ)句，則必須用圓括號(hào)將SELECT語(yǔ)句括起來(lái)。T-SQL語(yǔ)句|BEGIN…END語(yǔ)句塊：只能是一條。第三十六頁(yè)，共七十頁(yè)，2022年，8月28日11.5.3CASE語(yǔ)句

當(dāng)有多個(gè)選擇方案時(shí)，如果使用IF…ELSE語(yǔ)句進(jìn)行選擇判斷，則由于需要層層嵌套而非常不便，此時(shí)可以在SELECT語(yǔ)句中采用CASE結(jié)構(gòu)。CASE語(yǔ)句有兩種格式：簡(jiǎn)單CASE函數(shù)與CASE搜索函數(shù)。前者將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果；后者則需計(jì)算一組布爾表達(dá)式以確定結(jié)果。下面用實(shí)例加以說(shuō)明。第三十七頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.8]在pubs數(shù)據(jù)庫(kù)中根據(jù)作者來(lái)自的州的名字，打印出作者的姓名和州的全名第三十八頁(yè)，共七十頁(yè)，2022年，8月28日命令格式：CASE輸入表達(dá)式

WHEN比較的表達(dá)式THEN結(jié)果表達(dá)式[…n][ELSE返回結(jié)果表達(dá)式]END

命令說(shuō)明；CASE后面的輸入表達(dá)式，用于進(jìn)行比較。WHEN后面給出用于進(jìn)行比較的表達(dá)式的數(shù)據(jù)類型必須相同，或者能夠隱性轉(zhuǎn)換。[…n)表示可以有多個(gè)WHEN…THEN結(jié)構(gòu)。第三十九頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.9]

顯示學(xué)生成績(jī)中的期中成績(jī)分?jǐn)?shù)。第四十頁(yè)，共七十頁(yè)，2022年，8月28日由上例可見，CASE搜索函數(shù)的WHEN后表達(dá)式是布爾表達(dá)式，這是與簡(jiǎn)單CASE函數(shù)不同之處。命令格式；CASEWHEN邏輯表達(dá)式THEN[ELSE結(jié)果表達(dá)式]END第四十一頁(yè)，共七十頁(yè)，2022年，8月28日11．5．4WHILE語(yǔ)句

WHILE語(yǔ)句是一種條件循環(huán)語(yǔ)句，在滿足條件時(shí)執(zhí)行循環(huán)體，不滿足條件時(shí)跳出循環(huán)結(jié)構(gòu)。WHILE語(yǔ)句可以嵌套執(zhí)行，嵌套時(shí)首先運(yùn)行內(nèi)層循環(huán)，內(nèi)層循環(huán)結(jié)束之后再運(yùn)行外層循環(huán)。第四十二頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.10]在pubs數(shù)據(jù)庫(kù)中，將書價(jià)小于$30的書提高50%，如果提高后的書價(jià)超過(guò)$50，則輸出書價(jià)太貴。第四十三頁(yè)，共七十頁(yè)，2022年，8月28日由此，我們可給出命令格式：

WHILE邏輯表達(dá)式BEGIN

表達(dá)式

[BREAK][CONTINUE]END

命令說(shuō)明：當(dāng)邏輯表達(dá)式為真時(shí)，服務(wù)器將重復(fù)執(zhí)行SQL語(yǔ)句組。BREAK的作用是在某些情況發(fā)生時(shí)，立即無(wú)條件地跳出循環(huán)，并開始執(zhí)行緊接在END后面的語(yǔ)句。CONTINUE的作用是在某些情況發(fā)生時(shí)，跳出本次循環(huán)，開始執(zhí)行下一次循環(huán)。第四十四頁(yè)，共七十頁(yè)，2022年，8月28日11．5．5RETURN語(yǔ)句

RETURN語(yǔ)句用于從當(dāng)前過(guò)程、批處理或語(yǔ)句塊中無(wú)條件退出，并不再執(zhí)行位于RETURN之后的語(yǔ)句。命令格式：

RETURN整型表達(dá)式命令說(shuō)明：RETURN一般用于從存儲(chǔ)過(guò)程給調(diào)用過(guò)程或應(yīng)用程序返回整型值。除非特別指明，所有系統(tǒng)存儲(chǔ)過(guò)程返回0值表示成功，返回非0值則表示失敗。第四十五頁(yè)，共七十頁(yè)，2022年，8月28日11.6游標(biāo)

數(shù)據(jù)庫(kù)的游標(biāo)是類似于C語(yǔ)言指針一樣的語(yǔ)言結(jié)構(gòu)。SELECT語(yǔ)句是針對(duì)數(shù)據(jù)庫(kù)中表的所有數(shù)據(jù)進(jìn)行處理的，如果需要對(duì)某一行數(shù)據(jù)進(jìn)行處理，使用SELECT語(yǔ)句就顯得力不從心。游標(biāo)正好彌補(bǔ)了這一缺陷，它類似于一個(gè)指針，可以定位于SELECT語(yǔ)句結(jié)果集合中的某指定行，從而對(duì)該行的數(shù)據(jù)進(jìn)行操作。游標(biāo)的使用過(guò)程依次為：聲明游標(biāo)、打開游標(biāo)、使用游標(biāo)、關(guān)閉游標(biāo)、釋放游標(biāo)。第四十六頁(yè)，共七十頁(yè)，2022年，8月28日聲明游標(biāo)

[實(shí)例11.11]

定義游標(biāo)，打開學(xué)生信息表。第四十七頁(yè)，共七十頁(yè)，2022年，8月28日上面的例子首先通過(guò)DECLARE關(guān)鍵字來(lái)定義游標(biāo)，然后選擇一個(gè)表，打開游標(biāo)，就可以查詢到當(dāng)前表中記錄的個(gè)數(shù)。其中@@cursor_row表示當(dāng)前游標(biāo)記錄數(shù)目是一個(gè)全局變量。由此，我們給出打開游標(biāo)的命令格式：

DECLARE游標(biāo)名稱[INSENSITIVE][SCROLL]CURSORFORSELECT語(yǔ)句

[FORREADONLY|UPDATE[OF列名1，列名2，列名3…]]第四十八頁(yè)，共七十頁(yè)，2022年，8月28日命令說(shuō)明：游標(biāo)名：為聲明游標(biāo)所起的名字。INSENSITIVE：使用INSENSITIVE定義的游標(biāo)，把提取出來(lái)的數(shù)據(jù)放入一個(gè)在tempdb數(shù)據(jù)庫(kù)里創(chuàng)建的臨時(shí)表里。任何通過(guò)這個(gè)游標(biāo)進(jìn)行的操作，都在這個(gè)臨時(shí)表里進(jìn)行。所以所有對(duì)基本表的改動(dòng)都不會(huì)在用游標(biāo)進(jìn)行的操作中體現(xiàn)出來(lái)。SCROLL：使用SCROLL關(guān)鍵字定義的游標(biāo)，具有包括如下所示的所有取數(shù)功能：FIRST：取第一行數(shù)據(jù)；LAST：取最后一行數(shù)據(jù)；PRIOR：取前一行數(shù)據(jù)；NEXT：取最后一行數(shù)據(jù)；RELATIVE：按相對(duì)位置取數(shù)據(jù)；ABSOLUTE：按絕對(duì)位置取數(shù)據(jù)。如果沒(méi)有在聲明時(shí)使用SCROLL關(guān)鍵字，那么所聲明的游標(biāo)只具有默認(rèn)的NEXT功能。第四十九頁(yè)，共七十頁(yè)，2022年，8月28日SELECT語(yǔ)句：一些標(biāo)準(zhǔn)的SELECT語(yǔ)句，主要用來(lái)定義游標(biāo)所要進(jìn)行處理的結(jié)果集。在聲明游標(biāo)的SELECT語(yǔ)句中，不允許使用如COMPUTE，COMPUTEBY和INTO等關(guān)鍵字。FORREADONLY：聲明只讀光標(biāo)。不允許通過(guò)只讀光標(biāo)進(jìn)行數(shù)據(jù)的更新。UPDATE[OF列名1，列名2，列名3…]：定義在這個(gè)游標(biāo)里可以更新的列。如果定義了[OF列名1，列名2，列名3…]，那么只有列在表中的列可以被更新；如果沒(méi)有定義[OF列名1，列名2，列名3…]，那么游標(biāo)里的所有列都可以被更新。第五十頁(yè)，共七十頁(yè)，2022年，8月28日打開游標(biāo)

在可以使用游標(biāo)之前，必須首先打開游標(biāo)。開游標(biāo)的語(yǔ)法如下：

OPENcursor_name使用關(guān)鍵字OPEN來(lái)打開游標(biāo)，cursor_name為打開游標(biāo)的名字。具體例子可參考[實(shí)例11.11]第五十一頁(yè)，共七十頁(yè)，2022年，8月28日11．6．3關(guān)閉與釋放游標(biāo)

在打開游標(biāo)后，SQLServer服務(wù)器會(huì)專門為游標(biāo)開辟一定的內(nèi)存空間存放游標(biāo)操作的數(shù)據(jù)結(jié)果集，同時(shí)游標(biāo)的使用也會(huì)根據(jù)具體情況對(duì)某些數(shù)據(jù)進(jìn)行封鎖。所以，在不使用游標(biāo)的時(shí)候，一定要關(guān)閉游標(biāo)，以通知服務(wù)器釋放游標(biāo)所占用的資源。關(guān)閉游標(biāo)的語(yǔ)法：

CLOSEcursor_name第五十二頁(yè)，共七十頁(yè)，2022年，8月28日關(guān)閉游標(biāo)后可以再次打開游標(biāo)，在一個(gè)批處理中也可以多次打開和關(guān)閉游標(biāo)。而游標(biāo)本身也會(huì)占用一定的計(jì)算機(jī)資源，所以在使用完游標(biāo)后，為了回收被游標(biāo)占用的資源，應(yīng)該將游標(biāo)釋放。釋放游標(biāo)的語(yǔ)法是：

DEALLOCATEcursor_name

當(dāng)釋放完游標(biāo)后，如果要重新使用這個(gè)游標(biāo)必須重新執(zhí)行聲明游標(biāo)的語(yǔ)句。第五十三頁(yè)，共七十頁(yè)，2022年，8月28日11．6．4使用游標(biāo)

在打開游標(biāo)以后，就可以打開游標(biāo)通過(guò)FETCH語(yǔ)句提取數(shù)據(jù)了。[實(shí)例11.12]根據(jù)例11.11使用絕對(duì)位置以及相對(duì)位置來(lái)提取信息。第五十四頁(yè)，共七十頁(yè)，2022年，8月28日

FETCH語(yǔ)句的目的是從T-SQL服務(wù)器游標(biāo)中檢索特定的記錄。使用游標(biāo)提取數(shù)據(jù)的語(yǔ)法如下：

FETCH[[NEXT∣PRIOR∣FIRST∣LAST∣ABSOLUTE{n∣@nvar}∣IRELATIVE{n∣@nvar}FROM]{{游標(biāo)名稱INTO變量第五十五頁(yè)，共七十頁(yè)，2022年，8月28日命令說(shuō)明：NEXT：從當(dāng)前記錄立即返回結(jié)果集合以及對(duì)返回的記錄增加當(dāng)前記錄。如果FETCINEXT是通過(guò)游標(biāo)第一次提取，返回結(jié)果集合的第一條記錄。NEXT是提取游標(biāo)選項(xiàng)的默認(rèn)選項(xiàng)。在處理當(dāng)前記錄之前立即返回記錄以及減少返回記錄的當(dāng)前記錄數(shù)目。PRIOR：讀取當(dāng)前游標(biāo)指針?biāo)赶蛐械纳弦恍袛?shù)據(jù)，并將游標(biāo)指向該上一行。如果FETCHPRIOR是通過(guò)游標(biāo)第一次提取，沒(méi)有記錄返回并且光標(biāo)停留在第一行。FIRST：返回第一條記錄并且作為當(dāng)前記錄。LAST：返回最后一條記錄并作為當(dāng)前記錄。ABSOLUTE{n∣@nva}:如果n或者@nva正數(shù)，返回從前面游標(biāo)起的第n記錄，并將其作為當(dāng)前記錄；如果為負(fù)數(shù)，那么返回當(dāng)前游標(biāo)前面的第n條記錄；如果是0，則沒(méi)有記錄返回。RELATIVE{n∣@nvar}：相對(duì)當(dāng)前游標(biāo)位置。第五十六頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.13]

修改第5行的班級(jí)內(nèi)容：第五十七頁(yè)，共七十頁(yè)，2022年，8月28日11．7用戶自定義函數(shù)

在SQLServer2000中，為了使邏輯編程更為簡(jiǎn)潔清晰，設(shè)計(jì)了函數(shù)的功能。有兩類函數(shù)，系統(tǒng)內(nèi)置函數(shù)和用戶自定義函數(shù)。系統(tǒng)內(nèi)置函數(shù)包括數(shù)學(xué)函數(shù)(如max、min、count等)、字符串函數(shù)(如len、left等)、日期函數(shù)(如year、getdate等)、系統(tǒng)函數(shù)(如user)，這些系統(tǒng)內(nèi)置函數(shù)。第五十八頁(yè)，共七十頁(yè)，2022年，8月28日除了可以使用系統(tǒng)提供的內(nèi)置函數(shù)外，用戶可以定義自己的函數(shù)，來(lái)補(bǔ)充和擴(kuò)展系統(tǒng)支持的內(nèi)置函數(shù)。用戶自定義函數(shù)可以像系統(tǒng)函數(shù)一樣在查詢或存儲(chǔ)過(guò)程中使用，也可以想存儲(chǔ)過(guò)程一樣通過(guò)EXECUTE命令來(lái)執(zhí)行。第五十九頁(yè)，共七十頁(yè)，2022年，8月28日11．7．1創(chuàng)建和調(diào)用用戶自定義函數(shù)

[實(shí)例11.14]創(chuàng)建一個(gè)函數(shù)，根據(jù)是否為團(tuán)員返回不同的數(shù)據(jù)，如果輸入為1，即為團(tuán)員則返回該團(tuán)員的信息，否則返回非團(tuán)員的信息。第六十頁(yè)，共七十頁(yè)，2022年，8月28日語(yǔ)句如下：

CREATEFUNCTIONee(@yongBIT)RETURNS@infoTABLE/*定義返回的表結(jié)構(gòu)*/(學(xué)號(hào)char(7),

姓名char(8),

期末成績(jī)int)ASBEGINIF@yong=1INSERT@info/*返回?cái)?shù)據(jù)*/SELECT學(xué)生信息.學(xué)號(hào),姓名,期末成績(jī)

FROM學(xué)生信息,學(xué)生成績(jī)

WHERE是否團(tuán)員=1and學(xué)生信息.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)ELSEINSERT@info/*返回?cái)?shù)據(jù)*/SELECT學(xué)生信息.學(xué)號(hào),姓名,期末成績(jī)

FROM學(xué)生信息,學(xué)生成績(jī)

WHERE是否團(tuán)員=0and學(xué)生信息.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)RETURNENDGOUSE學(xué)生庫(kù)SELECT*FROMdbo.ee(1)GO第六十一頁(yè)，共七十頁(yè)，2022年，8月28日我們可以給出創(chuàng)建函數(shù)的命令格式：

CREATEFUNCTION[所有者]函數(shù)的名稱

([{自定義函數(shù)的參數(shù)[AS]定義函數(shù)參數(shù)的數(shù)據(jù)類型])<function_option>：RETURNS函數(shù)返回值的數(shù)據(jù)類型

[WITH<函數(shù)選項(xiàng)>[[，]．．．n]][AS]BEGIN

函數(shù)體

RETURN標(biāo)量值表達(dá)式

END：={ENCRYPTION|SCHEMABINDING}第六十二頁(yè)，共七十頁(yè)，2022年，8月28日11．7．2修改和刪除用戶自定義函數(shù)

刪除用戶自定義的函數(shù)的語(yǔ)法是：

DROPFUNCTION{[owner_name.]function_name}[,...n]命令說(shuō)明：

(1)DROPFUNCTION：刪除函數(shù)命令的關(guān)鍵字。

(2)owner_name：刪除表的權(quán)力僅屬于表的擁有者，且不能轉(zhuǎn)移。

(3)function_name：函數(shù)的名稱。第六十三頁(yè)，共七十頁(yè)，2022年，8月28日[實(shí)例11.15]將對(duì)前面定義的ee函數(shù)進(jìn)行修改，修改為如果為團(tuán)員則輸出期末成績(jī)大于80的學(xué)生的信息。語(yǔ)句如下：ALTERFUNCTIONee(@yongBIT)/*修改函數(shù)ee*/RETURNS@infoTABLE/*定義返回的表結(jié)構(gòu)*/(學(xué)號(hào)char(7),

姓名char(8),

期末成績(jī)int)AS第六十四頁(yè)，共七十頁(yè)，2022年，8月28日BEGINIF@yong=1INSERT@info/*返回?cái)?shù)據(jù)*/SELECT學(xué)生信息.學(xué)號(hào),姓名,期末成績(jī)

FROM學(xué)生信息,學(xué)生成績(jī)

WHERE是否團(tuán)員=1and學(xué)生信息.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)and期末成績(jī)>80ELSEINSERT@info/*返回?cái)?shù)據(jù)*/SELECT學(xué)生信息.學(xué)號(hào),姓名,期末成績(jī)

FROM學(xué)生信息,學(xué)生成績(jī)

WHERE是否團(tuán)員=0and學(xué)生信息.學(xué)號(hào)=學(xué)生成績(jī).學(xué)號(hào)RETURNENDGO第六十五頁(yè)，共七十頁(yè)，2022年，8月28日11.8本章總結(jié)

SQLServer支持結(jié)構(gòu)化的編程方法，可以進(jìn)行邏輯編程，處理各種結(jié)構(gòu)的編程，從而完成較為復(fù)雜的作業(yè)。在本章中主要講述了批處理、事務(wù)、變量等概念，目的是讓大家在進(jìn)行程序設(shè)計(jì)時(shí)使用這些SQLServer本身特有的方法以達(dá)到進(jìn)行復(fù)雜程序設(shè)計(jì)的目的。另外還介紹了5種程序控制語(yǔ)句，游標(biāo)的使用方法以及用戶自定義函數(shù)。大家應(yīng)該對(duì)SQLServer的編程邏輯有清楚的認(rèn)識(shí)，能夠勝任復(fù)雜的數(shù)據(jù)查詢和修改工作。第六十六頁(yè)，共七十頁(yè)，2022年，8月28日11.9思考與實(shí)訓(xùn)練習(xí)

思考題1．什么叫批處理，什么叫事務(wù)，它們之間的關(guān)系是什么？2．執(zhí)行一個(gè)批處理時(shí)，如果批處理內(nèi)有一個(gè)語(yǔ)句錯(cuò)誤，那么批內(nèi)的其他語(yǔ)句是否還執(zhí)行？3．執(zhí)行一個(gè)事務(wù)時(shí)，如果其內(nèi)部發(fā)生錯(cuò)誤，但又沒(méi)與設(shè)置回滾，是否這些錯(cuò)誤會(huì)被提交？4．SQLServe