數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)存儲(chǔ)過(guò)程PPT課件

《數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)存儲(chǔ)過(guò)程PPT課件》由會(huì)員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫(kù)應(yīng)用基礎(chǔ)存儲(chǔ)過(guò)程PPT課件（39頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、17.1 存儲(chǔ)過(guò)程概述7.2 創(chuàng)建存儲(chǔ)過(guò)程 7.3修改和刪除存儲(chǔ)過(guò)程 本章主要內(nèi)容本章主要內(nèi)容第1頁(yè)/共39頁(yè)27.1 7.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程(stored procedure)(stored procedure)是存放在服務(wù)器上的預(yù)先編譯好是存放在服務(wù)器上的預(yù)先編譯好的的SQLSQL語(yǔ)句語(yǔ)句, ,用于完成某項(xiàng)任務(wù)，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用參數(shù)值，并且可以嵌套調(diào)用 第2頁(yè)/共39頁(yè)3存儲(chǔ)過(guò)程類(lèi)型系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程本地存儲(chǔ)過(guò)程本地存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程遠(yuǎn)程存儲(chǔ)過(guò)程遠(yuǎn)程存儲(chǔ)過(guò)程擴(kuò)展

2、存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程本章主要內(nèi)容第3頁(yè)/共39頁(yè)4系統(tǒng)存儲(chǔ)過(guò)程：系統(tǒng)存儲(chǔ)過(guò)程：存儲(chǔ)在存儲(chǔ)在mastermaster數(shù)據(jù)庫(kù)中，由前綴數(shù)據(jù)庫(kù)中，由前綴spsp標(biāo)識(shí)標(biāo)識(shí)作用：作用：從系統(tǒng)表中獲取信息，從系統(tǒng)表中獲取信息，允許系統(tǒng)管理員在沒(méi)有直接更允許系統(tǒng)管理員在沒(méi)有直接更新底層表的許可權(quán)下執(zhí)行更新新底層表的許可權(quán)下執(zhí)行更新數(shù)據(jù)庫(kù)中系統(tǒng)表的數(shù)據(jù)庫(kù)管理數(shù)據(jù)庫(kù)中系統(tǒng)表的數(shù)據(jù)庫(kù)管理工作。工作。絕大部分的系統(tǒng)存儲(chǔ)過(guò)絕大部分的系統(tǒng)存儲(chǔ)過(guò)程可以在任何數(shù)據(jù)庫(kù)中執(zhí)行程可以在任何數(shù)據(jù)庫(kù)中執(zhí)行 第4頁(yè)/共39頁(yè)5本地存儲(chǔ)過(guò)程：本地存儲(chǔ)過(guò)程：這是用戶在獨(dú)立的這是用戶在獨(dú)立的用戶數(shù)據(jù)庫(kù)中為了完成某一特定功用戶數(shù)據(jù)庫(kù)中為了完成

3、某一特定功能而編寫(xiě)的存儲(chǔ)過(guò)程能而編寫(xiě)的存儲(chǔ)過(guò)程 臨時(shí)存儲(chǔ)過(guò)程：臨時(shí)存儲(chǔ)過(guò)程：它它與臨時(shí)表類(lèi)似，與臨時(shí)表類(lèi)似，通常又分為通常又分為本地本地和和全局全局臨時(shí)存儲(chǔ)過(guò)臨時(shí)存儲(chǔ)過(guò)程程兩種兩種，當(dāng)，當(dāng)臨時(shí)臨時(shí)存儲(chǔ)過(guò)程為本地時(shí)，存儲(chǔ)過(guò)程為本地時(shí)，其名字以符號(hào)其名字以符號(hào)#開(kāi)始，為開(kāi)始，為全局全局時(shí)，時(shí)，以符號(hào)以符號(hào)#開(kāi)始開(kāi)始第5頁(yè)/共39頁(yè)6遠(yuǎn)程存儲(chǔ)過(guò)程：遠(yuǎn)程存儲(chǔ)過(guò)程：遠(yuǎn)程存儲(chǔ)過(guò)程在遠(yuǎn)程存儲(chǔ)過(guò)程在分布式分布式查詢中使用查詢中使用擴(kuò)展存儲(chǔ)過(guò)程：擴(kuò)展存儲(chǔ)過(guò)程：使使SQLSQL ServerServer可可動(dòng)態(tài)裝載并執(zhí)行動(dòng)態(tài)裝載并執(zhí)行 DLL。這樣用。這樣用戶可使用象戶可使用象 C 這樣的編程語(yǔ)言這樣的編程語(yǔ)言創(chuàng)

