SQLServer數(shù)據(jù)庫(kù)技術(shù)-07存儲(chǔ)過(guò)程

SQLServer數(shù)據(jù)庫(kù)技術(shù)

存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程簡(jiǎn)介創(chuàng)立存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程設(shè)計(jì)存儲(chǔ)過(guò)程中的技巧常用的系統(tǒng)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程StoredProcedure存儲(chǔ)過(guò)程是一組預(yù)先寫好的能實(shí)現(xiàn)某種功能的T-SQL程序，指定一個(gè)程序名并將其編譯后將其存放在SQLServer中，以后要實(shí)現(xiàn)該功能，可以調(diào)用這個(gè)程序來(lái)完成。用戶可以通過(guò)存儲(chǔ)過(guò)程的名字并給出參數(shù)〔如果該存儲(chǔ)過(guò)程有參數(shù)〕來(lái)執(zhí)行它。存儲(chǔ)過(guò)程優(yōu)點(diǎn)執(zhí)行速度快、效率高模塊式編程減少網(wǎng)絡(luò)流量平安性存儲(chǔ)過(guò)程的分類系統(tǒng)存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程〔SQLServer2008〕用戶自定義存儲(chǔ)過(guò)程創(chuàng)立存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程輸入?yún)?shù)和輸出參數(shù)在存儲(chǔ)過(guò)程中執(zhí)行的T-SQL語(yǔ)句返回的狀態(tài)值，指明執(zhí)行存儲(chǔ)過(guò)程是成功還是失敗CreateProcedureCREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number]--架構(gòu)名。存儲(chǔ)過(guò)程名[；分組][{@parameter[type_schema_name.]data_type}--參數(shù)[VARYING][=default][[OUT[PUT]--作為游標(biāo)輸出參數(shù)][,...n][WITH<procedure_option>[,...n][FORREPLICATION]--不能在訂閱效勞器上執(zhí)行為復(fù)制創(chuàng)立的存儲(chǔ)過(guò)程AS{<sql_statement>[;][...n]--存儲(chǔ)過(guò)程語(yǔ)句|<method_specifier>}[;]<procedure_option>::=[ENCRYPTION]--加密[RECOMPILE]--不預(yù)編譯[EXECUTE_AS_Clause]--執(zhí)行存儲(chǔ)過(guò)程的平安上下文<sql_statement>::={[BEGIN]statements[END]}--存儲(chǔ)過(guò)程語(yǔ)句<method_specifier>::=EXTERNALNAMEassembly_name.class_name.method_name--指定程序集方法例：創(chuàng)立存儲(chǔ)過(guò)程創(chuàng)立一個(gè)存儲(chǔ)過(guò)程，查看類別表里的所有記錄，并運(yùn)行該存儲(chǔ)過(guò)程CREATEPROCpr_例一 AS SELECT*FROMCategoriesGOEXECpr_例一SQLServerManagementStudio修改存儲(chǔ)過(guò)程ALTER{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITH<procedure_option>[,...n]][FORREPLICATION]AS{<sql_statement>[...n]|<method_specifier>}<procedure_option>::=[ENCRYPTION][RECOMPILE][EXECUTE_AS_Clause]<sql_statement>::={[BEGIN]statements[END]}<method_specifier>::=EXTERNALNAME例：修改存儲(chǔ)過(guò)程，使其按照類別名排序ALTERPROCpr_例一

AS SELECT*FROMCategories

ORDERBYCategoryName修改存儲(chǔ)過(guò)程通過(guò)SQLServerManagementStudio修改存儲(chǔ)過(guò)程將SELECT*FROMProductsWHERECategoryID=@p1改為SELECT*FROMProductsWHERECategoryID=@p1OrderByProductName執(zhí)行存儲(chǔ)過(guò)程ExecExecdbo.SearchProductsByCategoryID假設(shè)輸入?yún)?shù)無(wú)默認(rèn)值時(shí)會(huì)出錯(cuò)Execdbo.SearchProductsByCategoryID2Execdbo.SearchProductsByCategoryID@p1=2SQLServerManagemetnStudio存儲(chǔ)過(guò)程->右鍵->執(zhí)行存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程DROPPROCdbo.SearchProductsByCategoryIDSQLServerManagementStudio設(shè)計(jì)存儲(chǔ)過(guò)程的技巧不能在存儲(chǔ)過(guò)程中使用的語(yǔ)句CreateaggregateCreatefunctionCreateprocedureCreateruleCreatetriggerCreateviewCreatedefaultCreateschemaSetparseonlySetshowplan_textSetshowplan_allSetshowplan_xmlAlterfunctionAlterprocedureAltertriggerAlterviewUsedatabase_name參數(shù)傳遞的方式存儲(chǔ)過(guò)程可以包含參數(shù)在執(zhí)行存儲(chǔ)過(guò)程時(shí)，如果不指明參數(shù)名稱，那么按存儲(chǔ)過(guò)程定義的參數(shù)次序傳遞如果在存儲(chǔ)過(guò)程里定義了參數(shù)的默認(rèn)值，并且放在最后，那么可以不指定該參數(shù)例：創(chuàng)立帶存儲(chǔ)的存儲(chǔ)過(guò)程創(chuàng)立一個(gè)存儲(chǔ)過(guò)程，輸入類別名稱、單價(jià)、庫(kù)存量和訂購(gòu)量后，可以看到該類型中大于該單價(jià)、庫(kù)存量和訂購(gòu)量的產(chǎn)品CREATEPROCEDUREpr_例六 @類別名稱varchar(15), @單價(jià)money=$10, @庫(kù)存量smallint, @訂購(gòu)量smallint=5ASBEGIN SELECT*FROMProductsJOINCategories ONProducts.Category=Categories.CategoryID WHERE(Categories.CategoryName=@類別名稱) AND(Products.UnitPrice>@單價(jià)) AND(Products.UnitsInStock>@庫(kù)存量) AND(Products.UnitsOnOrder>@訂購(gòu)量)ENDGO按照參數(shù)次序Execpr_例六Seafood,1,1,1不按照參數(shù)順序傳遞參數(shù)，那么要指定參數(shù)名Execpr_例六@單價(jià)=1,@訂購(gòu)量=1,@庫(kù)存量=1,@類別名稱=Seafood使用默認(rèn)值，用default代替Execpr_例六Seafood,default,1,defaultPr_例六存儲(chǔ)過(guò)程的最后一個(gè)參數(shù)定義了默認(rèn)值，所以可以將其省略Execpr_例六Seafood,default,1如果要省略所有帶有默認(rèn)值的參數(shù)，但這些參數(shù)并不是排在最后的，就必須指定參數(shù)名Execpr_例六@類別名稱=Seafood,@庫(kù)存量=1存儲(chǔ)過(guò)程的返回值輸出參數(shù)以輸出參數(shù)的形式向調(diào)用它的過(guò)程返回多個(gè)值狀態(tài)值可以向調(diào)用它的過(guò)程返回狀態(tài)值，說(shuō)明該存儲(chǔ)過(guò)程運(yùn)行成功或失敗返回值在存儲(chǔ)過(guò)程中以”returnn”的形式返回一個(gè)整數(shù)值在存儲(chǔ)過(guò)程中指定一個(gè)output的返回參數(shù)以返回值在存儲(chǔ)過(guò)程中執(zhí)行T-SQL語(yǔ)句返回?cái)?shù)據(jù)集，例如Select語(yǔ)句Returnn例：創(chuàng)立一個(gè)有返回值的存儲(chǔ)過(guò)程創(chuàng)立一個(gè)存儲(chǔ)過(guò)程，返回“Products”表里所有產(chǎn)品的庫(kù)存量CREATEPROCEDUREpr_例七ASBEGIN declare@返回值int SELECT@返回值=sum(UnitsInStock)FROMProducts return@返回值ENDGO執(zhí)行declare@接收值intexec@接收值=pr_例七print@接收值Output返回值也可以用output參數(shù)來(lái)返回CREATEPROCEDUREpr_例七_(dá)2@返回值intoutputASBEGIN SELECT@返回值=sum(UnitsInStock)FROMProductsENDGO接收output的返回值也必須要用變量，如：declare@接收值intexecpr_例七_(dá)2@接收值outputprint@接收值Return返回的是整數(shù)，output可以返回任何數(shù)據(jù)如果讓return返回非整數(shù)，在創(chuàng)立存儲(chǔ)過(guò)程中不會(huì)出錯(cuò)，但是運(yùn)行存儲(chǔ)過(guò)程時(shí)將會(huì)出錯(cuò)。如CREATEPROCEDUREpr_例七_(dá)3ASBEGIN declare@庫(kù)存int SELECT@庫(kù)存=sum(UnitsInStock)FROMProducts return'庫(kù)存量為：'+CAST(@庫(kù)存asvarchar(10))ENDGO再執(zhí)行代碼：declare@接收值intexec@接收值=pr_例七_(dá)3print@接收值使用outputCREATEPROCEDUREpr_例七_(dá)4@返回值varchar(20)outputASBEGIN declare@庫(kù)存int SELECT@庫(kù)存=sum(UnitsInStock)FROMProducts

