數(shù)據(jù)庫應(yīng)用基礎(chǔ)第七章存儲(chǔ)過程.ppt

1,第七章 存儲(chǔ)過程,2,7.1 存儲(chǔ)過程概述 7.2 創(chuàng)建存儲(chǔ)過程 7.3修改和刪除存儲(chǔ)過程,本章主要內(nèi)容,3,7.1 存儲(chǔ)過程概述,存儲(chǔ)過程(stored procedure)是存放在服務(wù)器上的預(yù)先編譯好的SQL語句,用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用,4,存儲(chǔ)過程類型,系統(tǒng)存儲(chǔ)過程 本地存儲(chǔ)過程 臨時(shí)存儲(chǔ)過程 遠(yuǎn)程存儲(chǔ)過程 擴(kuò)展存儲(chǔ)過程,本章主要內(nèi)容,5,系統(tǒng)存儲(chǔ)過程：存儲(chǔ)在master數(shù)據(jù)庫中，由前綴sp標(biāo)識(shí) 作用：從系統(tǒng)表中獲取信息，允許系統(tǒng)管理員在沒有直接更新底層表的許可權(quán)下執(zhí)行更新數(shù)據(jù)庫中系統(tǒng)表的數(shù)據(jù)庫管理工作。絕大部分的系統(tǒng)存儲(chǔ)過程可以在任何數(shù)據(jù)庫中執(zhí)行,6,本地存儲(chǔ)過程：這是用戶在獨(dú)立的用戶數(shù)據(jù)庫中為了完成某一特定功能而編寫的存儲(chǔ)過程,臨時(shí)存儲(chǔ)過程：它與臨時(shí)表類似，通常又分為本地和全局臨時(shí)存儲(chǔ)過程兩種，當(dāng)臨時(shí)存儲(chǔ)過程為本地時(shí)，其名字以符號(hào)#開始，為全局時(shí)，以符號(hào)#開始,7,遠(yuǎn)程存儲(chǔ)過程：遠(yuǎn)程存儲(chǔ)過程在分布式查詢中使用,擴(kuò)展存儲(chǔ)過程：使SQL Server可動(dòng)態(tài)裝載并執(zhí)行 DLL。這樣用戶可使用象 C 這樣的編程語言創(chuàng)建自己的外部例程 擴(kuò)展存儲(chǔ)過程由前綴xp標(biāo)識(shí),8,存儲(chǔ)過程的優(yōu)點(diǎn),提供了安全機(jī)制 改進(jìn)了執(zhí)行性能 減少了網(wǎng)絡(luò)流量 允許模塊化程序設(shè)計(jì),9,存儲(chǔ)過程提供的安全機(jī)制可以讓用戶通過存儲(chǔ)過程操作數(shù)據(jù)庫中的數(shù)據(jù)，而不讓用戶直接操作于存儲(chǔ)過程相關(guān)的表，從而保證數(shù)據(jù)庫中數(shù)據(jù)的安全性,10,存儲(chǔ)過程在第二次執(zhí)行時(shí)，無需預(yù)編譯，從而改進(jìn)系統(tǒng)的執(zhí)行性能,存儲(chǔ)過程是存放在服務(wù)器上的預(yù)先編譯好的單條或多條SQL語句并在服務(wù)器上運(yùn)行，用戶無需在網(wǎng)絡(luò)上發(fā)送上百個(gè)SQL語句，或是將眾多數(shù)據(jù)從服務(wù)器下載至客戶端后再進(jìn)行處理，從而大大減少了網(wǎng)絡(luò)負(fù)載,11,增強(qiáng)代碼的可重用性，提高開發(fā)效率。存儲(chǔ)過程可以視為為完成某特定功能而編寫的功能模塊，將來可以在其他的存儲(chǔ)過程中引用該存儲(chǔ)過程，從而實(shí)現(xiàn)代碼的重用性，加快應(yīng)用的開發(fā)速度，提高開發(fā)的質(zhì)量和效率,12,存儲(chǔ)過程中包含的輸入/出參數(shù),7.2 創(chuàng)建存儲(chǔ)過程,7.2.1創(chuàng)建簡單的存儲(chǔ)過程,CREATE PROCEDURE cedure_name;number parameter data_type VARYING=defaultOUTPUT，n WITHRECOMPILE|ENCRYPTION| RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statements,重新編譯,加密,在存儲(chǔ)過程中需執(zhí)行的操作,13,例：在My_DB1上新建my_procedure1存儲(chǔ)過程，以返回所有diagrm_id=100和principal=200的信息。 USE My_DB1 go CREATE PROC dbo.my_procedure1 AS SELECT * FROM dbo.sysdiagrms WHERE diagrm_id=100 AND principal=200 Go,14,創(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),15,存儲(chǔ)過程是數(shù)據(jù)庫對(duì)象，在命名用戶自定義的存儲(chǔ)過程時(shí)應(yīng)避免使用sp前綴，以免和系統(tǒng)存儲(chǔ)過程混淆,盡量不要使用臨時(shí)存儲(chǔ)過程，以避免tempdb上造成的對(duì)系統(tǒng)表資源的爭奪，從而影響系統(tǒng)的執(zhí)行性能,16,存儲(chǔ)過程最大尺寸被限制為128 MB，存儲(chǔ)過程最多允許嵌套至32級(jí),17,例：編寫指令執(zhí)行my_procedure1存儲(chǔ)過程 USE Northwind EXEC my_procedure1 GO,18,查看存儲(chǔ)過程信息,sysobjects syscomments sysdepends sp_stored_procedures,可以使用如下命令：,19,例：編寫SQL指令查看創(chuàng)建存儲(chǔ)過程my_procedure1的相關(guān)代碼信息 USE Northwind EXEC sp_helptext my_procedure1 GO,20,7.2.2 創(chuàng)建和執(zhí)行含參數(shù)存儲(chǔ)過程,通過使用參數(shù)，可以多次使用同一存儲(chǔ)過程并按指定要求查找數(shù)據(jù)庫,21,1. 創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過程,輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過程傳遞的參數(shù)，為了定義接受輸入?yún)?shù)的存儲(chǔ)過程，需要在CREATE PROCEDURE語句中聲明一個(gè)或多個(gè)變量作為參數(shù)。 如： parameter_name datatype=default,必須是常量或NULL,22,例：在Northwind庫上創(chuàng)建存儲(chǔ)過程my_procedure2的，其中定義了兩個(gè)時(shí)間類型的輸入?yún)?shù)和一個(gè)字符型輸入?yún)?shù)，返回所有日期在兩輸入時(shí)間之間、目的地與輸入的字符型參數(shù)相同的訂單，其中字符型輸入?yún)?shù)指定的默認(rèn)值為USA。,23,USE Northwind go CREATE PROC dbo.my_procedure2 StartDate DateTime, EndDate DateTime, Country varchar(20)=USA AS,24,IF (StartDate IS NULL or EndDate IS NULL or Country IS NULL) BEGIN RAISERROR (NULL value are invalid,5,5) RETURN END,返回用戶定義的錯(cuò)誤信息并設(shè)系統(tǒng)標(biāo)志,25,SELECT * FROM dbo.orders WHERE OrderDate BETWEEN StartDate AND EndDate AND shipCountry=Country go,26,2. 執(zhí)行帶輸入?yún)?shù)的存儲(chǔ)過程,使用參數(shù)名傳送參數(shù)值 按位置傳送參數(shù)值,兩種方法,由如下語句給出傳遞值 parameter_name=value,不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值,27,例：使用參數(shù)名傳送參數(shù)值的方法，通過my_procedure2存儲(chǔ)過程，返回所有日期在1997.7.1與1998.2.1日，目的地為USA的訂單記錄。 USE Northwind GO EXEC my_procedure2 EndDate=2/1/1998, StartDate=7/1/1997,28,例：使用按位置傳送參數(shù)值的方法，通過my_procedure2存儲(chǔ)過程，返回所有日期在1997.7.1與1998.2.1日，目的地為USA的訂單記錄。 USE Northwind GO EXEC my_procedure2 7/1/1997，2/1/1998,USA,29,3. 創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過程,在創(chuàng)建存儲(chǔ)過程的語句中定義輸出參數(shù)可以實(shí)現(xiàn)從存儲(chǔ)過程中返回值為了使用輸出參數(shù)。 在CREATE PROCEDURE中指定OUTPUT parameter_name datatype=Default OUTPUT,30,例：在Northwind上創(chuàng)建my_procedure3存儲(chǔ)過程，該存儲(chǔ)過程在my_procedure2的基礎(chǔ)上，使用輸出參數(shù)返回符合要求的訂單的條數(shù)。 USE Northwind GO CREATE PROC dbo.my_procedure3 StartDate DateTime, EndDate DateTime, Country varchar(20)=USA, recordcount int OUTPUT AS,31,IF (StartDate IS NULL or EndDate IS NULL or Country IS NULL) BEGIN RAISERROR(NUL value are invalid,5,5) RETURN END,32,SELECT * FROM dbo.orders WHERE OrderDate BETWEEN StartDate AND EndDate AND ShipCountry=Country SELECT recordcount=ROWCOUNT,用來返回受上一語句影響的行數(shù)的系統(tǒng)變量，在這里我們用它來返回符合條件訂單的條數(shù),33,4. 執(zhí)行帶輸出參數(shù)的存儲(chǔ)過程,為了接收存儲(chǔ)過程的返回值，必須聲明作為輸出的傳遞參數(shù) 在EXECUTE語句中指定OUTPUT,34,例：執(zhí)行my_procedure3存儲(chǔ)過程，返回在1997.7.1與1998.2.1之間、目的地為Germany的記錄的條數(shù) USE Northwind GO DECLARE recordnumber int EXEC my_procedure3 7/1/1997,2/1/1998,Germany, recordnumber OUTPUT PRINT The order count is:+ str(recordnumber),35,5.存儲(chǔ)過程的重編譯處理,存儲(chǔ)過程的處理,SQL Server在創(chuàng)建存儲(chǔ)過程時(shí)，需進(jìn)行語法檢查，若存在語法錯(cuò)誤，將返回錯(cuò)誤，并不創(chuàng)建該存儲(chǔ)過程；若語法正確，則存儲(chǔ)過程的文本將存儲(chǔ)在syscomments系統(tǒng)表中,36,存儲(chǔ)過程的重編譯處理,三種方法,建立存儲(chǔ)過程時(shí)設(shè)定重編譯選項(xiàng) CREATE PROCEDURE WITH RECOMPILE,在執(zhí)行存儲(chǔ)過程時(shí)設(shè)定重編譯選項(xiàng) EXECUTE PROCEDURE_NAME PARAMETER WITH RECOMPILE,通過系統(tǒng)存儲(chǔ)過程設(shè)定重編譯選項(xiàng) EXEC sp_recompile OBJECT,37,7.3修改和刪除存儲(chǔ)過程,7.3.1 修改存儲(chǔ)過程,ALTER PROC procedure_name;number parameter data_type VARYING=defaultOUTPUT,n WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYTION FOR REPLICATION AS Sql_statement,n,38,例：修改存儲(chǔ)過程my_procedure1,返回1998.1.1(含)以后的、目的地為美國的, CustomerID, OrderDate, ShipCountry, OrderID字段, 并指定重編譯和加密選項(xiàng) USE No