4、建自己的外部例程創(chuàng)建自己的外部例程擴(kuò)展存儲(chǔ)過(guò)程由前綴擴(kuò)展存儲(chǔ)過(guò)程由前綴xp標(biāo)識(shí)標(biāo)識(shí) 第6頁(yè)/共39頁(yè)7存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程的優(yōu)點(diǎn) 提供了安全機(jī)制提供了安全機(jī)制改進(jìn)了執(zhí)行性能改進(jìn)了執(zhí)行性能減少了網(wǎng)絡(luò)流量減少了網(wǎng)絡(luò)流量允許模塊化程序設(shè)計(jì)允許模塊化程序設(shè)計(jì)第7頁(yè)/共39頁(yè)8 存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程提供提供的安全的安全機(jī)制機(jī)制可以讓用戶通過(guò)存儲(chǔ)可以讓用戶通過(guò)存儲(chǔ)過(guò)程操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)，過(guò)程操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)，而不讓用戶直接操作于存而不讓用戶直接操作于存儲(chǔ)過(guò)程相關(guān)的表，從而保儲(chǔ)過(guò)程相關(guān)的表，從而保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性證數(shù)據(jù)庫(kù)中數(shù)據(jù)的安全性 第8頁(yè)/共39頁(yè)9存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程在第二次執(zhí)行時(shí)，無(wú)需預(yù)在第二次執(zhí)

5、行時(shí)，無(wú)需預(yù)編譯，從而改進(jìn)系統(tǒng)的編譯，從而改進(jìn)系統(tǒng)的執(zhí)行性能執(zhí)行性能存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程是存放在服務(wù)器上的預(yù)先是存放在服務(wù)器上的預(yù)先編譯好的編譯好的單條或多條單條或多條SQL語(yǔ)句語(yǔ)句并并在在服務(wù)器上運(yùn)行服務(wù)器上運(yùn)行，用戶無(wú)需在網(wǎng)絡(luò)上，用戶無(wú)需在網(wǎng)絡(luò)上發(fā)送上百個(gè)發(fā)送上百個(gè)SQL語(yǔ)句，或是將眾多語(yǔ)句，或是將眾多數(shù)據(jù)從服務(wù)器下載至客戶端后再進(jìn)數(shù)據(jù)從服務(wù)器下載至客戶端后再進(jìn)行處理，從而行處理，從而大大減少了網(wǎng)絡(luò)負(fù)載大大減少了網(wǎng)絡(luò)負(fù)載 第9頁(yè)/共39頁(yè)10增強(qiáng)代碼的可重用性，提高開(kāi)增強(qiáng)代碼的可重用性，提高開(kāi)發(fā)效率發(fā)效率。存儲(chǔ)過(guò)程可以視為為。存儲(chǔ)過(guò)程可以視為為完成某特定功能而編寫(xiě)的功能完成某特定功能而編寫(xiě)的功

6、能模塊模塊，將來(lái)可以在其他的存儲(chǔ)，將來(lái)可以在其他的存儲(chǔ)過(guò)程中引用該存儲(chǔ)過(guò)程，從而過(guò)程中引用該存儲(chǔ)過(guò)程，從而實(shí)現(xiàn)代碼的重用性，加快應(yīng)用實(shí)現(xiàn)代碼的重用性，加快應(yīng)用的開(kāi)發(fā)速度，提高開(kāi)發(fā)的質(zhì)量的開(kāi)發(fā)速度，提高開(kāi)發(fā)的質(zhì)量和效率和效率 第10頁(yè)/共39頁(yè)11存儲(chǔ)過(guò)程中包含的輸入/出參數(shù) 7.2 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程 7.2.17.2.1創(chuàng)建簡(jiǎn)單的存儲(chǔ)過(guò)程創(chuàng)建簡(jiǎn)單的存儲(chǔ)過(guò)程 CREATE PROCEDURE CREATE PROCEDURE owner.procedure_name;numberowner.procedure_name;numberparameter data_typeparameter

7、 data_typeVARYING=defaultOUTPUTVARYING=defaultOUTPUT，nnWITHRECOMPILE|ENCRYPTION|WITHRECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTIONRECOMPILE, ENCRYPTIONFOR REPLICATIONFOR REPLICATIONAS sql_statements AS sql_statements 重新編譯加密在存儲(chǔ)過(guò)程中需執(zhí)行的操作 第11頁(yè)/共39頁(yè)12例：在My_DB1上新建my_procedure1存儲(chǔ)過(guò)程，以返回所有diagrm_id=100和principa

