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

系統存儲過程:SQLServer提供了一些預編譯的存儲過程，用以管理SQLServer和顯示有關數據庫和用戶的信息

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

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

常用的系統存儲過程的使用EXECsp_databases--不帶參數EXECsp_helpdb

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

使用CREATEPROCEDURE語句創建存儲過程。所有的存儲過程都創建在當前數據庫中語法:CREATEPROC[EDURE]存儲過程名

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

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

調用示例存儲過程執行結果如下:EXEC過程名[參數]EXECproc_emp創建帶參數的存儲過程CREATEPROC[EDURE]存儲過程名

[{@參數1數據類型}[=默認值][OUTPUT],.......,{@參數n數據類型}[=默認值][OUTPUT]]ASSQL語句

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

USEempDBGO/*--創建存儲過程--*/CREATEPROCEDUREpro_emp@dnointASdeclare@salavgfloatSELECT@salavg=AVG(sal)FROMempWHEREdeptno=@dnoprint'部門平均工資為:'+convert(varchar(10),@salavg)GO/*--調用存儲過程--*/--給定部門號10，求出該部門的平均工資EXECpro_emp10演示創建帶輸入參數的存儲過程創建帶輸入參數的存儲過程示例-2

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

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

–部門號為默認值--正確的調用方式：EXECproc_job@jb=’CLERK’

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

CREATEPROC[EDURE]存儲過程名

[{@參數1數據類型}[=默認值][OUTPUT],.......,{@參數n數據類型}[=默認值][OUTPUT]]ASSQL語句

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

演示創建帶輸出參數的存儲過程創建帶輸出參數的存儲過程示例-2USEempDBGOCREATEPROCEDUREMathProd@m1smallint，

@m2smallint，

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

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

這個系統函數包含最近一次執行的Transact-SQL語句的錯誤編號。當語句執行時，對錯誤編號進行清除并重新設置。RAISERROR使用RAISERROR語句能返回用戶定義的錯誤信息并設置一個系統標志來記錄已經發生的錯誤。在使用RAISERROR語句時必須指定錯誤嚴重級別和信息狀態。錯誤信息處理示例

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

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

RETURN--返回

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

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