存儲過程介紹創(chuàng)建和管理存儲過程在存儲過程中使用參數(shù)系統(tǒng)課件

1、存儲過程介紹創(chuàng)建和管理存儲過程在存儲過程中使用參數(shù)系統(tǒng)和擴(kuò)展存儲過程處理錯誤信息第13章 實(shí)現(xiàn)存儲過程定義存儲過程存儲過程的優(yōu)點(diǎn)存儲過程的初始化和后續(xù)處理存儲過程介紹定義存儲過程存儲過程是存儲在服務(wù)器上的 Transact-SQL 語句的命名集合是封裝重復(fù)性任務(wù)的方法支持用戶聲明變量、條件執(zhí)行以及其他強(qiáng)有力的編程特性SQL Server 中的存儲過程與其他編程語言中的過程類似，它可以包含執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其他過程）的編程語句接受輸入?yún)?shù)向調(diào)用過程或批處理返回狀態(tài)值，以表明成功或失?。ㄒ约笆≡颍┮暂敵鰠?shù)的形式將多個值返回至調(diào)用過程或批處理存儲過程的優(yōu)點(diǎn)存儲過程封裝了商務(wù)邏輯，確保一致

2、的數(shù)據(jù)訪問和修改。若規(guī)則或策略有變化，則只需要修改服務(wù)器上的存儲過程，所有的客戶端就可以直接使用屏蔽數(shù)據(jù)庫模式的詳細(xì)資料。用戶不需要訪問底層的數(shù)據(jù)庫和數(shù)據(jù)庫內(nèi)的對象提供了安全性機(jī)制。用戶可以被賦予執(zhí)行存儲過程的權(quán)限，而不必在存儲過程引用的所有對象上都有權(quán)限改善性能。預(yù)編譯的 Transact-SQL 語句，可以根據(jù)條件決定執(zhí)行哪一部分減少網(wǎng)絡(luò)通信量。客戶端用一條語句調(diào)用存儲過程，就可以完成可能需要大量語句才能完成的任務(wù)，這樣減少了客戶端和服務(wù)器之間的請求/回答包存儲過程的后續(xù)處理若符合下列條件，則 SQL Server 使用在內(nèi)存中的計(jì)劃來執(zhí)行隨后的查詢當(dāng)前的環(huán)境和計(jì)劃編譯時的環(huán)境相同。服務(wù)器

3、、數(shù)據(jù)庫和連接的設(shè)置決定了環(huán)境存儲過程引用的對象不需要名稱解析。若被不同用戶擁有的對象具有相同的名字，則需要名稱解析。一個執(zhí)行計(jì)劃產(chǎn)生后，駐留在過程緩存中。僅當(dāng)需要空間時，SQL Server 將老的、沒用的計(jì)劃移出緩存存儲過程的后續(xù)處理（續(xù)）檢索到的執(zhí)行計(jì)劃未用過的計(jì)劃過時被清除執(zhí)行計(jì)劃執(zhí)行上下文SELECT *FROM dbo.memberWHEREmember_no = ?連接18082連接2連接3241003創(chuàng)建和管理存儲過程創(chuàng)建存儲過程執(zhí)行存儲過程修改和刪除存儲過程創(chuàng)建存儲過程創(chuàng)建存儲過程只能在當(dāng)前數(shù)據(jù)庫內(nèi)創(chuàng)建存儲過程，除了臨時存儲過程。臨時存儲過程總是創(chuàng)建在 tempdb 數(shù)據(jù)庫中

4、存儲過程可以引用表、視圖、用戶定義函數(shù)、其他存儲過程以及臨時表若存儲過程創(chuàng)建了局部臨時表，則當(dāng)存儲過程執(zhí)行結(jié)束后臨時表消失USE NorthwindGOCREATE PROC dbo.OverdueOrdersAS SELECT * FROM dbo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS NullGO創(chuàng)建存儲過程（續(xù)）創(chuàng)建存儲過程（續(xù)）CREATE PROCEDURE 定義可以包括任何數(shù)目和類型的Transact-SQL語句，但不包括下列對象創(chuàng)建語句：CREATE DEFAULT、CREATE PROCEDURE、CREA

5、TE RULE、CREATE TRIGGER 和 CREATE VIEW執(zhí)行 CREATE PROCEDURE 語句的用戶必須是 sysadmin、db_owner 或 db_ddladmin角色的成員，或必須擁有 CREATE PROCEDURE 權(quán)限依賴于可用內(nèi)存，存儲過程的最大大小為128 MB存儲過程可以嵌套32層。當(dāng)前的嵌套層數(shù)存儲在系統(tǒng)函數(shù) nestlevel 中。創(chuàng)建存儲過程（續(xù)）查看存儲過程的信息查看所有類型存儲過程的額外信息系統(tǒng)存儲過程 sp_help、sp_helptext、sp_depends顯示數(shù)據(jù)庫中的存儲過程以及擁有者名字的列表系統(tǒng)存儲過程 sp_stored_pr

