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

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

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

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

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

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

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

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

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

創(chuàng)建不帶參數(shù)的存儲過程問題：查看部門的平均工資以及各部門工資不超過2000的員工，并根據(jù)工資數(shù)額顯示加薪信息USEempDBGO/*--創(chuàng)建存儲過程---*/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)用存儲過程語法:

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

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

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

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

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

USEempDBGO/*--創(chuàng)建參數(shù)帶默認(rèn)值的存儲過程--*/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)用存儲過程--*/EXECproc_job--都采用默認(rèn)值，得到10部門，從事銷售工作的人數(shù)EXECproc_job30--部門號為30，工種采用默認(rèn)值EXECproc_job20,‘CLERK’--都不采用默認(rèn)值，部門號位0，工種為’CLERK’--錯誤的調(diào)用方式：EXECproc_job,’CLERK’

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

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

CREATEPROC[EDURE]存儲過程名

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

表示輸出參數(shù)創(chuàng)建帶輸出參數(shù)的存儲過程示例-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)用存儲過程--*/declare@noint--定義變量，用于存放調(diào)用存儲過程時返回的結(jié)果execpro_job30,'CLERK',@nooutput--調(diào)用時要帶有OUTPUT關(guān)鍵字print'求得的人數(shù)是：'+convert(varchar(10),@no)問題：對于不同部門，求出從事銷售工作的人數(shù)

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

@m2smallint，

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

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

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

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

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

RETURN--返回

ENDCOMMITTRANSACTION--事務(wù)結(jié)束/*--執(zhí)行存儲過程--*/EXECAddRec50,'采購部','天河南一路'--執(zhí)行正確，事務(wù)提交EXECAddRec'aa','自動化部','天河南一路'--執(zhí)行錯誤，事務(wù)回滾RAISERROR語句如果存儲過程在執(zhí)行過程中可能會出現(xiàn)錯誤，則需要在存儲過程中加入錯誤檢查語句。在存儲過程中，可以使用PRINT語句顯示用戶定義的錯誤信息。但是，這些信息是臨時的，且只能顯示給用戶，使用RAISERROR語句能返回用戶定義的錯誤信息并設(shè)置一個系統(tǒng)標(biāo)志來記錄已經(jīng)發(fā)生的錯誤。在使用RAISERROR語句時必須指定錯誤嚴(yán)重級別和信息狀態(tài)語法:其中:msg_id：在sysmessages系統(tǒng)表中指定的用戶定義錯誤信息。msg_str：用戶定義的特定信息，最長255個字符severity：與特定信息相關(guān)聯(lián)，表示用戶定義的嚴(yán)重性級別。用戶可使用的級別為0-18級。19-25級是為sysadmin固定角色的成員預(yù)留的，并且需要指定WITHLOG選項。20-25級錯誤被認(rèn)為是致命錯誤。state：表示錯誤的狀態(tài)，是1-127的值option：指示是否將錯誤記錄到服務(wù)器錯誤日志中。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)用存儲過程時，傳入的部門號為負(fù)數(shù)時，將彈出錯誤警告，終止存儲過程的執(zhí)行RAISERROR語句示例USEempDBGO/*--創(chuàng)建存儲過程--*/CREATEPROCEDUREpro_emp@dnointASIF(@dno<0)BEGINRAISERROR(‘部門號錯誤,請指定一個正整數(shù),程序退出’,16,1)