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

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

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

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

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

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

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

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

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ù)的存儲(chǔ)過(guò)程DECLARE answer intEXEC

10、 proc1 4, 7, answer OUTPUTSELECT answer as ANSWER 必須定義一個(gè)變量，以接受返回值寫(xiě)上OUTPUT,才可以接收到返回值顯式地重新編譯存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程可以顯式地重新編譯，但應(yīng)盡量少做，僅當(dāng)存儲(chǔ)過(guò)程所引用的表中的數(shù)據(jù)發(fā)生巨大的變化時(shí)存儲(chǔ)過(guò)程所引用的對(duì)象的架構(gòu)發(fā)生變更時(shí)，如增加刪除列、規(guī)則、約束，或者為底層表增加了存儲(chǔ)過(guò)程可能從中受益的索引時(shí)顯式地重新編譯存儲(chǔ)過(guò)程（續(xù)）三種顯式重新編譯存儲(chǔ)過(guò)程的方法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 不對(duì)該存儲(chǔ)過(guò)程計(jì)劃進(jìn)行高速緩存EXEC proc1 4, 7, answer OUTPUT WITH RECOMPILE 顯式地重新編譯存儲(chǔ)過(guò)程（續(xù)）三種顯式重新編譯存儲(chǔ)過(guò)程的方法sp_recompileEXEC sp_recompile proc1說(shuō)明：下次存儲(chǔ)過(guò)程proc1執(zhí)行時(shí)重新編譯。EXEC sp_recompile Orders說(shuō)明：作用于表Orders上的存儲(chǔ)過(guò)程在下次運(yùn)行時(shí)重新編譯。存儲(chǔ)過(guò)程類型SQL Server 支持五種類型的存儲(chǔ)過(guò)程系統(tǒng)存

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

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

14、儲(chǔ)過(guò)程允許開(kāi)發(fā)者創(chuàng)建用戶定義的錯(cuò)誤信息，指定消息號(hào)、嚴(yán)重級(jí)別和消息文本，可設(shè)定為把錯(cuò)誤信息自動(dòng)記錄到 Windows 2000 應(yīng)用程序日志中RAISERROR 語(yǔ)句返回一個(gè)用戶定義的錯(cuò)誤信息，并設(shè)置系統(tǒng)標(biāo)志記錄發(fā)生了一個(gè)錯(cuò)誤error 全局變量error 包含了最近執(zhí)行的 Transact-SQL 語(yǔ)句的錯(cuò)誤號(hào)，隨著每一條語(yǔ)句的執(zhí)行而更新如果語(yǔ)句成功執(zhí)行，返回0演示1 錯(cuò)誤信息處理例：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，插入學(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 錯(cuò)誤信息處理（續(xù)）用下面的語(yǔ)句驗(yàn)證：EXEC upStudInsert Tom, 2007-1-1, 1988-11-1EXEC upStudInsert Tom, 1981-1-1, 1988-11-1結(jié)果： INSERT 語(yǔ)句與 COLUMN

16、CHECK 約束 CK_tblstuden_birth_6383C8BA 沖突。該沖突發(fā)生于數(shù)據(jù)庫(kù) student1，表 tblstudent, column birthdate。語(yǔ)句已終止。 fail結(jié)果：success演示2 錯(cuò)誤信息處理（續(xù)）創(chuàng)建一個(gè)自定義的錯(cuò)誤信息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)建一個(gè)返回錯(cuò)誤信息的存儲(chǔ)過(guò)程演示2 錯(cuò)誤信息處理（續(xù)）執(zhí)行這個(gè)存儲(chǔ)過(guò)程DECLARE result int, a int , b intset a = 1set b = -3EXEC proc1 a,b,result outputselect The result = result演示 3 錯(cuò)誤信息處理（續(xù)）創(chuàng)建一個(gè)自定義的錯(cuò)誤信息EXEC SP_ADDMESSAGE 50005, 16,