8、l=200的信息。USE My_DB1goCREATE PROC dbo.my_procedure1ASSELECT * FROM dbo.sysdiagrmsWHERE diagrm_id=100diagrm_id=100 AND principal=200principal=200Go第12頁(yè)/共39頁(yè)13創(chuàng)建存儲(chǔ)過(guò)程的步驟及注意事項(xiàng)創(chuàng)建存儲(chǔ)過(guò)程的步驟及注意事項(xiàng) 不能將CREATE PROCEDURE語(yǔ)句與其它SQL語(yǔ)句組合到單個(gè)批處理中創(chuàng)建存儲(chǔ)過(guò)程是有權(quán)限的，其默認(rèn)權(quán)限為dbo，其他用戶若要獲得創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，要由dbo授權(quán)第13頁(yè)/共39頁(yè)14存儲(chǔ)過(guò)程是存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)對(duì)

9、象，在命，在命名用戶自定義的存儲(chǔ)過(guò)程時(shí)應(yīng)名用戶自定義的存儲(chǔ)過(guò)程時(shí)應(yīng)避免使用避免使用sp前綴，以免和系統(tǒng)前綴，以免和系統(tǒng)存儲(chǔ)過(guò)程混淆存儲(chǔ)過(guò)程混淆 盡量不要使用臨時(shí)存儲(chǔ)過(guò)程，盡量不要使用臨時(shí)存儲(chǔ)過(guò)程，以避免以避免tempdb上造成的對(duì)系上造成的對(duì)系統(tǒng)表資源的爭(zhēng)奪，從而影響系統(tǒng)表資源的爭(zhēng)奪，從而影響系統(tǒng)的執(zhí)行性能統(tǒng)的執(zhí)行性能 第14頁(yè)/共39頁(yè)15 存儲(chǔ)過(guò)程最大尺寸被存儲(chǔ)過(guò)程最大尺寸被限制為限制為128 MB，存儲(chǔ)過(guò)，存儲(chǔ)過(guò)程最多允許嵌套至程最多允許嵌套至32級(jí)級(jí) 第15頁(yè)/共39頁(yè)16例：例：編寫(xiě)指令執(zhí)行編寫(xiě)指令執(zhí)行my_procedure1存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程USE NorthwindEXEC my

10、_procedure1GO第16頁(yè)/共39頁(yè)17查看存查看存儲(chǔ)過(guò)程信息儲(chǔ)過(guò)程信息 sysobjectssyscommentssysdependssp_stored_procedures 可以使用如下命令：可以使用如下命令：第17頁(yè)/共39頁(yè)18例：編寫(xiě)例：編寫(xiě)SQL指令查看創(chuàng)建存儲(chǔ)過(guò)指令查看創(chuàng)建存儲(chǔ)過(guò)程程my_procedure1的相關(guān)代碼信息的相關(guān)代碼信息 USE NorthwindEXEC sp_helptext my_procedure1GO第18頁(yè)/共39頁(yè)197.2.2 創(chuàng)建和執(zhí)行含參數(shù)存儲(chǔ)過(guò)程創(chuàng)建和執(zhí)行含參數(shù)存儲(chǔ)過(guò)程 通過(guò)使用參數(shù)，可以多通過(guò)使用參數(shù)，可以多次使用同一存儲(chǔ)過(guò)程并次使

11、用同一存儲(chǔ)過(guò)程并按指定要求查找數(shù)據(jù)庫(kù)按指定要求查找數(shù)據(jù)庫(kù) 第19頁(yè)/共39頁(yè)201. 創(chuàng)建創(chuàng)建帶帶輸入?yún)?shù)輸入?yún)?shù)的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程 輸入?yún)?shù)輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過(guò)是指由調(diào)用程序向存儲(chǔ)過(guò)程傳遞的參數(shù)，為了定義接受輸入程傳遞的參數(shù)，為了定義接受輸入?yún)?shù)的存儲(chǔ)過(guò)程，需要在參數(shù)的存儲(chǔ)過(guò)程，需要在CREATE PROCEDURE語(yǔ)句中聲明一個(gè)或語(yǔ)句中聲明一個(gè)或多個(gè)變量作為參數(shù)。多個(gè)變量作為參數(shù)。如：如：parameter_name datatype=default必須是常量或NULL 第20頁(yè)/共39頁(yè)21例：在Northwind庫(kù)上創(chuàng)建存儲(chǔ)過(guò)程my_procedure2的，其中定義了兩個(gè)時(shí)