6、ocedures得到存儲過程的信息查詢系統(tǒng)表 sysobjects、syscomments、sysdepends創(chuàng)建存儲過程的指導(dǎo)原則 避免出現(xiàn)存儲過程的擁有者和底層對象的擁有者不同的情況，建議由dbo用戶擁有數(shù)據(jù)庫中所有對象每個存儲過程完成單個任務(wù)命名本地存儲過程的時候，避免使用“sp_”前綴盡量少使用臨時存儲過程，以避免頻繁連接 tempdb 里的系統(tǒng)表不要直接從 syscomments 系統(tǒng)表里刪除項(xiàng)執(zhí)行存儲過程單獨(dú)執(zhí)行存儲過程不帶參數(shù)的情況：EXECUTE 存儲過程名 WITH RECOMPILE在 INSERT 語句內(nèi)執(zhí)行存儲過程語法：INSERT INTO 表名 EXECUTE 將

7、本地或遠(yuǎn)程存儲過程返回的結(jié)果集插入本地表中在 INSERT 語句內(nèi)執(zhí)行的存儲過程必須返回關(guān)系結(jié)果集USE NorthwindGOEXEC OverdueOrdersGO 修改和刪除存儲過程（續(xù)）刪除存儲過程語法：DROP PROCEDURE 存儲過程名 ,.n用 DROP PROCEDURE 語句從當(dāng)前數(shù)據(jù)庫中移除用戶定義存儲過程刪除存儲過程的注意事項(xiàng)在刪除存儲過程之前，執(zhí)行系統(tǒng)存儲過程 sp_depends 檢查是否有對象依賴于此存儲過程存儲過程介紹創(chuàng)建和管理存儲過程在存儲過程中使用參數(shù)系統(tǒng)和擴(kuò)展存儲過程處理錯誤信息第13章 實(shí)現(xiàn)存儲過程使用輸入?yún)?shù)輸入?yún)?shù)允許傳遞信息到存儲過程內(nèi)在 CRE

8、ATE PROCEDURE 中指定 參數(shù)名 數(shù)據(jù)類型 =默認(rèn)值 USE NorthwindGOCREATE PROC dbo.OverdueOrders2Employee_ID int ,Order_date datetimeASSELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeIDFROM OrdersWHERE EmployeeID = Employee_ID and OrderDate = Order_dateGO 使用輸入?yún)?shù)執(zhí)行存儲過程通過參數(shù)名傳遞值(順序無所謂)通過位置傳遞參

9、數(shù)（順序保持一致）EXEC OverdueOrders2 Employee_ID = 1 , Order_date = 1996-7-17EXEC OverdueOrders2 1 , 1996-7-17EXEC OverdueOrders2 Order_date = 1996-7-17, Employee_ID = 1 使用輸出參數(shù)返回值輸出參數(shù)：以 OUTPUT 關(guān)鍵字指定的變量CREATE PROC proc1A int , B int , RESULT int OUTPUTASSET RESULT = A * BGO 執(zhí)行有輸出參數(shù)的存儲過程DECLARE answer intEXEC

10、 proc1 4, 7, answer OUTPUTSELECT answer as ANSWER 必須定義一個變量，以接受返回值寫上OUTPUT,才可以接收到返回值顯式地重新編譯存儲過程存儲過程可以顯式地重新編譯，但應(yīng)盡量少做，僅當(dāng)存儲過程所引用的表中的數(shù)據(jù)發(fā)生巨大的變化時存儲過程所引用的對象的架構(gòu)發(fā)生變更時，如增加刪除列、規(guī)則、約束，或者為底層表增加了存儲過程可能從中受益的索引時顯式地重新編譯存儲過程（續(xù)）三種顯式重新編譯存儲過程的方法CREATE PROCEDURE WITH RECOMPILEEXECUTE WITH RECOMPILE CREATE PROC proc1 A int

11、, B int , RESULT int OUTPUT WITH RECOMPILE AS SET RESULT = A * B GO 不對該存儲過程計(jì)劃進(jìn)行高速緩存EXEC proc1 4, 7, answer OUTPUT WITH RECOMPILE 顯式地重新編譯存儲過程（續(xù)）三種顯式重新編譯存儲過程的方法sp_recompileEXEC sp_recompile proc1說明：下次存儲過程proc1執(zhí)行時重新編譯。EXEC sp_recompile Orders說明：作用于表Orders上的存儲過程在下次運(yùn)行時重新編譯。存儲過程類型SQL Server 支持五種類型的存儲過程系統(tǒng)存

