第八章-存儲(chǔ)過(guò)程-數(shù)據(jù)庫(kù)原理及應(yīng)用授課課件

AnIntroductiontoDatabaseSystem內(nèi)蒙古農(nóng)業(yè)大學(xué)計(jì)算機(jī)信息工程學(xué)院數(shù)據(jù)庫(kù)系統(tǒng)概論AnIntroductiontoDatabaseSystem第八章存儲(chǔ)過(guò)程本章內(nèi)容8.1SQLServer編程結(jié)構(gòu)8.2存儲(chǔ)過(guò)程8.3觸發(fā)器8.1SQLServer編程結(jié)構(gòu)8.1.1注釋8.1.2批處理8.1.3事務(wù)8.1.4局部變量8.1.5全局變量8.1.6T-SQL控制語(yǔ)句8.1.7游標(biāo)8.1.1注釋在程序中添加注釋是個(gè)很好的習(xí)慣。什么是注釋?zhuān)阂恍┱f(shuō)明性的文字，對(duì)代碼的功能或?qū)崿F(xiàn)方式給出簡(jiǎn)要的提示或解釋。T-SQL中注釋兩種方式：/*

注釋語(yǔ)句*/--注釋語(yǔ)句8.1.2批處理批處理是包含一個(gè)或多個(gè)Transact-SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行。SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱(chēng)為執(zhí)行計(jì)劃。GO指令用信號(hào)通知SQLServer實(shí)用工具一批T-SQL語(yǔ)句的結(jié)束。GO不是Transact-SQL語(yǔ)句；而是可為osql及SQLServer查詢分析器識(shí)別的命令。8.1.3事務(wù)什么是事務(wù)？事務(wù)是由一系列語(yǔ)句（選擇、插入、更新或刪除）構(gòu)成的邏輯工作單元。如果在事務(wù)執(zhí)行過(guò)程中沒(méi)有遇到錯(cuò)誤，則事務(wù)中的所有修改成為數(shù)據(jù)庫(kù)的永久部分。如果遇到錯(cuò)誤，則不對(duì)數(shù)據(jù)庫(kù)做任何修改。為什么使用事務(wù)？維護(hù)數(shù)據(jù)庫(kù)的完整性和一致性。事務(wù)的執(zhí)行步驟1.事務(wù)啟動(dòng)前，數(shù)據(jù)庫(kù)處于一致?tīng)顟B(tài)。2.應(yīng)用程序發(fā)出啟動(dòng)事務(wù)的信號(hào)。這可以通過(guò)BEGINTRANSACTION語(yǔ)句顯式完成?；蛘撸瑧?yīng)用程序也可以設(shè)置在隱性事務(wù)模式下運(yùn)行的選項(xiàng)；使新事務(wù)由前面的事務(wù)完成后所執(zhí)行的第一個(gè)Transact-SQL語(yǔ)句自動(dòng)啟動(dòng)。此時(shí)不向日志寫(xiě)入記錄；當(dāng)應(yīng)用程序?yàn)閿?shù)據(jù)修改生成第一個(gè)日志記錄時(shí)，才向日志寫(xiě)入第一個(gè)記錄。事務(wù)的執(zhí)行步驟3.應(yīng)用程序開(kāi)始修改數(shù)據(jù)。一次只修改一個(gè)表中的數(shù)據(jù)。隨著一系列的修改，數(shù)據(jù)庫(kù)可能會(huì)處于暫時(shí)不一致的中間狀態(tài)。4.當(dāng)應(yīng)用程序執(zhí)行到全部修改均已成功完成且數(shù)據(jù)庫(kù)再次處于一致?tīng)顟B(tài)時(shí)，應(yīng)用程序提交事務(wù)。這使全部修改成為數(shù)據(jù)庫(kù)的永久部分。事務(wù)的執(zhí)行步驟5.如果應(yīng)用程序遇到一些防礙事務(wù)完成的錯(cuò)誤，則撤消或回滾所有數(shù)據(jù)修改。這將使數(shù)據(jù)庫(kù)返回到事務(wù)啟動(dòng)前所處于的一致?tīng)顟B(tài)。在SQLServer中使用事務(wù)有三種模式：自動(dòng)提交事務(wù)（默認(rèn)模式）顯式事務(wù)隱性事務(wù)自動(dòng)提交事務(wù)自動(dòng)提交模式，是SQLServer的默認(rèn)模式。每個(gè)Transact-SQL語(yǔ)句在完成時(shí)，都被提交或回滾；如果一個(gè)語(yǔ)句成功地完成，則提交該語(yǔ)句；如果遇到錯(cuò)誤，則回滾該語(yǔ)句。顯式事務(wù)（一）顯式事務(wù)可以顯式地在其中定義事務(wù)的啟動(dòng)和結(jié)束。BEGINTRANSACTION標(biāo)志顯式事務(wù)的起始點(diǎn)。COMMITTRANSACTION

