《SQL Server 數(shù)據(jù)庫(kù)案例教程》課件第11章存儲(chǔ)過程

課程內(nèi)容回顧事務(wù)的概念事務(wù)的特性:原子性、一致性、隔離性和持久性(ACID)事務(wù)可以分為如下類型：顯式事務(wù)隱式事務(wù)自動(dòng)提交事務(wù)索引的好處和分類聚集索引和非聚集索引。視圖的好處本章目標(biāo)了解存儲(chǔ)過程的優(yōu)點(diǎn)掌握常用的系統(tǒng)存儲(chǔ)過程掌握如何創(chuàng)建存儲(chǔ)過程掌握如何調(diào)用存儲(chǔ)過程存儲(chǔ)過程介紹存儲(chǔ)和執(zhí)行T-SQL程序的方法有兩種:一種是在本地存儲(chǔ)程序，然后創(chuàng)建應(yīng)用程序來將命令發(fā)送到SQLServer并對(duì)結(jié)果進(jìn)行處理。另一種方法是將程序存儲(chǔ)為SQLServer中的存儲(chǔ)過程，然后創(chuàng)建應(yīng)用程序來執(zhí)行存儲(chǔ)過程并對(duì)結(jié)果進(jìn)行處理。存儲(chǔ)過程用戶定義的存儲(chǔ)過程:數(shù)據(jù)庫(kù)開發(fā)人員或管理員編寫的用來運(yùn)行經(jīng)常執(zhí)行的管理任務(wù)，或者應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則。包含數(shù)據(jù)操縱或數(shù)據(jù)檢索語句

系統(tǒng)存儲(chǔ)過程:SQLServer提供了一些預(yù)編譯的存儲(chǔ)過程，用以管理SQLServer和顯示有關(guān)數(shù)據(jù)庫(kù)和用戶的信息

存儲(chǔ)過程中的語句存儲(chǔ)過程----------------單個(gè)SELECT語句SELECT語句塊可以包含SELECT語句與邏輯控制語句存儲(chǔ)過程中的語句SQLServer中的存儲(chǔ)過程與其他語言中的過程或函數(shù)類似，它們的共同特征是:它們都接收輸入?yún)?shù)，并向調(diào)用過程或語句返回值。它們都包含在數(shù)據(jù)庫(kù)中執(zhí)行操作或調(diào)用其他存儲(chǔ)過程的編程語句。它們都向調(diào)用過程返回狀態(tài)值，指示執(zhí)行過程是否成功存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的優(yōu)點(diǎn):允許模塊化程序設(shè)計(jì)只需創(chuàng)建過程一次并將其存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可在程序中調(diào)用該過程任意次允許更快執(zhí)行存儲(chǔ)過程將比Transact-SQL批代碼的執(zhí)行要快減少網(wǎng)絡(luò)流量存儲(chǔ)過程存儲(chǔ)在后端數(shù)據(jù)庫(kù)中不需要通過網(wǎng)絡(luò)傳輸可作為安全機(jī)制使用即使對(duì)于沒有直接執(zhí)行存儲(chǔ)過程中語句權(quán)限的用戶，也可授予他執(zhí)行該存儲(chǔ)過程的權(quán)限常用的系統(tǒng)存儲(chǔ)過程SQLServer提供系統(tǒng)存儲(chǔ)過程，它們是一組預(yù)編譯的T-SQL語句所有系統(tǒng)存儲(chǔ)過程的名稱都以“_sp”開頭。系統(tǒng)存儲(chǔ)過程位于master數(shù)據(jù)庫(kù)中系統(tǒng)存儲(chǔ)過程

說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns返回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過程sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義的存儲(chǔ)過程、觸發(fā)器或視圖的實(shí)際文本常用的系統(tǒng)存儲(chǔ)過程的使用常用的擴(kuò)展存儲(chǔ)過程：xp_cmdshell它可以完成DOS命令下的一些操作，諸如創(chuàng)建文件夾、列出文件等語法:示例:xp_cmdshell擴(kuò)展存儲(chǔ)過程的使用EXECxp_cmdshellDOS命令[NO_OUTPUT]USEmasterGO--創(chuàng)建文件夾H:\prodexecxp_cmdshell'mkdirH:\prod',NO_OUTPUTexecxp_cmdshell'dirH:\prod\'--查看文件

常用的系統(tǒng)存儲(chǔ)過程的使用EXECsp_databases--不帶參數(shù)EXECsp_helpdb