12、儲過程（sp_）：存儲在 master 數(shù)據(jù)庫內(nèi)，以“sp_”前綴標(biāo)識本地存儲過程：本地存儲過程在單獨(dú)的用戶數(shù)據(jù)庫內(nèi)創(chuàng)建臨時存儲過程：臨時存儲過程可能是局部的，名稱以“#”開頭；也可能是全局的，名稱以“#”開頭遠(yuǎn)程存儲過程：遠(yuǎn)程存儲過程是 SQL Server 的一個傳統(tǒng)功能，分布式查詢支持這項(xiàng)功能擴(kuò)展存儲過程（xp_）：擴(kuò)展存儲過程以動態(tài)鏈接庫（DLL）的形式實(shí)現(xiàn)，在 SQL Server 環(huán)境外執(zhí)行執(zhí)行擴(kuò)展存儲過程擴(kuò)展存儲過程是以動態(tài)鏈接庫（DLL）的形式存放C+代碼，用于擴(kuò)展 SQL Server 2000 的功能大多數(shù)擴(kuò)展存儲過程和其他系統(tǒng)存儲過程一起執(zhí)行，很少單獨(dú)使用允許用戶使用編程

13、語言例如微軟的 Visual C+ 等創(chuàng)建自己的外部例程，并創(chuàng)建自己的擴(kuò)展存儲過程只能被添加到 master 數(shù)據(jù)庫存儲過程介紹創(chuàng)建和管理存儲過程在存儲過程中使用參數(shù)系統(tǒng)和擴(kuò)展存儲過程處理錯誤信息第13章 實(shí)現(xiàn)存儲過程錯誤信息處理為了增強(qiáng)存儲過程的效率，應(yīng)使用錯誤信息向用戶傳達(dá)事務(wù)狀態(tài)（成功或失?。┛梢栽阱e誤處理邏輯中檢查下列錯誤：返回碼、SQL Server 錯誤、用戶定義的錯誤信息RETURN 語句從查詢或存儲過程無條件返回，同時可以返回一個整數(shù)狀態(tài)值（返回碼）返回碼為0表示成功。返回非零表示失敗。用戶定義的返回值總是優(yōu)先于系統(tǒng)的返回值。錯誤信息處理（續(xù)）sp_addmessage 系統(tǒng)存

14、儲過程允許開發(fā)者創(chuàng)建用戶定義的錯誤信息，指定消息號、嚴(yán)重級別和消息文本，可設(shè)定為把錯誤信息自動記錄到 Windows 2000 應(yīng)用程序日志中RAISERROR 語句返回一個用戶定義的錯誤信息，并設(shè)置系統(tǒng)標(biāo)志記錄發(fā)生了一個錯誤error 全局變量error 包含了最近執(zhí)行的 Transact-SQL 語句的錯誤號，隨著每一條語句的執(zhí)行而更新如果語句成功執(zhí)行，返回0演示1 錯誤信息處理例：創(chuàng)建一個存儲過程，插入學(xué)生信息。 create proc upStudInsert studName varchar(20),birthdate datetime,schooldate datetime as

15、begin transaction insert into tblstudent values(studName,birthdate,schooldate) if error 0 begin rollback tran print fail return end else print success commit transaction GO演示1 錯誤信息處理（續(xù)）用下面的語句驗(yàn)證：EXEC upStudInsert Tom, 2007-1-1, 1988-11-1EXEC upStudInsert Tom, 1981-1-1, 1988-11-1結(jié)果： INSERT 語句與 COLUMN

16、CHECK 約束 CK_tblstuden_birth_6383C8BA 沖突。該沖突發(fā)生于數(shù)據(jù)庫 student1，表 tblstudent, column birthdate。語句已終止。 fail結(jié)果：success演示2 錯誤信息處理（續(xù)）創(chuàng)建一個自定義的錯誤信息EXEC SP_ADDMESSAGE 50011, 16, Please input again, EnglishCREATE PROC proc1a int, b int, result int outputASif a 0 or b 0 begin RAISERROR(50011,16,1) RETURN end set result = a + bGO創(chuàng)建一個返回錯誤信息的存儲過程演示2 錯誤信息處理（續(xù)）執(zhí)行這個存儲過程DECLARE result int, a int , b intset a = 1set b = -3EXEC proc1 a,b,result outputselect The result = result演示 3 錯誤信息處理（續(xù)）創(chuàng)建一個自定義的錯誤信息EXEC SP_ADDMESSAGE 50005, 16,