如果沒(méi)有遇到錯(cuò)誤，可使用該語(yǔ)句成功地結(jié)束事務(wù)。該事務(wù)中的所有數(shù)據(jù)修改在數(shù)據(jù)庫(kù)中都將永久有效。事務(wù)占用的資源將被釋放。ROLLBACKTRANSACTION用來(lái)清除遇到錯(cuò)誤的事務(wù)。該事務(wù)修改的所有數(shù)據(jù)都返回到事務(wù)開(kāi)始時(shí)的狀態(tài)。事務(wù)占用的資源將被釋放。顯式事務(wù)（二）開(kāi)始一個(gè)事務(wù)語(yǔ)法：BEGINTRAN[SACTION][transaction_name||@tran_name_variable]舉例：BEGINTRANT1UPDATEtable1...INSERTINTOtable2…COMMITTRANT1顯式事務(wù)（三）提交事務(wù)語(yǔ)法：COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]回退事務(wù)語(yǔ)法：ROLLBACK[TRAN[SACTION][transaction_name|@tran_name_variable]隱性事務(wù)當(dāng)連接以隱性事務(wù)模式進(jìn)行操作時(shí)，SQLServer將在提交或回滾當(dāng)前事務(wù)后自動(dòng)啟動(dòng)新事務(wù)。無(wú)須描述事務(wù)的開(kāi)始，只需提交或回滾每個(gè)事務(wù)。課堂演示（批）USEpubsGOCREATETABLETest1(aINTPRIMARYKEY,bCHAR(3))GOINSERTINTOTest1VALUES(1,'aaa')INSERTINTOTest1VALUES(2,'bbb')INSERTINTOTest1VALUSE(3,'ccc')/*Syntaxerror*/GO/*語(yǔ)法錯(cuò)誤，整個(gè)批都不會(huì)被執(zhí)行*/SELECT*FROMTest1/*Returnsnorows*/GO課堂演示（顯式事務(wù)）USEpubsGOCREATETABLETest1(aINTPRIMARYKEY,bCHAR(3))GOBegintranINSERTINTOTest1VALUES(1,'aaa')INSERTINTOTest1VALUES(2,'bbb')GoCommitGO課堂演示（隱性事務(wù)）首先要更改查詢分析器的設(shè)置：工具-〉選項(xiàng)-〉連接屬性設(shè)置implicit_transactions打上勾，確定。插入數(shù)據(jù)INSERTINTOTest1VALUES(1,'aaa')INSERTINTOTest1VALUES(2,'bbb')Go回退（rollback）或提交(commit)課堂練習(xí)開(kāi)始一個(gè)顯示事務(wù)更新titles表，使得所有的書(shū)籍的價(jià)格增加10%，然后回退該事務(wù)，看titles表有沒(méi)有變化，再次更新titles表中的價(jià)格為原來(lái)的1.1倍，這次提交事務(wù)，看表中的結(jié)果有沒(méi)有變化。8.1.4局部變量局部變量是程序的基本組成部分。在T-SQL中怎么使用局部變量？聲明局部變量使用局部變量聲明局部變量使用Declare語(yǔ)句聲明局部變量語(yǔ)法：Declare@variable_name<datatype>[,…n]使用局部變量給局部變量賦值使用Select語(yǔ)句使用Set語(yǔ)句語(yǔ)法：Select@variablel_name=expression[Fromtable_name[,…n]Whereclause]Set@variable_name=expression[,…n]局部變量舉例（一）--聲明一個(gè)變量Declare@authorvarchar(20)--賦值Select@author=au_idFromauthorsWherestate=‘MI’--注意：這里的賦值一定要保證select語(yǔ)句返回的auL_id必須只有一條數(shù)據(jù)。局部變量舉例（二）--聲明變量Declare@myvarvarchar(20)@rowsint--使用set賦值Set@myvar=‘thisisatest’Set@rows=(selectcount(*)fromauthors)查看變量的值Select@variable_name例如：Declare@myvarvarchar(20),@rowsintSet@myvar='thisisatest'Set@rows=(selectcount(*)fromauthors)--查看兩個(gè)變量的值Select@rowsasrows,@myvarasmyvar8.1.5全局變量SQLServer使用全局變量記錄SQLServer服務(wù)器活動(dòng)狀態(tài)的一組數(shù)據(jù)。介紹一些常用的全局變量@@Error返回最后執(zhí)行的TSQL語(yǔ)句的錯(cuò)誤代碼@@fetch_status@@cursor_rows@@rowcount課堂練習(xí)聲明一個(gè)變量@name，給這個(gè)變量賦值為pubs數(shù)據(jù)庫(kù)中authors表里au_id為‘722-51-5454’的作家姓名（au_fname+au_lname），然后查看這個(gè)變量的值是否正確。8.1.6T-SQL控制語(yǔ)句Begin…End語(yǔ)句塊If…Else條件判斷結(jié)構(gòu)While循環(huán)Case多重判斷結(jié)構(gòu)Return語(yǔ)句Begin…End語(yǔ)句塊Begin…End可以將一組SQL語(yǔ)句封裝成一個(gè)語(yǔ)句塊（出于編程的需要）。任何時(shí)候當(dāng)控制流語(yǔ)句必須執(zhí)行一個(gè)包含兩條或兩條以上T-SQL語(yǔ)句的語(yǔ)句塊時(shí)，請(qǐng)使用BEGIN和END語(yǔ)句。BEGIN和END語(yǔ)句必須成對(duì)使用：任何一條語(yǔ)句均不能單獨(dú)使用。Begin…End語(yǔ)句塊舉例（一）例如，當(dāng)IF語(yǔ)句僅控制一條Transact-SQL語(yǔ)句的執(zhí)行時(shí)，不需要使用BEGIN和END語(yǔ)句：IF(@@ERROR<>0)SET@ErrorSaveVariable=@@ERROR如果@@ERROR為0，則僅跳過(guò)SET語(yǔ)句。Begin…End語(yǔ)句塊舉例（二）用BEGIN和END語(yǔ)句在遇到錯(cuò)誤時(shí)做更多的事情：SET@ErrorSaveVariable=@@ERRORIF(@ErrorSaveVariable<>0)BEGINROLLBACKPRINT'Errorencountered,'+CAST(@ErrorSaveVariableASVARCHAR(10))ENDBegin…End語(yǔ)句塊舉例（三）提示說(shuō)明cast系統(tǒng)函數(shù)作用：將某種數(shù)據(jù)類(lèi)型的表達(dá)式顯式轉(zhuǎn)換為另一種數(shù)據(jù)類(lèi)型。語(yǔ)法：CAST(expressionASdata_type)使用演示腳本演示一段程序IF…ELSE條件判斷結(jié)構(gòu)（一）在執(zhí)行T-SQL語(yǔ)句時(shí)強(qiáng)加條件。如果條件滿足（布爾表達(dá)式返回TRUE時(shí)），則執(zhí)行IF關(guān)鍵字后的T-SQL語(yǔ)句；當(dāng)不滿足IF條件時(shí)（布爾表達(dá)式返回FALSE），就執(zhí)行ELSE關(guān)鍵字后的T-SQL語(yǔ)句。IF…ELSE條件判斷結(jié)構(gòu)（二）語(yǔ)法：IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]IF…ELSE條件判斷演示如果平均價(jià)格高于15美元就顯示一行語(yǔ)句，如果低于15美元，顯示另一行語(yǔ)句。IF(SELECTAVG(price)FROMtitlesWHEREtype='busniess')<$15PRINT'Averagetitlepriceislessthan$15.'ELSEPRINT'Morethan$15.'While循環(huán)（一）設(shè)置重復(fù)執(zhí)行SQL語(yǔ)句或語(yǔ)句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行WHILE后面的T-SQL語(yǔ)句?？梢允褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語(yǔ)句的執(zhí)行。While循環(huán)（二）語(yǔ)法：WHILEBoolean_expression{sql_statement|statement_block}[BREAK]{sql_statement|statement_block}[CONTINUE]Case多重判斷結(jié)構(gòu)（一）計(jì)算條件列表并返回多個(gè)可能結(jié)果表達(dá)式之一。CASE具有兩種格式：簡(jiǎn)單CASE函數(shù)將某個(gè)表達(dá)式與一組簡(jiǎn)單表達(dá)式進(jìn)行比較以確定結(jié)果。CASE搜索函數(shù)計(jì)算一組布爾表達(dá)式以確定結(jié)果。Case多重判斷結(jié)構(gòu)（二）簡(jiǎn)單CASE函數(shù)：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expression]ENDCase多重判斷結(jié)構(gòu)（三）CASE搜索函數(shù)：CASEWHENBoolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]ENDCase多重判斷結(jié)構(gòu)舉例使用演示腳本演示一段程序Return語(yǔ)句從查詢或過(guò)程中無(wú)條件退出。RETURN即時(shí)且完全，可在任何時(shí)候用于從過(guò)程、批處理或語(yǔ)句塊中退出，不執(zhí)行位于RETURN之后的語(yǔ)句。語(yǔ)法：RETURN[integer_expression]除非特別指明，所有系統(tǒng)存儲(chǔ)過(guò)程返回0值表示成功，返回非零值則表示失敗。課堂練習(xí)（一）3.查詢pubs數(shù)據(jù)庫(kù)的employee表，如果表中雇員的平均服務(wù)時(shí)間長(zhǎng)于10年，則打印信息：‘我們的雇員都很忠誠(chéng):)’，否則打印信息：‘我們的雇員經(jīng)常跳槽:(’。提示：使用datediff和getdate系統(tǒng)函數(shù)用法：DATEDIFF(datepart,startdate,enddate)例如：datediff(year,hire_date,getdate())課堂練習(xí)（二）4.查詢pubs數(shù)據(jù)庫(kù)中employee表，顯示相關(guān)雇員信息(id，姓名，服務(wù)時(shí)間等)，其中增加一個(gè)‘雇員類(lèi)型’列：如果雇傭時(shí)間不長(zhǎng)于12年，則顯示他為‘新雇員’，否則顯示他為‘老雇員’。8.1.7游標(biāo)SQLServer2000支持的三種游標(biāo)實(shí)現(xiàn)Transact-SQL游標(biāo)聲明游標(biāo)打開(kāi)游標(biāo)提取數(shù)據(jù)關(guān)閉游標(biāo)SQLServer2000支持的三種游標(biāo)實(shí)現(xiàn)Transact-SQL游標(biāo)（本章介紹內(nèi)容）應(yīng)用編程接口（API）服務(wù)器游標(biāo)客戶端游標(biāo)注意：由于Transact-SQL游標(biāo)和API服務(wù)器游標(biāo)都在服務(wù)器端實(shí)現(xiàn)，它們一起被稱(chēng)為服務(wù)器游標(biāo)。Transact-SQL游標(biāo)概述基于DECLARECURSOR語(yǔ)法，主要用在Transact-SQL腳本、存儲(chǔ)過(guò)程和觸發(fā)器中。Transac-SQL游標(biāo)在服務(wù)器上實(shí)現(xiàn)并由從客戶端發(fā)送到服務(wù)器的Transact-SQL語(yǔ)句管理。API服務(wù)器游標(biāo)概述支持OLEDB、ODBC和DB-Library中的API游標(biāo)函數(shù)。API服務(wù)器游標(biāo)在服務(wù)器上實(shí)現(xiàn)。每次客戶應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時(shí)，SQLServerOLEDB提供程序、ODBC驅(qū)動(dòng)程序或DB-Library動(dòng)態(tài)鏈接庫(kù)（DLL）就把請(qǐng)求傳送到服務(wù)器，以便對(duì)API服務(wù)器游標(biāo)進(jìn)行操作。客戶端游標(biāo)概述由SQLServerODBC驅(qū)動(dòng)程序、DB-LibraryDLL和實(shí)現(xiàn)ADOAPI的DLL在內(nèi)部實(shí)現(xiàn)?？蛻舳擞螛?biāo)通過(guò)在客戶端高速緩存所有結(jié)果集行來(lái)實(shí)現(xiàn)。每次客戶應(yīng)用程序調(diào)用API游標(biāo)函數(shù)時(shí)，SQLServerODBC驅(qū)動(dòng)程序、DB-LibraryDLL或ADODLL就對(duì)高速緩存在客戶端中的結(jié)果集行執(zhí)行游標(biāo)操作。Transact-SQL游標(biāo)Transact-SQL游標(biāo)主要用在存儲(chǔ)過(guò)程、觸發(fā)器和Transact-SQL腳本中，它們使結(jié)果集的內(nèi)容對(duì)其它Transact-SQL語(yǔ)句同樣可用。使用游標(biāo)有四種基本的步驟:聲明游標(biāo)、打開(kāi)游標(biāo)、提取數(shù)據(jù)、關(guān)閉游標(biāo)。