--可帶參數(shù),也可不帶,所有數(shù)據(jù)庫(kù)中可用USEempDB--指定數(shù)據(jù)庫(kù)empDBGOEXECsp_helpemp--帶參數(shù),參數(shù)為表emp示例:其他系統(tǒng)存儲(chǔ)過程的使用一些系統(tǒng)存儲(chǔ)過程必須在特定的數(shù)據(jù)庫(kù)中使用,大多數(shù)在所有數(shù)據(jù)庫(kù)中可用創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程SSMS:可視化的方式T-SQL:代碼(重點(diǎn)介紹)

使用CREATEPROCEDURE語句創(chuàng)建存儲(chǔ)過程。所有的存儲(chǔ)過程都創(chuàng)建在當(dāng)前數(shù)據(jù)庫(kù)中語法:CREATEPROC[EDURE]存儲(chǔ)過程名

[{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT],.......,{@參數(shù)n數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT]]ASSQL語句其中,參數(shù)部分為可選

創(chuàng)建不帶參數(shù)的存儲(chǔ)過程問題：查看部門的平均工資以及各部門工資不超過2000的員工，并根據(jù)工資數(shù)額顯示加薪信息USEempDBGO/*--創(chuàng)建存儲(chǔ)過程---*/CREATEPROCEDUREproc_empASDECLARE@salAvgfloat,@salSumfloatSELECT@salAvg=AVG(sal),@salSum=sum(sal)FROMempprint'員工平均工資：'+convert(varchar(10),@salAvg)print'員工總工資：'+convert(varchar(10),@salSum)IF(@salAvg>2000)print'員工不需要加薪'ELSEprint'員工需要加薪'print'--------------------------------------------'print'部門需要加薪的員工:'SELECTdname,empno,ename,salFROMdeptINNERJOINempONdept.deptno=emp.deptnoWHEREsal<2000GO調(diào)用存儲(chǔ)過程語法:

調(diào)用示例存儲(chǔ)過程執(zhí)行結(jié)果如下:EXEC過程名[參數(shù)]EXECproc_emp創(chuàng)建帶參數(shù)的存儲(chǔ)過程CREATEPROC[EDURE]存儲(chǔ)過程名

[{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT],.......,{@參數(shù)n數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT]]ASSQL語句

回顧創(chuàng)建存儲(chǔ)過程的語法存儲(chǔ)過程中的參數(shù)可分為2種:輸入?yún)?shù):可以在調(diào)用時(shí)向存儲(chǔ)過程傳遞參數(shù),此參數(shù)可用來在存儲(chǔ)過程中傳入值輸出參數(shù):如果希望返回值，則可以使用輸出參數(shù)，輸出參數(shù)后有“OUTPUT”標(biāo)記，執(zhí)行存儲(chǔ)過程后，將把返回值存放在輸出參數(shù)中，可供其他T-SQL語句讀取訪問CREATEPROCEDURE語句中聲明一個(gè)或多個(gè)變量作為參數(shù)@參數(shù)數(shù)據(jù)類型[=默認(rèn)值]創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程示例-1emp表中，當(dāng)部門不同時(shí)，需要加薪的員工和部門的平均工資會(huì)有所不同問題：給定部門號(hào)，求該部門的平均工資

USEempDBGO/*--創(chuàng)建存儲(chǔ)過程--*/CREATEPROCEDUREpro_emp@dnointASdeclare@salavgfloatSELECT@salavg=AVG(sal)FROMempWHEREdeptno=@dnoprint'部門平均工資為:'+convert(varchar(10),@salavg)GO/*--調(diào)用存儲(chǔ)過程--*/--給定部門號(hào)10，求出該部門的平均工資EXECpro_emp10演示創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程示例-2

問題：對(duì)于不同部門，求出從事銷售工作的人數(shù)

USEempDBGO/*--創(chuàng)建參數(shù)帶默認(rèn)值的存儲(chǔ)過程--*/CREATEPROCEDUREproc_job@dnoint=20,@jbvarchar(20)='SALESMAN'ASdeclare@rsintprint'部門:'+convert(varchar(5),@dno)+'工作:'+@jbprint'------------------------------------------'SELECT@rs=COUNT(*)FROMempWHEREdeptno=@dnoandjob=@jbprint'人數(shù)：'+convert(varchar(10),@rs)GO/*--調(diào)用存儲(chǔ)過程--*/EXECproc_job--都采用默認(rèn)值，得到10部門，從事銷售工作的人數(shù)EXECproc_job30--部門號(hào)為30，工種采用默認(rèn)值EXECproc_job20,‘CLERK’--都不采用默認(rèn)值，部門號(hào)位0，工種為’CLERK’--錯(cuò)誤的調(diào)用方式：EXECproc_job,’CLERK’