12、間類(lèi)型的輸入?yún)?shù)和一個(gè)字符型輸入?yún)?shù)，其中字符型輸入?yún)?shù)指定的默認(rèn)值為USA。第21頁(yè)/共39頁(yè)22USE NorthwindgoCREATECREATE PROCPROC dbo.my_procedure2StartDate DateTime, EndDate DateTime, Country varchar(20)=USAAS第22頁(yè)/共39頁(yè)23IF (StartDate IS NULL orEndDate IS NULL or Country IS NULL)BEGIN RAISERRORRAISERROR (NULL value are invalid,5,5)RETURNEND

13、返回用戶定義的錯(cuò)誤信息并設(shè)系統(tǒng)標(biāo)志 第23頁(yè)/共39頁(yè)24SELECT * FROM dbo.ordersWHERE OrderDate BETWEEN StartDate AND EndDate AND shipCountry=Countrygo第24頁(yè)/共39頁(yè)252. 執(zhí)行執(zhí)行帶帶輸入?yún)?shù)輸入?yún)?shù)的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程 使用參數(shù)名傳送參數(shù)值使用參數(shù)名傳送參數(shù)值按位置傳送參數(shù)值按位置傳送參數(shù)值兩種方法由如下語(yǔ)句給出傳遞值 parameter_name=value不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值 第25頁(yè)/共39頁(yè)26例：例：使用使用參數(shù)名參數(shù)名傳送參數(shù)值的方法，傳送參數(shù)值的方法，通過(guò)

14、通過(guò)my_procedure2存儲(chǔ)過(guò)程，返存儲(chǔ)過(guò)程，返回所有日期在回所有日期在1997.7.1與與1998.2.1日，日，目的地為目的地為USA的訂單記錄。的訂單記錄。USE NorthwindGOEXEC my_procedure2EndDate=2/1/1998,StartDate=7/1/1997第26頁(yè)/共39頁(yè)27例：例：使用按位置傳送參數(shù)值的方法，使用按位置傳送參數(shù)值的方法，通過(guò)通過(guò)my_procedure2存儲(chǔ)過(guò)程，返存儲(chǔ)過(guò)程，返回所有日期在回所有日期在1997.7.1與與1998.2.1日，日，目的地為目的地為USA的訂單記錄。的訂單記錄。USE NorthwindGOEXEC

15、 my_procedure27/1/1997，2/1/1998,USA第27頁(yè)/共39頁(yè)283. 創(chuàng)建創(chuàng)建帶帶輸出參數(shù)輸出參數(shù)的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程 在創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句中定義輸出在創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句中定義輸出參數(shù)可以實(shí)現(xiàn)從存儲(chǔ)過(guò)程中返回值參數(shù)可以實(shí)現(xiàn)從存儲(chǔ)過(guò)程中返回值為了使用輸出參數(shù)。為了使用輸出參數(shù)。在在CREATECREATE PROCEDUREPROCEDURE中指定中指定OUTPUTOUTPUTparameter_name datatype=Default OUTPUT 第28頁(yè)/共39頁(yè)29例：在Northwind上創(chuàng)建my_procedure3存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程在my_proce

16、dure2的基礎(chǔ)上，使用輸出參數(shù)返回符合要求的訂單的條數(shù)。 USE NorthwindGOCREATE PROC dbo.my_procedure3StartDate DateTime, EndDate DateTime, Country varchar(20)=USA,recordcount int OUTPUTAS第29頁(yè)/共39頁(yè)30IF (StartDate IS NULL or EndDate IS NULL or Country IS NULL)BEGIN RAISERROR(NUL value are invalid,5,5)RETURNEND第30頁(yè)/共39頁(yè)31SELECT

17、* FROM dbo.ordersWHEREWHERE OrderDate BETWEENBETWEEN StartDate ANDAND EndDate AND ShipCountry=CountrySELECT recordcount=ROWCOUNT用來(lái)返回受上一語(yǔ)句影響的行數(shù)的系統(tǒng)變量，在這里我們用它來(lái)返回符合條件訂單的條數(shù) 第31頁(yè)/共39頁(yè)324. 執(zhí)行執(zhí)行帶帶輸出參數(shù)輸出參數(shù)的存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程 為了接收存儲(chǔ)過(guò)程的返回值，為了接收存儲(chǔ)過(guò)程的返回值，必須聲明作為輸出的傳遞參數(shù)必須聲明作為輸出的傳遞參數(shù)在在EXECUTEEXECUTE語(yǔ)句中指定語(yǔ)句中指定OUTPUTOUTPUT第32