聲明游標(biāo)象使用其它類(lèi)型的變量一樣，使用一個(gè)游標(biāo)之前，首先應(yīng)當(dāng)聲明它。使用DECLARECURSOR語(yǔ)句把Transact-SQL游標(biāo)與一個(gè)SELECT語(yǔ)句相關(guān)聯(lián)。DECLARECURSOR語(yǔ)句同時(shí)定義游標(biāo)的特征，比如游標(biāo)名稱(chēng)以及游標(biāo)是否為只讀或只進(jìn)特性。聲明游標(biāo)語(yǔ)法游標(biāo)的聲明包括兩個(gè)部分:游標(biāo)的名稱(chēng)這個(gè)游標(biāo)所用到的SQL語(yǔ)句SQL-92語(yǔ)法DECLAREcursor_name

[INSENSITIVE]

[SCROLL]

CURSORFORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]聲明游標(biāo)的注意點(diǎn)（一）關(guān)于INSENSITIVE(英文意思：反應(yīng)遲鈍的)：使用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)行。換句話說(shuō)：所有對(duì)基本表的變動(dòng)都不會(huì)在用這個(gè)游標(biāo)進(jìn)行的操作中體現(xiàn)出來(lái)。聲明游標(biāo)時(shí)如不指定insensitive，（任何用戶）對(duì)基表提交的刪除和更新都反映在后面的提取中。聲明游標(biāo)的注意點(diǎn)（二）關(guān)于SCROLL：使用scroll關(guān)鍵字定義的游標(biāo)，具有包括如下所示的功能：FIRST,LAST,PRIOR,NEXT,RELATIVE,ABSOLUTE。如聲明游標(biāo)時(shí)沒(méi)有指定SCROLL關(guān)鍵字，那么聲明的游標(biāo)只有默認(rèn)的NEXT功能。聲明游標(biāo)舉例聲明一個(gè)包含authors表所有信息的游標(biāo)DECLAREauthors_cursor1CURSORFORSELECT*FROMauthors聲明一個(gè)有條件限制的游標(biāo)DECLAREauthors_cursor2CURSORFORSELECTau_id,au_fname,au_lnameFROMauthorsWHEREstate="UT"ORDERBYau_id打開(kāi)游標(biāo)使用OPEN語(yǔ)句執(zhí)行SELECT語(yǔ)句并生成游標(biāo)。由于打開(kāi)游標(biāo)是對(duì)數(shù)據(jù)庫(kù)進(jìn)行一些SQLSELECT的操作,它將耗費(fèi)一段時(shí)間,主要取決于您使用的系統(tǒng)性能和這條語(yǔ)句的復(fù)雜程度。語(yǔ)法：OPEN{cursor_name|cursor_variable_name}打開(kāi)游標(biāo)舉例對(duì)于上面的示例一DECLAREauthors_cursor1CURSORFORSELECT*FROMauthorsOPENauthors_cursor1對(duì)于上面的示例二OPENauthors_cursor2提取數(shù)據(jù)當(dāng)用OPEN語(yǔ)句打開(kāi)了游標(biāo)并在數(shù)據(jù)庫(kù)中執(zhí)行了查詢后,您不能立即利用在查詢結(jié)果集中的數(shù)據(jù)。您必須用FETCH語(yǔ)句來(lái)取得數(shù)據(jù)。一條FETCH語(yǔ)句一次可以將一條記錄放入程序員指定的變量中。事實(shí)上,FETCH語(yǔ)句是游標(biāo)使用的核心。FETCH語(yǔ)法FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]FETCH舉例使用演示腳本演示兩段FETCH的例子說(shuō)明幾點(diǎn)：@@fetch_status：返回被FETCH語(yǔ)句執(zhí)行的最后游標(biāo)的狀態(tài)。@@cursor_rows：返回連接上最后打開(kāi)的游標(biāo)中當(dāng)前存在的合格行的數(shù)量。關(guān)閉游標(biāo)結(jié)束游標(biāo)時(shí)，使用CLOSE語(yǔ)句關(guān)閉游標(biāo)。關(guān)閉游標(biāo)可以釋放某些資源，但是如果重新發(fā)出一個(gè)OPEN語(yǔ)句，則該游標(biāo)結(jié)構(gòu)仍可用于處理。DEALLOCATE語(yǔ)句則完全釋放分配給游標(biāo)的資源，包括游標(biāo)名稱(chēng)。一般游標(biāo)用完以后，使用close語(yǔ)句關(guān)閉游標(biāo)，接著使用deallocate語(yǔ)句釋放游標(biāo)。課堂練習(xí)5.創(chuàng)建一個(gè)游標(biāo)，它所包含的結(jié)果集是pubs數(shù)據(jù)庫(kù)中titles表中的所有書(shū)名（title）。把所有的書(shū)名以下面的格式打印出來(lái)：Title:TheBusyExecutive'sDatabaseGuide8.2存儲(chǔ)過(guò)程8.2.1什么是存儲(chǔ)過(guò)程8.2.2為什么使用存儲(chǔ)過(guò)程8.2.3創(chuàng)建存儲(chǔ)過(guò)程8.2.4使用存儲(chǔ)過(guò)程8.2.1存儲(chǔ)過(guò)程引言在使用SQLServer2000創(chuàng)建應(yīng)用程序時(shí)，Transact-SQL編程語(yǔ)言是應(yīng)用程序和SQLServer數(shù)據(jù)庫(kù)之間的主要編程接口。使用Transact-SQL程序時(shí)，可用兩種方法存儲(chǔ)和執(zhí)行程序。可以在本地存儲(chǔ)程序，并創(chuàng)建向SQLServer發(fā)送命令并處理結(jié)果的應(yīng)用程序；也可以將程序在SQLServer中存儲(chǔ)為存儲(chǔ)過(guò)程，并創(chuàng)建執(zhí)行存儲(chǔ)過(guò)程并處理結(jié)果的應(yīng)用程序。8.2.2什么是存儲(chǔ)過(guò)程Transact-SQL語(yǔ)句的預(yù)編譯集合，這些語(yǔ)句在一個(gè)名稱(chēng)下存儲(chǔ)并作為一個(gè)單元進(jìn)行處理。換句話說(shuō)：在一個(gè)存儲(chǔ)過(guò)程內(nèi)，可以設(shè)計(jì)、編碼和測(cè)試執(zhí)行某個(gè)常用任務(wù)所需的SQL語(yǔ)句和邏輯。之后，每個(gè)需要執(zhí)行該任務(wù)的應(yīng)用程序只須執(zhí)行此存儲(chǔ)過(guò)程即可。（代碼重用）為什么使用存儲(chǔ)過(guò)程（一）1.允許模塊化程序設(shè)計(jì)只需創(chuàng)建過(guò)程一次并將其存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可在程序中調(diào)用該過(guò)程任意次。存儲(chǔ)過(guò)程可由在數(shù)據(jù)庫(kù)編程方面有專(zhuān)長(zhǎng)的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。銀行每個(gè)月底做結(jié)算的例子為什么使用存儲(chǔ)過(guò)程（二）2.