–部門號(hào)為默認(rèn)值--正確的調(diào)用方式：EXECproc_job@jb=’CLERK’

–部門號(hào)為默認(rèn)值創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程如果希望調(diào)用存儲(chǔ)過程后，返回一個(gè)或多個(gè)值，就需要使用輸出(OUTPUT)參數(shù)了。為了使用輸出參數(shù)，必須在CREATEPRODECURE語句和EXECUTE語句中指定OUTPUT關(guān)鍵字。在執(zhí)行存儲(chǔ)過程時(shí)，如果忽略O(shè)UTPUT關(guān)鍵字，存儲(chǔ)過程仍然會(huì)執(zhí)行但不返回值

CREATEPROC[EDURE]存儲(chǔ)過程名

[{@參數(shù)1數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT],.......,{@參數(shù)n數(shù)據(jù)類型}[=默認(rèn)值][OUTPUT]]ASSQL語句

表示輸出參數(shù)創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程示例-1USEempDBGOCREATEPROCEDUREpro_job@dnoint=20,@jbvarchar(20)='SALESMAN',--默認(rèn)參數(shù)放后@rsintoutput--OUTPUT關(guān)鍵字，否則視為輸入?yún)?shù)ASprint'部門:'+convert(varchar(5),@dno)+'工作:'+@jbprint'---------------------------'SELECT@rs=COUNT(*)FROMempWHEREdeptno=@dnoandjob=@jbprint'人數(shù)：'+convert(varchar(10),@rs)GO/*--調(diào)用存儲(chǔ)過程--*/declare@noint--定義變量，用于存放調(diào)用存儲(chǔ)過程時(shí)返回的結(jié)果execpro_job30,'CLERK',@nooutput--調(diào)用時(shí)要帶有OUTPUT關(guān)鍵字print'求得的人數(shù)是：'+convert(varchar(10),@no)問題：對(duì)于不同部門，求出從事銷售工作的人數(shù)

演示創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程示例-2USEempDBGOCREATEPROCEDUREMathProd@m1smallint，

@m2smallint，

@resultsmallintOUTPUTASSET@result=@m1*@m2GO/*調(diào)用存儲(chǔ)過程，傳遞兩個(gè)實(shí)參5和6，將求得結(jié)果輸出到變量@answer*/DECLARE@answersmallintEXECMathProd5,6,@answerOUTPUTSELECT'Theresultis:',@answer問題:創(chuàng)建一個(gè)名為MathProd的存儲(chǔ)過程，用以計(jì)算出兩個(gè)數(shù)字的乘積，然后聲明一個(gè)變量以打印字符串“Theresultis：演示創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程修改和刪除存儲(chǔ)過程修改存儲(chǔ)過程可以通過SSMS或T-SQL語句修改存儲(chǔ)過程。使用ALTERPROCEDURE語句來修改現(xiàn)有的存儲(chǔ)過程，在使用ALTERPROCEDURE進(jìn)行修改時(shí)，SQLServer會(huì)覆蓋存儲(chǔ)過程以前的定義刪除存儲(chǔ)過程DROPPROCEDURE語句用來從當(dāng)前的數(shù)據(jù)庫(kù)刪除用戶定義的存儲(chǔ)過程語法:示例:刪除存儲(chǔ)過程MathProdDROPPROCEDURE存儲(chǔ)過程名USEempDBGODROPPROCEDUREMathProdGO錯(cuò)誤信息處理為了提高存儲(chǔ)過程的效率，存儲(chǔ)過程應(yīng)該包含與用戶進(jìn)行交互的狀態(tài)（成功或失?。┑腻e(cuò)誤信息，在錯(cuò)誤發(fā)生時(shí)，盡可能給客戶提供足夠多的信息

在錯(cuò)誤處理中可以檢查以下內(nèi)容：SQLServer錯(cuò)誤自定義的錯(cuò)誤信息。@@error

