存儲過程2回顧事務具有ACID四個屬性，它們是？管理控制事務的常用T-SQL語句有哪些？索引分為哪幾類？您在stuInfo表中創建了主鍵列stuNo，SQLServer將自動創建哪些索引？3本章目標了解存儲過程的優點掌握常用的系統存儲過程掌握如何創建存儲過程掌握如何調用存儲過程4存儲過程（procedure）類似于C語言中的函數用來執行管理任務或應用復雜的業務規則存儲過程可以帶參數，也可以返回結果intsum(inta,intb){ints;s=a+b;returns;}存儲過程相當于C語言中的函數什么是存儲過程5存儲過程------------------------單個SELECT語句SELECT語句塊SELECT語句與邏輯控制語句可以包含什么是存儲過程存儲過程可以包含數據操縱語句、變量、邏輯控制語句等6存儲過程的優點執行速度更快允許模塊化程序設計提高系統安全性減少網絡流通量7存儲過程的分類系統存儲過程由系統定義，存放在master數據庫中類似C語言中的系統函數系統存儲過程的名稱都以“sp_”開頭或”xp_”開頭用戶自定義存儲過程由用戶在自己的數據庫中創建的存儲過程類似C語言中的用戶自定義函數8常用的系統存儲過程系統存儲過程說明sp_databases列出服務器上的所有數據庫。sp_helpdb報告有關指定數據庫或所有數據庫的信息sp_renamedb更改數據庫的名稱sp_tables返回當前環境下可查詢的對象的列表sp_columns回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當前環境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發器或視圖的實際文本。9EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

常用的系統存儲過程修改數據庫的名稱(單用戶訪問)列出當前系統中的數據庫當前數據庫中查詢的對象的列表返回某個表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當前數據庫中的存儲過程演示：常用的存儲過程10如何創建存儲過程定義存儲過程的語法CREATEPROC[EDURE]存儲過程名@參數1數據類型=默認值OUTPUT,……,@參數n數據類型=默認值OUTPUTASSQL語句GO和C語言的函數一樣，參數可選參數分為輸入參數、輸出參數輸入參數允許有默認值11創建不帶參數的存儲過程問題：請創建存儲過程，查看本次考試平均分以及未通過考試的學員名單12創建不帶參數的存儲過程CREATEPROCEDUREproc_stu

ASDECLARE@writtenAvgfloat,@labAvgfloat

SELECT@writtenAvg=AVG(cSharpExam),

@labAvg=AVG(SQLExam)

FROMstuMarksprint'C#平均分：'+convert(varchar(5),@writtenAvg)print'SQL平均分：'+convert(varchar(5),@labAvg)IF(@writtenAvg>70AND@labAvg>70)print'本班考試成績：優秀'ELSEprint'本班考試成績：較差'print'--------------------------------------------------'print'參加本次考試沒有通過的學員：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExam

FROMstuInfoLeftJOINstuMarksON

stuInfo.stuNo=stuMarks.stuNo

WHEREcSharpExam<60ORSQLExam<60GOproc_stu為存儲過程的名稱C#平均分和SQL平均分變量顯示考試成績的等級顯示未通過的學員13調用存儲過程EXECUTE（執行）語句用來調用存儲過程調用的語法

EXEC

過程名

[參數]EXECproc_stu14存儲過程的參數分兩種：輸入參數輸出參數輸入參數：用于向存儲過程傳入值輸出參數：用于在調用存儲過程后，返回結果帶參數存儲過程15帶輸入參數的存儲過程問題：修改上例：由于每次考試的難易程度不一樣，每次C#和SQL的及格線可能隨時變化（不再是60分），這導致考試的評判結果也相應變化。分析：在述存儲過程添加2個輸入參數：@SQLPassC#及格線@cSharpPassSQL及格線16帶輸入參數的存儲過程CREATEPROCEDUREproc_stu

@SQLPassint,

@cSharpPassint

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學員：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam<@SQLPass

ORSQLExam<@cSharpPass

GO輸入參數：C#及格線輸入參數：SQL及格線查詢沒有通過考試的學員17帶輸入參數的存儲過程EXECproc_stu60,55調用帶參數的存儲過程