對(duì)于需要重復(fù)執(zhí)行的代碼，執(zhí)行效率更高

如果某操作需要大量Transact-SQL代碼或需重復(fù)執(zhí)行，存儲(chǔ)過(guò)程將比Transact-SQL批代碼的執(zhí)行要快。系統(tǒng)會(huì)在創(chuàng)建存儲(chǔ)過(guò)程時(shí)對(duì)其進(jìn)行分析和優(yōu)化，并可在首次執(zhí)行該過(guò)程后使用該過(guò)程的內(nèi)存中版本。如果不采用存儲(chǔ)過(guò)程，每次運(yùn)行那些需要重復(fù)執(zhí)行的Transact-SQL語(yǔ)句時(shí)，都需要把這些語(yǔ)句從客戶端發(fā)送到服務(wù)器端，并且在SQLServer每次執(zhí)行這些語(yǔ)句時(shí)，都要對(duì)其進(jìn)行編譯和優(yōu)化——顯然效率會(huì)很低。為什么使用存儲(chǔ)過(guò)程（三）3.減少網(wǎng)絡(luò)流量一個(gè)需要數(shù)百行Transact-SQL代碼的操作由一條執(zhí)行過(guò)程代碼的單獨(dú)語(yǔ)句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼。4.可作為安全機(jī)制使用

