sql server數(shù)據(jù)庫應(yīng)用基礎(chǔ)教程22604-第八章

1第八章存儲過程及其應(yīng)用28.1存儲過程概述

存儲過程(storedprocedure)是存放在服務(wù)器上的預先編譯好的SQL語句,用于完成某項任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用

3存儲過程類型系統(tǒng)存儲過程本地存儲過程臨時存儲過程遠程存儲過程擴展存儲過程本章主要內(nèi)容4系統(tǒng)存儲過程：存儲在master數(shù)據(jù)庫中，由前綴sp標識作用：從系統(tǒng)表中獲取信息，允許系統(tǒng)管理員在沒有直接更新底層表的許可權(quán)下執(zhí)行更新數(shù)據(jù)庫中系統(tǒng)表的數(shù)據(jù)庫管理工作。絕大部分的系統(tǒng)存儲過程可以在任何數(shù)據(jù)庫中執(zhí)行5本地存儲過程：這是用戶在獨立的用戶數(shù)據(jù)庫中為了完成某一特定功能而編寫的存儲過程臨時存儲過程：它與臨時表類似，通常又分為本地和全局臨時存儲過程兩種，當臨時存儲過程為本地時，其名字以符號#開始，為全局時，以符號##開始6遠程存儲過程：遠程存儲過程在分布式查詢中使用擴展存儲過程：使SQL

Server可動態(tài)裝載并執(zhí)行DLL。這樣用戶可使用象C這樣的編程語言創(chuàng)建自己的外部例程擴展存儲過程由前綴xp標識7存儲過程的優(yōu)點

提供了安全機制改進了執(zhí)行性能減少了網(wǎng)絡(luò)流量允許模塊化程序設(shè)計8

存儲過程提供的安全機制可以讓用戶通過存儲過程操作數(shù)據(jù)庫中的數(shù)據(jù)，而不讓用戶直接操作于存儲過程相關(guān)的表，從而保證數(shù)據(jù)庫中數(shù)據(jù)的安全性9存儲過程在第二次執(zhí)行時，無需預編譯，從而改進系統(tǒng)的執(zhí)行性能存儲過程是存放在服務(wù)器上的預先編譯好的單條或多條SQL語句并在服務(wù)器上運行，用戶無需在網(wǎng)絡(luò)上發(fā)送上百個SQL語句，或是將眾多數(shù)據(jù)從服務(wù)器下載至客戶端后再進行處理，從而大大減少了網(wǎng)絡(luò)負載

10增強代碼的可重用性，提高開發(fā)效率。存儲過程可以視為為完成某特定功能而編寫的功能模塊，將來可以在其他的存儲過程中引用該存儲過程，從而實現(xiàn)代碼的重用性，加快應(yīng)用的開發(fā)速度，提高開發(fā)的質(zhì)量和效率11存儲過程中包含的輸入/出參數(shù)8.2創(chuàng)建存儲過程8.2.1創(chuàng)建簡單的存儲過程

CREATEPROC[EDURE][owner.]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statements重新編譯加密在存儲過程中需執(zhí)行的操作12例：在My_DB1上新建my_procedure1存儲過程，以返回所有diagrm_id>=100和principal>=200的信息。USEMy_DB1goCREATEPROCdbo.my_procedure1ASSELECT*FROMdbo.sysdiagrmsWHEREdiagrm_id>=100

ANDprincipal>=200Go13創(chuàng)建存儲過程的步驟及注意事項不能將CREATE

PROCEDURE語句與其它SQL語句組合到單個批處理中創(chuàng)建存儲過程是有權(quán)限的，其默認權(quán)限為dbo，其他用戶若要獲得創(chuàng)建存儲過程的權(quán)限，要由dbo授權(quán)14存儲過程是數(shù)據(jù)庫對象，在命名用戶自定義的存儲過程時應(yīng)避免使用sp前綴，以免和系統(tǒng)存儲過程混淆盡量不要使用臨時存儲過程，以避免tempdb上造成的對系統(tǒng)表資源的爭奪，從而影響系統(tǒng)的執(zhí)行性能15

存儲過程最大尺寸被限制為128MB，存儲過程最多允許嵌套至32級16例：編寫指令執(zhí)行my_procedure1存儲過程

USENorthwind EXECmy_procedure1 GO17查看存儲過程信息

sysobjectsmentssysdependssp_stored_procedures可以使用如下命令：18例：編寫SQL指令查看創(chuàng)建存儲過程my_procedure1的相關(guān)代碼信息

USENorthwindEXECsp_helptextmy_procedure1GO198.2.2創(chuàng)建和執(zhí)行含參數(shù)存儲過程通過使用參數(shù)，可以多次使用同一存儲過程并按指定要求查找數(shù)據(jù)庫201.

創(chuàng)建帶輸入?yún)?shù)的存儲過程輸入?yún)?shù)是指由調(diào)用程序向存儲過程傳遞的參數(shù)，為了定義接受輸入?yún)?shù)的存儲過程，需要在CREATEPROCEDURE語句中聲明一個或多個變量作為參數(shù)。如：@parameter_namedatatype[=default]必須是常量或NULL21例：在Northwind庫上創(chuàng)建存儲過程my_procedure2的，其中定義了兩個時間類型的輸入?yún)?shù)和一個字符型輸入?yún)?shù)，返回所有日期在兩輸入時間之間、目的地與輸入的字符型參數(shù)相同的訂單，其中字符型輸入?yún)?shù)指定的默認值為’USA’。22USENorthwindgoCREATE