這個(gè)系統(tǒng)函數(shù)包含最近一次執(zhí)行的Transact-SQL語句的錯(cuò)誤編號(hào)。當(dāng)語句執(zhí)行時(shí)，對(duì)錯(cuò)誤編號(hào)進(jìn)行清除并重新設(shè)置。RAISERROR使用RAISERROR語句能返回用戶定義的錯(cuò)誤信息并設(shè)置一個(gè)系統(tǒng)標(biāo)志來記錄已經(jīng)發(fā)生的錯(cuò)誤。在使用RAISERROR語句時(shí)必須指定錯(cuò)誤嚴(yán)重級(jí)別和信息狀態(tài)。錯(cuò)誤信息處理示例

問題:在empDB數(shù)據(jù)庫(kù)中創(chuàng)建名為AddRec的存儲(chǔ)過程，該存儲(chǔ)過程使用@@error系統(tǒng)函數(shù)來確定在每個(gè)INSERT語句執(zhí)行時(shí)是否發(fā)生錯(cuò)誤。如果發(fā)生錯(cuò)誤，事務(wù)將回滾USEempDBGOCREATEPROCEDUREAddRec@deptnoint=NULL,@dnamevarchar(20)=NULL,@locvarchar(20)=NULLASBEGINTRANSACTION--事務(wù)開始

INSERTINTODEPT(deptno,dname,loc)VALUES(@deptno,@dname,@loc)IF@@error<>0--判斷T-SQL語句是否有錯(cuò)，如果有錯(cuò)@@error不為0BEGINROLLBACKTRAN--事務(wù)回滾

RETURN--返回

ENDCOMMITTRANSACTION--事務(wù)結(jié)束/*--執(zhí)行存儲(chǔ)過程--*/EXECAddRec50,'采購(gòu)部','天河南一路'--執(zhí)行正確，事務(wù)提交EXECAddRec'aa','自動(dòng)化部','天河南一路'--執(zhí)行錯(cuò)誤，事務(wù)回滾RAISERROR語句如果存儲(chǔ)過程在執(zhí)行過程中可能會(huì)出現(xiàn)錯(cuò)誤，則需要在存儲(chǔ)過程中加入錯(cuò)誤檢查語句。在存儲(chǔ)過程中，可以使用PRINT語句顯示用戶定義的錯(cuò)誤信息。但是，這些信息是臨時(shí)的，且只能顯示給用戶，使用RAISERROR語句能返回用戶定義的錯(cuò)誤信息并設(shè)置一個(gè)系統(tǒng)標(biāo)志來記錄已經(jīng)發(fā)生的錯(cuò)誤。在使用RAISERROR語句時(shí)必須指定錯(cuò)誤嚴(yán)重級(jí)別和信息狀態(tài)語法:其中:msg_id：在sysmessages系統(tǒng)表中指定的用戶定義錯(cuò)誤信息。msg_str：用戶定義的特定信息，最長(zhǎng)255個(gè)字符severity：與特定信息相關(guān)聯(lián)，表示用戶定義的嚴(yán)重性級(jí)別。用戶可使用的級(jí)別為0-18級(jí)。19-25級(jí)是為sysadmin固定角色的成員預(yù)留的，并且需要指定WITHLOG選項(xiàng)。20-25級(jí)錯(cuò)誤被認(rèn)為是致命錯(cuò)誤。state：表示錯(cuò)誤的狀態(tài)，是1-127的值option：指示是否將錯(cuò)誤記錄到服務(wù)器錯(cuò)誤日志中。RAISERROR（{msg_id|msg_str}{,severity,state}）

[WITHoption[，。。。n]]RAISERROR語句示例接前面示例代碼如下:CREATEPROCEDUREpro_emp@dnointASdeclare@salavgfloatSELECT@salavg=AVG(sal)FROMempWHEREdeptno=@dnoprint'部門平均工資為:'+convert(varchar(10),@salavg)GO當(dāng)用戶調(diào)用存儲(chǔ)過程時(shí)，傳入的部門號(hào)為負(fù)數(shù)時(shí)，將彈出錯(cuò)誤警告，終止存儲(chǔ)過程的執(zhí)行RAISERROR語句示例USEempDBGO/*--創(chuàng)建存儲(chǔ)過程--*/CREATEPROCEDUREpro_emp@dnointASIF(@dno<0)BEGINRAISERROR(‘部門號(hào)錯(cuò)誤,請(qǐng)指定一個(gè)正整數(shù),程序退出’,16,1)