即使對(duì)于沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可授予他們執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。存儲(chǔ)過(guò)程的處理流程對(duì)象信息進(jìn)入sysobjects

和syscomments系統(tǒng)表

編譯過(guò)的執(zhí)行計(jì)劃存放在緩沖區(qū)里面編譯優(yōu)化創(chuàng)建執(zhí)行(第一次或編譯)

語(yǔ)法分析

8.2.3創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)法CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]創(chuàng)建存儲(chǔ)過(guò)程舉例（一）從authors表中返回所有作者的姓名、電話、地址和所在的州名。CREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,phone,address,stateFROMauthorsGO創(chuàng)建存儲(chǔ)過(guò)程舉例（二）指定參數(shù)，返回指定州的作家的姓名、電話、地址。CREATEPROCEDUREau_info@statechar(2)ASSELECTau_lname,au_fname,phone,addressFROMauthorsWHEREstate=@stateGO創(chuàng)建存儲(chǔ)過(guò)程舉例（三）具有默認(rèn)值的存儲(chǔ)過(guò)程。CREATEPROCEDUREau_info@statechar(2)=‘CA‘

--默認(rèn)值=’CA’ASIF@stateNOTLIKE‘[A-Z][A-Z]’

--增加一段檢查代碼

BEGINprint'輸入?yún)?shù)錯(cuò)誤'returnENDSELECTau_lname,au_fname,phone,addressFROMauthorsWHEREstate=@stateGO8.2.4使用存儲(chǔ)過(guò)程（一）對(duì)于例1我們可以以下面的方式執(zhí)行：EXECUTEau_info_allEXECau_info_all如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info_all使用存儲(chǔ)過(guò)程（二）對(duì)于例2（有參數(shù)的存儲(chǔ)過(guò)程）我們可以以下面的方式執(zhí)行：EXECUTEau_info'CA'EXECUTEau_info@state='CA'EXECau_info'CA'EXECau_info@state='CA'如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info'CA'au_info@state='CA'使用存儲(chǔ)過(guò)程（三）對(duì)于例3（有默認(rèn)值的存儲(chǔ)過(guò)程）我們可以以下面的方式執(zhí)行：同例2，指定參數(shù)不指定參數(shù)，使用默認(rèn)參數(shù)：EXECUTEau_infoEXECau_info如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：au_info使用Output參數(shù)返回結(jié)果CREATEPROCEDUREMathTutor@m1smallint,@m2smallint,@resultsmallintOUTPUTASSET@result=@m1*@m2GODECLARE@answersmallintEXECUTEMathTutor5,6,@answerOUTPUTSELECT'Theresultis:',@answerTheresultis:30結(jié)果執(zhí)行存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程課堂練習(xí)6.創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，顯示所有價(jià)格在15美元以下的書(shū)的書(shū)名，類(lèi)型，價(jià)格。7.把價(jià)格作為參數(shù)，創(chuàng)建一個(gè)能顯示在某兩個(gè)指定價(jià)格之間的書(shū)的書(shū)名，類(lèi)型，價(jià)格。8.使用OUTPUT參數(shù)，創(chuàng)建一個(gè)計(jì)算圓柱體體積的存儲(chǔ)過(guò)程。并執(zhí)行它。8.3觸發(fā)器8.3.1觸發(fā)器概述8.3.2觸發(fā)器的功能8.3.3創(chuàng)建觸發(fā)器8.3.4Inserted和deleted表8.3.5建立列級(jí)觸發(fā)器8.3.6使用INSTEADOF觸發(fā)器8.3.7使用觸發(fā)器的注意點(diǎn)8.3.1觸發(fā)器概述觸發(fā)器是一類(lèi)特殊的存儲(chǔ)過(guò)程，被定義為在對(duì)表或視圖發(fā)出UPDATE、INSERT或DELETE語(yǔ)句時(shí)自動(dòng)執(zhí)行。觸發(fā)器是與表緊密相連，是與某個(gè)表關(guān)聯(lián)的。也就是說(shuō)，如果在這個(gè)表上發(fā)生了UPDATE、INSERT或DELETE操作，就會(huì)觸發(fā)相應(yīng)的觸發(fā)器進(jìn)行工作。8.3.2觸發(fā)器分類(lèi)AFTER觸發(fā)器觸發(fā)器在觸發(fā)它們的語(yǔ)句完成后執(zhí)行。如果該語(yǔ)句因錯(cuò)誤（如違反約束或語(yǔ)法錯(cuò)誤）而失敗，觸發(fā)器將不會(huì)執(zhí)行。不能為視圖指定AFTER觸發(fā)器。在SQLServer2000中AFTER是默認(rèn)觸發(fā)器。INSTEADOF觸發(fā)器該觸發(fā)器代替觸發(fā)操作執(zhí)行?？稍诒砗鸵晥D上指定INSTEADOF觸發(fā)器。只能為每個(gè)觸發(fā)操作（INSERT、UPDATE和DELETE）定義一個(gè)INSTEADOF觸發(fā)器。8.3.3觸發(fā)器的功能觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改；不過(guò)，通過(guò)級(jí)聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改；執(zhí)行比check約束更復(fù)雜的商業(yè)邏輯；查找在數(shù)據(jù)修改前后，表狀態(tài)之間的差別，并根據(jù)差別采取相應(yīng)的措施；一個(gè)表中的多個(gè)同類(lèi)觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個(gè)不同的對(duì)策以響應(yīng)同一個(gè)修改語(yǔ)句。8.3.4創(chuàng)建觸發(fā)器語(yǔ)法CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{FOR|AFTER|INSTEADOF}{[INSERT,][DELETE,][UPDATE]}ASsql_statement}創(chuàng)建觸發(fā)器舉例（一）CREATETRIGGERreminderONauthorsFORUPDATEASdeclare@rowcountvarchar(10)set@rowcount=cast(@@rowcountasvarchar(10))print@rowcount+'dataupdated!'GOupdateauthorssetau_lname='haijian'whereau_fname='zhang'8.3.5Inserted和deleted表觸發(fā)器語(yǔ)句中使用了兩種特殊的表：deleted表和inserted表。這兩張表存儲(chǔ)在高速緩存中，SQLServer自動(dòng)創(chuàng)建和管理這兩個(gè)表。這兩個(gè)表的結(jié)構(gòu)和與當(dāng)前觸發(fā)器所關(guān)聯(lián)的表的結(jié)構(gòu)一樣?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件；只有在觸發(fā)器中才可以使用這兩個(gè)表，且不能直接對(duì)表中的數(shù)據(jù)進(jìn)行更改。創(chuàng)建觸發(fā)器舉例（二）CREATETRIGGERreminderONauthorsFORUPDATEASdeclare@old_lnamevarchar(20),@new_lnamevarchar(20)select@old_lname=au_lnamefromdeletedselect@new_lname=au_lnamefrominsertedprint'oldlnameis:'+@old_lname+'，newlnameis:'+@new_lnameselect*fromdeletedselect*frominsertedHowanINSERTTriggerWorksINSERTstatementtoatablewithanINSERTTriggerDefinedINSERT[OrderDetails]VALUES(10525,2,19.00,5,0.2)OrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7