PROCdbo.my_procedure2@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA'AS23IF(@StartDateISNULL or @EndDateISNULL or @CountryISNULL)BEGINRAISERROR('NULLvalueareinvalid',5,5)RETURNEND

返回用戶定義的錯誤信息并設(shè)系統(tǒng)標志24SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDshipCountry=@Countrygo252.執(zhí)行帶輸入?yún)?shù)的存儲過程使用參數(shù)名傳送參數(shù)值按位置傳送參數(shù)值兩種方法由如下語句給出傳遞值@parameter_name=value不參照被傳遞的參數(shù)而直接給出參數(shù)的傳遞值26例：使用參數(shù)名傳送參數(shù)值的方法，通過my_procedure2存儲過程，返回所有日期在1997.7.1與1998.2.1日，目的地為’USA’的訂單記錄。

USENorthwind GO EXECmy_procedure2 @EndDate=’2/1/1998’, @StartDate=’7/1/1997’27例：使用按位置傳送參數(shù)值的方法，通過my_procedure2存儲過程，返回所有日期在1997.7.1與1998.2.1日，目的地為’USA’的訂單記錄。USENorthwindGOEXECmy_procedure2’7/1/1997’，’2/1/1998’,’USA’283.

創(chuàng)建帶輸出參數(shù)的存儲過程在創(chuàng)建存儲過程的語句中定義輸出參數(shù)可以實現(xiàn)從存儲過程中返回值為了使用輸出參數(shù)。在CREATE

PROCEDURE中指定OUTPUT@parameter_name

datatype[=Default]

OUTPUT29例：在Northwind上創(chuàng)建my_procedure3存儲過程，該存儲過程在my_procedure2的基礎(chǔ)上，使用輸出參數(shù)返回符合要求的訂單的條數(shù)。

USENorthwindGOCREATEPROCdbo.my_procedure3@StartDateDateTime,@EndDateDateTime,@Countryvarchar(20)='USA',@recordcountintOUTPUTAS30IF (@StartDateISNULLor @EndDateISNULLor @CountryISNULL)BEGINRAISERROR('NULvalueareinvalid',5,5)RETURNEND31SELECT*FROMdbo.ordersWHEREOrderDateBETWEEN@StartDateAND@EndDateANDShipCountry=@CountrySELECT@recordcount=@@ROWCOUNT用來返回受上一語句影響的行數(shù)的系統(tǒng)變量，在這里我們用它來返回符合條件訂單的條數(shù)324.

執(zhí)行帶輸出參數(shù)的存儲過程為了接收存儲過程的返回值，必須聲明作為輸出的傳遞參數(shù)在EXECUTE語句中指定OUTPUT33例：執(zhí)行my_procedure3存儲過程，返回在1997.7.1與1998.2.1之間、目的地為’Germany’的記錄的條數(shù)USENorthwindGODECLARE@recordnumberintEXECmy_procedure3'7/1/1997','2/1/1998','Germany',@recordnumberOUTPUTPRINT'Theordercountis:'+str(@recordnumber)345.存儲過程的重編譯處理

存儲過程的處理SQLServer在創(chuàng)建存儲過程時，需進行語法檢查，若存在語法錯誤，將返回錯誤，并不創(chuàng)建該存儲過程；若語法正確，則存儲過程的文本將存儲在ments系統(tǒng)表中35存儲過程的重編譯處理三種方法建立存儲過程時設(shè)定重編譯選項CREATEPROCEDURE[WITHPILE]在執(zhí)行存儲過程時設(shè)定重編譯選項EXECUTEPROCEDURE_NAME[PARAMETER][WITHPILE]通過系統(tǒng)存儲過程設(shè)定重編譯選項EXECpileOBJECT368.3修改和刪除存儲過程8.3.1修改存儲過程

ALTERPROCprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{PILE|ENCRYPTION| PILE,ENCRYTION}][FORREPLICATION]ASSql_statement[,..n]37例：修改存儲過程my_procedure1,返回1998.1.1(含)以后的、目的地為美國的,CustomerID,OrderDate,ShipCountry,OrderID字段,并指定重編譯和加密選項USENorthwindgoALTERPROCdbo.my_procedure1WITHPILE,ENCRYPTIONASSELECTOrderID，CustomerID，OrderDate，ShipCountryFROMdbo.ordersWHEREOrderDate>='1/1/1998'ANDShipCountry='USA'388.3.2刪除存儲過程

DROPPROC[EDURE]procedure_name398.4系統(tǒng)存儲過程和擴展存儲過程8.4.1系統(tǒng)存儲過程sp_helpsql：顯示關(guān)于SQL語句、存儲過程和其他主題的信息sp_help：提供關(guān)于存儲過程或其他 數(shù)據(jù)庫對象的報告sp_helptext：顯示存儲過程和其他對 象的文本sp_depends：列舉引用或依賴指定對 象的所有存儲過程40角色定義了一組Windows2003用戶帳戶和組，是用來實現(xiàn)最終用戶安全性的例：利用sp_addgroup命令在當前數(shù)據(jù)庫中建立一個角色my_groupusemas