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

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

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

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

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

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

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

7、 data_typeVARYING=defaultOUTPUTVARYING=defaultOUTPUT，nnWITHRECOMPILE|ENCRYPTION|WITHRECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTIONRECOMPILE, ENCRYPTIONFOR REPLICATIONFOR REPLICATIONAS sql_statements AS sql_statements 重新編譯加密在存儲過程中需執(zhí)行的操作 第11頁/共39頁12例：在My_DB1上新建my_procedure1存儲過程，以返回所有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頁/共39頁13創(chuàng)建存儲過程的步驟及注意事項創(chuàng)建存儲過程的步驟及注意事項 不能將CREATE PROCEDURE語句與其它SQL語句組合到單個批處理中創(chuàng)建存儲過程是有權(quán)限的，其默認權(quán)限為dbo，其他用戶若要獲得創(chuàng)建存儲過程的權(quán)限，要由dbo授權(quán)第13頁/共39頁14存儲過程是存儲過程是數(shù)據(jù)庫對象數(shù)據(jù)庫對

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

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

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

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

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

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

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

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

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

18、頁/共39頁33例：例：執(zhí)行執(zhí)行my_procedure3存儲過程，存儲過程，返回在返回在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頁/共39頁345.5.存儲過程的重編譯處理存儲過程的重編譯處理 存儲過程的

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

20、ILE 在執(zhí)行存儲過程時設(shè)定重編譯選項在執(zhí)行存儲過程時設(shè)定重編譯選項EXECUTEEXECUTE PROCEDURE_NAMEPROCEDURE_NAME PARAMETER WITH RECOMPILE PARAMETER WITH RECOMPILE 通過系統(tǒng)存儲過程設(shè)定重編譯選項通過系統(tǒng)存儲過程設(shè)定重編譯選項EXEC sp_recompile OBJECTEXEC sp_recompile OBJECT 第35頁/共39頁367.3修改和刪除存儲過程修改和刪除存儲過程 7.3.1 修改存儲過程修改存儲過程 ALTER PROC procedure_name;numberALTER PROC procedure_name;number parameter data_typeparameter data_typeVARYING=defaultOUTPUT,VARYING=defaultOUTPUT,nnWITH RECOMPILE|ENCRYPTION|WITH RECOMPILE|ENCRYPTION| RECOMPILE,E