924Discount0.2

0.150.0519.0020.210523Insertstatementloggedinserted10523219.0050.2TRIGGERActionsExecuteOrderDetailsOrderID105221052310524ProductID10417UnitPrice31.009.6530.00Quantity7

924Discount0.2

0.150.0519.0020.210523TriggerCode:USENorthwindCREATETRIGGEROrdDet_InsertON[OrderDetails]FORINSERTASUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDUPDATEPSETUnitsInStock=(P.UnitsInStock–I.Quantity)FROMProductsASPINNERJOINInsertedASIONP.ProductID=I.ProductIDProductsProductIDUnitsInStock……12341510

65

20215INSERTStatementtoaTablewithanINSERTTriggerDefinedINSERTStatementLoggedTriggerActionsExecuted123HowaDELETETriggerWorksDELETEStatementtoatablewithaDELETETriggerDefinedDELETEStatementtoatablewithaDELETETriggerDefinedDeleted4DairyProductsCheeses0x15…DELETEstatementloggedCategoriesCategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoftdrinks,coffees…Sweetandsavory…Desserts,candies,…Picture0x15…

0x15…0x15…0x15…CheesesDairyProducts4DELETECategoriesWHERECategoryID=4USENorthwindCREATETRIGGERCategory_Delete ONCategories FORDELETEAS UPDATEPSETDiscontinued=1 FROMProductsASPINNERJOINdeletedASd ONP.CategoryID=d.CategoryIDProductsProductIDDiscontinued……123400