SET@返回值='庫(kù)存量為：'+CAST(@庫(kù)存asvarchar(10))ENDGOdeclare@接收值varchar(20)execpr_例七_(dá)4@接收值outputprint@接收值在存儲(chǔ)過(guò)程中執(zhí)行T-SQL返回?cái)?shù)據(jù)集直接返回到執(zhí)行存儲(chǔ)過(guò)程的應(yīng)用程序中，無(wú)法在批程序中使用，只能在批程序的結(jié)果中將其顯示出來(lái)例：建立一個(gè)存儲(chǔ)過(guò)程，用于查看某個(gè)類別的所有產(chǎn)品CREATEPROCEDUREpr_例八@類別名稱nvarchar(15)ASBEGIN SELECTProductID,ProductNameFROMProducts

JOINCategoriesONProducts.CategoryID=Products.CategoryID WHERECategories.CategoryName=@類別名稱ENDGOexecpr_例八N‘Seafood'不影響行數(shù)的通知將顯示影響行數(shù)的信息關(guān)閉，以免干擾程序的運(yùn)行，也可以減少帶寬資源的占用關(guān)閉SETNOCOUNTON翻開SETNOCOUNTOFF嵌套調(diào)用存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程中可以執(zhí)行另一個(gè)存儲(chǔ)過(guò)程，這就是嵌套存儲(chǔ)過(guò)程可以多層嵌套，最多可以嵌套到32層，如果超過(guò)32層嵌套將會(huì)導(dǎo)致整個(gè)調(diào)用鏈?zhǔn)≌趫?zhí)行的存儲(chǔ)過(guò)程的當(dāng)前嵌套層數(shù)可以使用@@NESTLEVEL來(lái)查看例：嵌套調(diào)用存儲(chǔ)過(guò)程查看訂購(gòu)量最多的產(chǎn)品的供給商的信息CREATEPROCpr_例九_(tái)查看訂購(gòu)量最多的產(chǎn)品ID@產(chǎn)品IDintoutputAS SELECTTOP1@產(chǎn)品ID=ProductIDFROM[OrderDetails] GROUPBYProductID ORDERBYMAX(Quantity)DESC PRINT'存儲(chǔ)過(guò)程“pr_例九_(tái)查看訂購(gòu)量最多的產(chǎn)品ID”的嵌套層次為：第' +CAST(@@NESTLEVELASVARCHAR(1))+'層'GOCREATEPROCpr_例九_(tái)查看產(chǎn)品的供給商ID@供給商IDintoutputAS DECLARE@產(chǎn)品編號(hào)int execpr_例九_(tái)查看訂購(gòu)量最多的產(chǎn)品ID@產(chǎn)品編號(hào)output SELECT@供給商ID=SupplierIDFROMProducts WHEREProductID=@產(chǎn)品編號(hào) PRINT'存儲(chǔ)過(guò)程“pr_例九_(tái)查看產(chǎn)品的供給商ID”的嵌套層次為：第' +CAST(@@NESTLEVELASVARCHAR(1))+'層'GOCREATEPROCpr_例九_(tái)供給商信息AS DECLARE@供給商編號(hào)int execpr_例九_(tái)查看產(chǎn)品的供給商ID@供給商編號(hào)output SELECT*FROMSuppliers WHERESupplierID=@供給商編號(hào) PRINT'存儲(chǔ)過(guò)程“pr_例九_(tái)供給商信息”的嵌套層次為：第' +CAST(@@NESTLEVELASVARCHAR(1))+'層'GOExecpr_例九_(tái)供給商信息查看存儲(chǔ)過(guò)程的依賴關(guān)系SQLServerManagementStudio加密存儲(chǔ)過(guò)程通過(guò)加密保證平安加密之后的存儲(chǔ)過(guò)程可以正常使用，但是無(wú)法查看該存儲(chǔ)過(guò)程的內(nèi)容，只能通過(guò)Alter語(yǔ)句來(lái)修改例：創(chuàng)立一個(gè)加密的存儲(chǔ)過(guò)程CREATEPROCpr_例十@類別名稱nvarchar(15)WITHENCRYPTIONAS SELECT*FROMCategories WHERECategoryName=@類別名稱GO查看存儲(chǔ)過(guò)程的源代碼Sp_helptext存儲(chǔ)過(guò)程名Sp_helptext不能查看加了密的存儲(chǔ)過(guò)程execsp_helptextpr_例八GOexecsp_helptextpr_例十GO存儲(chǔ)過(guò)程分組可以將多個(gè)存儲(chǔ)過(guò)程放在同一組中，可以執(zhí)行相關(guān)或不相關(guān)的數(shù)據(jù)庫(kù)操作，刪除存儲(chǔ)過(guò)程組時(shí)，將會(huì)把組中的所有存儲(chǔ)過(guò)程都刪除例：創(chuàng)立兩個(gè)存儲(chǔ)過(guò)程，一個(gè)查詢“Categories”表中所有數(shù)據(jù)，一個(gè)根據(jù)類別名來(lái)查詢，由于兩個(gè)存儲(chǔ)過(guò)程都是針對(duì)同一個(gè)表進(jìn)行查詢，所以可以將其放入同一過(guò)程組中。CREATEPROCpr_例十一;1 as select*fromCategoriesGOCREATEPROCpr_例十一;2@類別名稱nvarchar(15) as select*fromCategories whereCategoryName=@類別名稱GO執(zhí)行完成后，只能看到一個(gè)名為”pr_例十一”的存儲(chǔ)過(guò)程組，而看不到里面包含了多少個(gè)存儲(chǔ)過(guò)程如果要運(yùn)行pr_例十一的第一個(gè)存儲(chǔ)過(guò)程Execpr_例十一;1或者execpr_例十一存儲(chǔ)過(guò)程組中編號(hào)為1的存儲(chǔ)過(guò)程編號(hào)可以省略，而該存儲(chǔ)過(guò)程組中的其他存儲(chǔ)過(guò)程的存儲(chǔ)過(guò)程編號(hào)不能省略Execpr_例十一;2Seafood常用的系統(tǒng)存儲(chǔ)過(guò)程Sp_help：查看對(duì)象信息用于查看數(shù)據(jù)庫(kù)對(duì)象、用戶自定義數(shù)據(jù)類型或SQLServer提供的數(shù)據(jù)類型的消息sp_help[[@objname=]'name']Execsp_help：返回當(dāng)前數(shù)據(jù)庫(kù)中的所有對(duì)象Execsp_help數(shù)據(jù)表名：返回該數(shù)據(jù)表中的所有對(duì)象，例如字段名、主鍵、約束、索引、外鍵等Sp_helpdb：查看數(shù)據(jù)庫(kù)信息用于返回所有或指定數(shù)據(jù)庫(kù)的信息sp_helpdb[[@dbname=]'name']Execsp_helpdb：返回