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

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

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

2、存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程本章主要內(nèi)容第3頁/共39頁4系統(tǒng)存儲(chǔ)過程：系統(tǒng)存儲(chǔ)過程：存儲(chǔ)在存儲(chǔ)在mastermaster數(shù)據(jù)庫中，由前綴數(shù)據(jù)庫中，由前綴spsp標(biāo)識(shí)標(biāo)識(shí)作用：作用：從系統(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)存儲(chǔ)過絕大部分的系統(tǒng)存儲(chǔ)過程可以在任何數(shù)據(jù)庫中執(zhí)行程可以在任何數(shù)據(jù)庫中執(zhí)行 第4頁/共39頁5本地存儲(chǔ)過程：本地存儲(chǔ)過程：這是用戶在獨(dú)立的這是用戶在獨(dú)立的用戶數(shù)據(jù)庫中為了完成某一特定功用戶數(shù)據(jù)庫中為了完成

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

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

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

6、能模塊模塊，將來可以在其他的存儲(chǔ)，將來可以在其他的存儲(chǔ)過程中引用該存儲(chǔ)過程，從而過程中引用該存儲(chǔ)過程，從而實(shí)現(xiàn)代碼的重用性，加快應(yīng)用實(shí)現(xiàn)代碼的重用性，加快應(yīng)用的開發(fā)速度，提高開發(fā)的質(zhì)量的開發(fā)速度，提高開發(fā)的質(zhì)量和效率和效率 第10頁/共39頁11存儲(chǔ)過程中包含的輸入/出參數(shù) 7.2 創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程 7.2.17.2.1創(chuàng)建簡單的存儲(chǔ)過程創(chuàng)建簡單的存儲(chǔ)過程 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ǔ)過程中需執(zhí)行的操作 第11頁/共39頁12例：在My_DB1上新建my_procedure1存儲(chǔ)過程，以返回所有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)建存儲(chǔ)過程的步驟及注意事項(xiàng)創(chuàng)建存儲(chǔ)過程的步驟及注意事項(xiàng) 不能將CREATE PROCEDURE語句與其它SQL語句組合到單個(gè)批處理中創(chuàng)建存儲(chǔ)過程是有權(quán)限的，其默認(rèn)權(quán)限為dbo，其他用戶若要獲得創(chuàng)建存儲(chǔ)過程的權(quán)限，要由dbo授權(quán)第13頁/共39頁14存儲(chǔ)過程是存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象數(shù)據(jù)庫對(duì)

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

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

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

12、間類型的輸入?yún)?shù)和一個(gè)字符型輸入?yún)?shù)，其中字符型輸入?yún)?shù)指定的默認(rèn)值為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、返回用戶定義的錯(cuò)誤信息并設(shè)系統(tǒng)標(biāo)志 第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ù)的存儲(chǔ)過程的存儲(chǔ)過程 使用參數(shù)名傳送參數(shù)值使用參數(shù)名傳送參數(shù)值按位置傳送參數(shù)值按位置傳送參數(shù)值兩種方法由如下語句給出傳遞值 parameter_name=value不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值 第25頁/共39頁26例：例：使用使用參數(shù)名參數(shù)名傳送參數(shù)值的方法，傳送參數(shù)值的方法，通過

14、通過my_procedure2存儲(chǔ)過程，返存儲(chǔ)過程，返回所有日期在回所有日期在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存儲(chǔ)過程，返存儲(chǔ)過程，返回所有日期在回所有日期在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ù)的存儲(chǔ)過程的存儲(chǔ)過程 在創(chuàng)建存儲(chǔ)過程的語句中定義輸出在創(chuàng)建存儲(chǔ)過程的語句中定義輸出參數(shù)可以實(shí)現(xiàn)從存儲(chǔ)過程中返回值參數(shù)可以實(shí)現(xiàn)從存儲(chǔ)過程中返回值為了使用輸出參數(shù)。為了使用輸出參數(shù)。在在CREATECREATE PROCEDUREPROCEDURE中指定中指定OUTPUTOUTPUTparameter_name datatype=Default OUTPUT 第28頁/共39頁29例：在Northwind上創(chuàng)建my_procedure3存儲(chǔ)過程，該存儲(chǔ)過程在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ù)的存儲(chǔ)過程的存儲(chǔ)過程 為了接收存儲(chǔ)過程的返回值，為了接收存儲(chǔ)過程的返回值，必須聲明作為輸出的傳遞參數(shù)必須聲明作為輸出的傳遞參數(shù)在在EXECUTEEXECUTE語句中指定語句中指定OUTPUTOUTPUT第32

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

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

20、ILE 在執(zhí)行存儲(chǔ)過程時(shí)設(shè)定重編譯選項(xiàng)在執(zhí)行存儲(chǔ)過程時(shí)設(shè)定重編譯選項(xiàng)EXECUTEEXECUTE PROCEDURE_NAMEPROCEDURE_NAME PARAMETER WITH RECOMPILE PARAMETER WITH RECOMPILE 通過系統(tǒng)存儲(chǔ)過程設(shè)定重編譯選項(xiàng)通過系統(tǒng)存儲(chǔ)過程設(shè)定重編譯選項(xiàng)EXEC sp_recompile OBJECTEXEC sp_recompile OBJECT 第35頁/共39頁367.3修改和刪除存儲(chǔ)過程修改和刪除存儲(chǔ)過程 7.3.1 修改存儲(chǔ)過程修改存儲(chǔ)過程 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頁/共39頁37例：例：修改存儲(chǔ)過程修改存儲(chǔ)過程my_p

22、rocedure1,my_procedure1,返回返回1998.1.1(含含)以后的、目的地為美國的以后的、目的地為美國的, 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頁/共39頁387.3.2 7.3.2 刪除存儲(chǔ)過程刪除存儲(chǔ)過程 DROP PROCEDURE procedure_nameDROP PROCEDURE procedure_name第38頁/共39頁39感謝您的觀看。第39頁/共39頁