0

0TriggerActionsExecute21UPDATEPSETDiscontinued=1 FROMProductsASPINNERJOINdeletedASd ONP.CategoryID=d.CategoryIDDELETEStatementtoaTablewithaDELETEStatementDefinedDELETEStatementLoggedTriggerActionsExecuted123HowanUPDATETriggerWorksUPDATEStatementtoatablewithanUPDATETriggerDefinedUPDATEEmployeesSETEmployeeID=17WHEREEmployeeID=2UPDATEStatementloggedasINSERTandDELETEStatementsEmployeesEmployeeIDLastNameFirstNameTitleHireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSalesRep.RSalesRep.SalesRep.~~~~~~~~~~~~2FullerAndrewVicePres.~~~inserted17FullerAndrewVicePres.~~~deleted2FullerAndrewVicePres.~~~TRIGGERActionsExecuteUSENorthwindGOCREATETRIGGEREmployee_Update ONEmployees FORUPDATEASIFUPDATE(EmployeeID)BEGINTRANSACTION RAISERROR('Transactioncannotbeprocessed.\ *****EmployeeIDnumbercannotbemodified.',10,1) ROLLBACKTRANSACTIONASIFUPDATE(EmployeeID)BEGINTRANSACTION RAISERROR('Transactioncannotbeprocessed.\ *****EmployeeIDnumbercannotbemodified.',10,1) ROLLBACKTRANSACTIONTransactioncannotbeprocessed.*****MembernumbercannotbemodifiedEmployeesEmployeeIDLastNameFirstNameTitleHireDate1234DavolioBarrLeverlingPeacockNancyAndrewJanetMargaretSalesRep.RSalesRep.SalesRep.~~~~~~~~~~~~2FullerAndrewVicePres.~~~UPDATEStatementtoaTablewithanUPDATETriggerDefinedUPDATEStatementLoggedasINSERTandDELETEStatementsTriggerActionsExecuted123課堂練習(xí)1.A)建立price_change表，準(zhǔn)備用來(lái)存放書(shū)的價(jià)格變化信息，有以下幾列：title_id,type,old_price,new_price,change_date,operator。

