SQLServer數(shù)據(jù)庫技術-07存儲過程

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

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

AS SELECT*FROMCategories

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

SET@返回值='庫存量為：'+CAST(@庫存asvarchar(10))ENDGOdeclare@接收值varchar(20)execpr_例七_4@接收值outputprint@接收值在存儲過程中執(zhí)行T-SQL返回數(shù)據(jù)集直接返回到執(zhí)行存儲過程的應用程序中，無法在批程序中使用，只能在批程序的結果中將其顯示出來例：建立一個存儲過程，用于查看某個類別的所有產品CREATEPROCEDUREpr_例八@類別名稱nvarchar(15)ASBEGIN SELECTProductID,ProductNameFROMProducts

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