假定本次考試SQL偏難，SQL的及格線定為55分，C#及格線定為60分--或這樣調用：EXECproc_stu@cSharpPass=55,@SQLPass=6018輸入參數的默認值帶參數的存儲過程確實比較方便，調用者可根據試卷的難易度，隨時修改每次考試的及格線問題：如果試卷的難易程度合適，則調用者還是必須如此調用：EXECproc_stu60,60，比較麻煩這樣調用就比較合理：EXECproc_stu55EXECproc_stu

C#及格線55分，SQL及格線默認為60分C#和SQL及格線都默認為標準的60分19CREATEPROCEDUREproc_stu

@SQLPassint=60,

@cSharpPassint=60

ASprint'--------------------------------------------------'print'參加本次考試沒有通過的學員：'SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfo LEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam<@SQLPass

ORSQLExam<@cSharpPass

GOC#及格線：默認為60分SQL及格線：默認為60分查詢沒有通過考試的學員輸入參數的默認值20輸入參數的默認值EXECproc_stu--都采用默認值EXECproc_stu64--SQL采用默認值EXECproc_stu60,55--都不采用默認值調用帶參數默認值的存儲過程--錯誤的調用方式：希望C#采用默認值，SQL及格線55分EXECproc_stu,55--正確的調用方式：EXECproc_stu@cSharpPass=5521帶輸出參數的存儲過程如果希望調用存儲過程后，返回一個或多個值，這時就需要使用輸出（OUTPUT）參數了

問題：修改上例，返回未通過考試的學員人數。22CREATEPROCEDUREproc_stu

@notpassSumintOUTPUT,@SQLPassint=60,@cSharpPassint=60AS……SELECTstuName,stuInfo.stuNo,cSharpExam,SQLExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREcSharpExam<@SQLPassORSQLExam<@cSharpPassSELECT@notpassSum=COUNT(stuNo)FROMstuMarksWHEREcSharpExam<@SQLPassORSQLExam<@cSharpPassGO輸出（返回）參數：表示沒有通過的人數推薦將默認參數放后帶輸出參數的存儲過程統計并返回沒有通過考試的學員人數23/*---調用存儲過程----*/DECLARE@sumintEXECproc_stu@sumOUTPUT,64print'--------------------------------------------------'IF@sum>=3print'未通過人數：'+convert(varchar(5),@sum)+'人,

超過60%,及格分數線還應下調'ELSEprint'未通過人數：'+convert(varchar(5),@sum)+'人,

已控制在60%以下，及格分數線適中'GO調用帶輸出參數的存儲過程帶輸出參數的存儲過程調用時必須帶OUTPUT關鍵字，返回結果將存放在變量@sum中后續語句引用返回結果24處理存儲過程中的錯誤可以使用PRINT語句顯示錯誤信息，但這些信息是臨時的，只能顯示給用戶RAISERROR顯示用戶定義的錯誤信息時可指定嚴重級別，設置系統變量@@ERROR記錄所發生的錯誤等25使用RAISERROR語句RAISERROR(msg_id|msg_str,severity,stateWITHoption[,...n]])RAISERROR語句的用法如下：msg_id：在sysmessages系統表中指定用戶定義錯誤信息msg_str：用戶定義的特定信息，最長255個字符severity：定義嚴重性級別。用戶可使用的級別為0–18級state：表示錯誤的狀態，1至127之間的值option：指示是否將錯誤記錄到服務器錯誤日志中26問題：完善上例，當用戶調用存儲過程時，傳入的及格線參數不在0～100之間時，將彈出錯誤警告，終止存儲過程的執行。

使用RAISERROR語句27CREATEPROCEDUREproc_stu@notpassSumintOUTPUT,--輸出參數

@SQLPassint=60,--默認參數放后

@cSharpPassint=60--默認參數放后

ASIF(NOT@SQLPassBETWEEN0AND100)OR(NOT@cSharpPassBETWEEN0AND100)BEGIN

RAISERROR(‘及格線錯誤，請指定0－100之間的分數，統計中斷退出',16,1)RETURN---立即返回，退出存儲過程

END…..其他語句同上例，略GO錯誤處理引發系統錯誤，指定錯誤的嚴重級別16，調用狀態為1（默認）,并影響@@ERROR系統變量的值使用RAIS