B)建立一個(gè)更新觸發(fā)器，一旦titles表發(fā)生更新，立即把相關(guān)信息存放到price_change表中。createtableprice_change( title_idtidprimarykey, typechar(12)notnull, old_pricemoney, new_pricemoney, change_datedatetime, operatorchar(10))createtriggerp_change_triggerontitlesafterupdateasdeclare@old_pricemoney,@new_pricemoney,@title_idtid,@typechar(12)select@old_price=pricefromdeletedselect@new_price=pricefrominsertedselect@title_id=title_idfrominsertedselect@type=typefrominsertedif(@@rowcount!=0) insertintoprice_changevalues (@title_id,@type,@old_price, @new_price,getdate(),'update')go8.3.6建立列級(jí)觸發(fā)器列級(jí)觸發(fā)器對(duì)數(shù)據(jù)庫(kù)表的某些列進(jìn)行監(jiān)控，一旦這些列發(fā)生變動(dòng)，即觸發(fā)一系列操作。UPDATE(column_name)：判斷指定的列是否經(jīng)過(guò)了修改。課堂練習(xí)2.修改練習(xí)1，使得只有當(dāng)price列被更新時(shí)，才會(huì)觸發(fā)觸發(fā)器。8.3.6使用INSTEADOF觸發(fā)器INSTEADOF觸發(fā)器替代觸發(fā)語(yǔ)句的標(biāo)準(zhǔn)操作（INSERT、UPDATE或DELETE)。例如，可以定義INSTEADOF觸發(fā)器在一列或多列上執(zhí)行錯(cuò)誤或值的檢查，然后在插入記錄之前執(zhí)行其它操作；例如，當(dāng)工資表中小時(shí)工資列的更新值超過(guò)指定值時(shí)，可以定義觸發(fā)器或者產(chǎn)生錯(cuò)誤信息并回滾該事務(wù)，或者在審核日志中插入新記錄（在工資表中插入該記錄之前）。例如，INSTEADOF觸發(fā)器能夠提供邏輯以通過(guò)視圖修改多個(gè)基表，或者修改包含計(jì)算列的基表。INSTEADOF觸發(fā)器舉例創(chuàng)建一個(gè)所有居住在CA州的作家的視圖，包