18、頁(yè)/共39頁(yè)33例：例：執(zhí)行執(zhí)行my_procedure3存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程，返回在返回在1997.7.1與與1998.2.1之間、目之間、目的地為的地為Germany的記錄的條數(shù)的記錄的條數(shù)USE NorthwindGODECLAREDECLARE recordnumber intEXECEXEC my_procedure37/1/1997,2/1/1998,Germany, recordnumber OUTPUTPRINTPRINT The order count is:+ str(recordnumber)第33頁(yè)/共39頁(yè)345.5.存儲(chǔ)過(guò)程的重編譯處理存儲(chǔ)過(guò)程的重編譯處理 存儲(chǔ)過(guò)程的

19、處理存儲(chǔ)過(guò)程的處理 SQLSQL Server在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需進(jìn)行語(yǔ)法檢查，若存在語(yǔ)法需進(jìn)行語(yǔ)法檢查，若存在語(yǔ)法錯(cuò)誤，將返回錯(cuò)誤，并不創(chuàng)建錯(cuò)誤，將返回錯(cuò)誤，并不創(chuàng)建該存儲(chǔ)過(guò)程；若語(yǔ)法正確，則該存儲(chǔ)過(guò)程；若語(yǔ)法正確，則存儲(chǔ)過(guò)程的文本將存儲(chǔ)在存儲(chǔ)過(guò)程的文本將存儲(chǔ)在syscommentssyscomments系統(tǒng)表中系統(tǒng)表中 第34頁(yè)/共39頁(yè)35存儲(chǔ)過(guò)程的重編譯處理存儲(chǔ)過(guò)程的重編譯處理 三三種種方方法法建立存儲(chǔ)過(guò)程時(shí)設(shè)定重編譯選項(xiàng)建立存儲(chǔ)過(guò)程時(shí)設(shè)定重編譯選項(xiàng)CREATE PROCEDURE WITH RECOMPILE CREATE PROCEDURE WITH RECOMP

20、ILE 在執(zhí)行存儲(chǔ)過(guò)程時(shí)設(shè)定重編譯選項(xiàng)在執(zhí)行存儲(chǔ)過(guò)程時(shí)設(shè)定重編譯選項(xiàng)EXECUTEEXECUTE PROCEDURE_NAMEPROCEDURE_NAME PARAMETER WITH RECOMPILE PARAMETER WITH RECOMPILE 通過(guò)系統(tǒng)存儲(chǔ)過(guò)程設(shè)定重編譯選項(xiàng)通過(guò)系統(tǒng)存儲(chǔ)過(guò)程設(shè)定重編譯選項(xiàng)EXEC sp_recompile OBJECTEXEC sp_recompile OBJECT 第35頁(yè)/共39頁(yè)367.3修改和刪除存儲(chǔ)過(guò)程修改和刪除存儲(chǔ)過(guò)程 7.3.1 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程 ALTER PROC procedure_name;numberALTER PRO

21、C procedure_name;number parameter data_typeparameter data_typeVARYING=defaultOUTPUT,VARYING=defaultOUTPUT,nnWITH RECOMPILE|ENCRYPTION|WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYTION RECOMPILE,ENCRYTIONFOR REPLICATIONFOR REPLICATIONAS AS Sql_statement,.nSql_statement,.n 第36頁(yè)/共39頁(yè)37例：例：修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程my_p

22、rocedure1,my_procedure1,返回返回1998.1.1(含含)以后的、目的地為美國(guó)的以后的、目的地為美國(guó)的, CustomerIDCustomerID, OrderDateOrderDate, ShipCountryShipCountry, OrderIDOrderID字段字段, 并指定并指定重編譯重編譯和和加密加密選項(xiàng)選項(xiàng)USE NorthwindgoALTERALTER PROCPROC dbo.my_procedure1WITHWITH RECOMPILERECOMPILE, ENCRYPTIONENCRYPTIONAS SELECTSELECT OrderIDOrderID，CustomerIDCustomerID，OrderDateOrderDate，ShipCountry FROMShipCountry FROM dbo.ordersWHEREWHERE OrderDate=1/1/1998 AND ShipCountry=USA第37頁(yè)/共39頁(yè)387.3.2 7.3.2 刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程 DROP PROCEDURE procedure_nameDROP PROCEDURE procedure_name第38頁(yè)/共39頁(yè)39感謝您的觀看。第39頁(yè)/共39頁(yè)