存儲過程-《SQLServer2005程序設(shè)計語言》

SQLServer2005數(shù)據(jù)庫程序設(shè)計第10章存儲過程存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程在SQLServer中,存儲過程既子程序就是將常用的或復(fù)雜的工作，預(yù)先用SQL語句寫好并用指定的名稱存儲起來。用EXECUTE執(zhí)行。存儲過程是存儲在服務(wù)器上的T-SQL語句的命名集合是封裝重復(fù)性任務(wù)的方法支持用戶聲明變量、條件執(zhí)行以及其他強(qiáng)有力的編程特性10.1存儲過程概述SQLServer中的存儲過程可以包含執(zhí)行數(shù)據(jù)庫操作（包括調(diào)用其他過程）的編程語句接受輸入?yún)?shù)向調(diào)用過程或批處理返回狀態(tài)值，以表明成功或失?。ㄒ约笆≡颍┮暂敵鰠?shù)的形式將多個值返回至調(diào)用過程或批處理10.1存儲過程概述存儲過程------------------------單個SELECT語句SELECT語句塊SELECT語句與邏輯控制語句可以包含存儲過程可以包含數(shù)據(jù)操縱語句、變量、邏輯控制語句等10.1存儲過程概述10.1存儲過程概述在SQLServer2005中，按編寫的語言，存儲過程有兩種類型：T-SQL或CLR。T-SQL（SQLServer的存儲過程）：T-SQL存儲過程是指保存的T-SQL語句集合。CLR：是指對.NET公共語言運行時(CLR)方法的引用。SQLServer支持的T-SQL存儲過程系統(tǒng)存儲過程（sp_）：存儲在master數(shù)據(jù)庫內(nèi)，以“sp_”前綴標(biāo)識本地存儲過程：在單獨的用戶數(shù)據(jù)庫內(nèi)創(chuàng)建臨時存儲過程：可能是局部的，名稱以“#”開頭；也可能是全局的，名稱以“##”開頭遠(yuǎn)程存儲過程：是指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲過程，分布式查詢支持這項功能擴(kuò)展存儲過程（sp_或xp_）：在SQLServer環(huán)境外執(zhí)行10.1存儲過程概述關(guān)于系統(tǒng)存儲過程的前綴sp_所有的系統(tǒng)存儲過程的名字都以sp_開頭任何創(chuàng)建在master數(shù)據(jù)庫中的以“sp_”開頭的存儲過程都可以在其他數(shù)據(jù)庫中被直接調(diào)用而不需要用數(shù)據(jù)庫名完全引用，不必使用完全合法名稱10.1存儲過程概述執(zhí)行擴(kuò)展存儲過程常用的擴(kuò)展存儲過程例：execxp_cmdshell'dird:\'EXECsp_configure'showadvancedoption','1';RECONFIGURE;EXECsp_configure'xp_cmdshell','1';RECONFIGURE;使用存儲過程的優(yōu)點：只在創(chuàng)造時進(jìn)行編譯，執(zhí)行速度快提高系統(tǒng)性能確保數(shù)據(jù)庫的安全自動完成需要預(yù)先執(zhí)行的任務(wù)可重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量10.1存儲過程概述創(chuàng)建存儲過程只能在當(dāng)前數(shù)據(jù)庫內(nèi)創(chuàng)建存儲過程，除了臨時存儲過程。臨時存儲過程總是創(chuàng)建在tempdb數(shù)據(jù)庫中存儲過程可以引用表、視圖、用戶定義函數(shù)、其他存儲過程以及臨時表若存儲過程創(chuàng)建了局部臨時表，則當(dāng)存儲過程執(zhí)行結(jié)束后臨時表消失權(quán)限10.1存儲過程概述存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程10.2設(shè)計存儲過程用戶可以設(shè)計自己的存儲過程，以提高業(yè)務(wù)邏輯使用的通用性。設(shè)計存儲過程通常應(yīng)該遵循以下原則：存儲過程名稱避免使用sp的前綴盡量不使用臨時存儲過程。CREATE/ALTERPROCEDURE必須是批處理的第一個語句。使用CREATEPROCEDURE定義SQL語句集合（不能使用下表中的語句）：10.2設(shè)計存儲過程存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程10.3.1在SQLServerManagementStudio中建立存儲過程用戶可以使用SQLServerManagementStudio工具建立存儲過程10.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程創(chuàng)建存儲過程只能在當(dāng)前數(shù)據(jù)庫內(nèi)創(chuàng)建存儲過程，除了臨時存儲過程。臨時存儲過程總是創(chuàng)建在tempdb數(shù)據(jù)庫中存儲過程可以引用表、視圖、用戶定義函數(shù)、其他存儲過程以及臨時表若存儲過程創(chuàng)建了局部臨時表，則當(dāng)存儲過程執(zhí)行結(jié)束后臨時表消失USENorthwindGOCREATEPROCdbo.OverdueOrdersASSELECT*FROMdbo.OrdersWHERERequiredDate<GETDATE()ANDShippedDateISNull語法CREATEPROC[EDURE][schema_name.]proceduce_name[;number]/*定義存儲過程名，number對同名的過程分組*/[{@parameterdata_type}/*定義參數(shù)類型[VARYING][=default][OUT[PUT]]/*定義參數(shù)屬性[,…n][WITH{RECOMPILE|ENCRYPTION}]/*定義存儲過程的處理方式ASsql_statement[…n]/*執(zhí)行的操作10.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程注釋：存儲過程名必須符合標(biāo)識符規(guī)則。Number為可選參數(shù)，用于區(qū)分同名的存儲過程@parameter為存儲過程的形參，data_type指定數(shù)據(jù)類型Default指定存儲過程輸入?yún)?shù)的默認(rèn)值，OUTPUT指定參數(shù)從存儲過程的返回信息。RECOMPILE表明每次運行該過程，將對其重新編譯，ENCRYPTION對包含創(chuàng)建存儲過程的文本加密。sql_statement代表過程體包含的T-SQL語句10.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程--示例：使用帶有復(fù)雜SELECT語句的簡單過程--下面的存儲過程從四個表的聯(lián)接中返回所有作者、出版的書籍以及出版社。該存儲過程不使用任何參數(shù)。USEpubsifobject_id('au_info_all','P')isnotnull DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_id查看存儲過程的信息查看所有類型存儲過程的額外信息系統(tǒng)存儲過程sp_help、sp_helptext、sp_depends顯示數(shù)據(jù)庫中的存儲過程以及擁有者名字的列表系統(tǒng)存儲過程sp_stored_procedures得到存儲過程的信息查詢系統(tǒng)視圖sys.sysobjects、sys.syscomments、sys.sysdepends10.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程嵌套存儲過程：一個存儲過程調(diào)用另一個存儲過程可以嵌套32層。若試圖超過32層嵌套，則整個存儲過程調(diào)用鏈?zhǔn)‘?dāng)前的嵌套層數(shù)存儲在系統(tǒng)函數(shù)@@nestlevel中若一個存儲過程調(diào)用了第二個存儲過程，那么第二個存儲過程可以訪問第一個存儲過程創(chuàng)建的所有對象，包括臨時表嵌套的存儲過程可以遞歸調(diào)用。10.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程CREATEPROCEDUREinnerprocasselect@@NESTLEVELAS'InnerLevel'示例1CREATEPROCEDUREouterprocasselect@@NESTLEVELAS'OuterLevel'EXECinnerproc示例210.3.2用CREATEPROCEDURE語句創(chuàng)建存儲過程重命名存儲過程重命名存儲過程sp_renameoldname,newname限定存儲過程所引用的對象名稱創(chuàng)建存儲過程的準(zhǔn)則ü每個任務(wù)創(chuàng)建一個存儲過程ü創(chuàng)建，測試存儲過程，并對其進(jìn)行故障診斷ü存儲過程名稱避免使用sp_前綴

ü盡可能減少臨時存儲過程的使用

ü存儲過程的參數(shù)分兩種：輸入?yún)?shù)輸出參數(shù)輸入?yún)?shù)：用于向存儲過程傳入值，類似C語言的按值傳遞;輸出參數(shù)：用于在調(diào)用存儲過程后，返回結(jié)果，類似C語言的按引用傳遞;intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）傳入?yún)?shù)值返回結(jié)果存儲過程的參數(shù)輸入?yún)?shù)允許傳遞信息到存儲過程內(nèi)在CREATEPROCEDURE中指定@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值]指定參數(shù)的原則所有的輸入?yún)?shù)值都應(yīng)在存儲過程開始時進(jìn)行檢查，以盡早捕獲缺失值和非法值的情況應(yīng)為參數(shù)提供合適的默認(rèn)值。若定義了默認(rèn)值，用戶可以在未指定參數(shù)值的基礎(chǔ)上執(zhí)行存儲過程參數(shù)對存儲過程而言是局部的。輸入?yún)?shù)輸入?yún)?shù)示例：CREATEPROCProduction.LongLeadProducts@MinimumLengthintASIF(@MinimumLength<0)

BEGIN

RAISERROR('Invalidleadtime.',14,1)

RETURN

ENDSELECT Name,ProductNumber,DaysToManufactureFROM Production.ProductWHERE DaysToManufacture>=@MinimumLengthORDERBYDaysToManufactureDESC,NameEXECProduction.LongLeadProducts@MinimumLength=4CREATEPROCEDUREdbo.GetProducts2@namevarchar(50)ASSELECT*FROMProduction.ProductWHEREName=@name輸入?yún)?shù)示例：以下執(zhí)行語句均正確execGetProducts2'HexNut22'execGetProducts2@name='HexNut22'GetProducts2'HexNut22'輸入?yún)?shù)通過參數(shù)名傳遞值在EXECUTE語句中以“@參數(shù)名=值”的格式指定參數(shù)稱為通過參數(shù)名傳遞當(dāng)通過參數(shù)名傳遞值時，可以以任何順序指定參數(shù)值，并且可以省略允許空值或具有默認(rèn)值的參數(shù)若定義了參數(shù)的默認(rèn)值，當(dāng)調(diào)用存儲過程時，參數(shù)課未指定值或者參數(shù)的值指定為DEFAULT關(guān)鍵字輸入?yún)?shù)輸入?yún)?shù)提供合適的默認(rèn)值，驗證輸入?yún)?shù)值，包括空值檢查CREATEPROCProduction.LongLeadProducts@MinimumLengthint=1 --defaultvalueASIF(@MinimumLength<0) --validate

BEGIN

RAISERROR('Invalidleadtime.',14,1)

RETURN

ENDSELECT Name,ProductNumber,DaysToManufactureFROM Production.ProductWHERE DaysToManufacture>=@MinimumLengthORDERBYDaysToManufactureDESC,NameEXECProduction.LongLeadProducts通過位置傳遞參數(shù)只傳遞值（而沒有對被傳值參數(shù)的引用）稱為通過位置傳遞參數(shù)值必須以參數(shù)在CREATEPROCEDURE語句中的定義順序列出可以忽略有默認(rèn)值的參數(shù)，但不能中斷次序輸入?yún)?shù)CREATEPROCEDUREdbo.[sum1]@aint,@bintASDECLARE@sumintSET@sum=@a+@bprint@sum輸入?yún)?shù)以下方式執(zhí)行均可：exec[sum1]@a=1,@b=2exec[sum1]@b=2,@a=1exec[sum1]1,2CREATEPROCEDUREdbo.[yeartoyearsales]@beginningdatedatetime,@endingdatedatetimeASIF@beginningdateISNULLOR@endingdateISNULLBEGINRAISERROR(‘NULLvaluearenotallowed’,14,1)RETURNENDSELECTO.Shippeddate,O.Orderid,OS.Subtotal,DATENAME(yy,Shippeddate)ASyearFROMORDERSOINNERJOIN[OrderSubtotals]OSONO.Orderid=OS.OrderidWHEREO.ShippeddateBETWEEN@beginningdateAND@endingdate輸入?yún)?shù)輸出參數(shù)：以O(shè)UTPUT關(guān)鍵字指定的變量存儲過程通過輸出參數(shù)向調(diào)用它的存儲過程或客戶端返回信息通過輸出參數(shù)，存儲過程的運行結(jié)果可以得到保留，即使存儲過程運行結(jié)束輸出參數(shù)的特性調(diào)用語句必須包含一個變量名，以接受返回值。不能傳遞常數(shù)可以在隨后的Transact-SQL語句中使用返回變量輸出參數(shù)和返回值CREATEPROCEDUREdbo.[sum]@aint,@bint,@sumintOUTPUTASSET@sum=@a+@b示例DECLARE@answerintexec[sum]1,2,@answerOUTPUTselect@answer輸出參數(shù)和返回值DECLARE@answerintexec[sum]@a=1,@b=2,@sum=@answerOUTPUTselect@answerCREATEPROCHumanResources.AddDepartment@Namenvarchar(50),@GroupNamenvarchar(50),@DeptIDsmallintOUTPUTASINSERTINTOHumanResources.Department(Name,GroupName)VALUES (@Name,@GroupName)SET@DeptID=SCOPE_IDENTITY()輸出參數(shù)和返回值CREATEPROCHumanResources.AddDepartment@Namenvarchar(50),@GroupNamenvarchar(50),@DeptIDsmallintOUTPUTASIF((@Name='')OR(@GroupName=''))RETURN-1INSERTINTOHumanResources.Department(Name,GroupName)VALUES (@Name,@GroupName)SET@DeptID=SCOPE_IDENTITY()RETURN0DECLARE@deptintEXECAddDepartment'Refunds','',@deptOUTPUTSELECT@deptDECLARE@deptint,@resultintEXEC@result=AddDepartment'Refunds','',@deptOUTPUTIF(@result=0) SELECT@deptELSE SELECT'Errorduringinsert'顯式地重新編譯存儲過程存儲過程可以顯式地重新編譯。顯式重新編譯過程的方法CREATEPROCEDURE[WITHRECOMPILE]創(chuàng)建存儲過程時在其定義中指定WITHRECOMPILE選項，表明SQLServer將不對該存儲過程計劃進(jìn)行高速緩存，該存儲過程將在每次執(zhí)行時都重新編譯EXECUTE[WITHRECOMPILE]在執(zhí)行存儲過程時指定WITHRECOMPILE選項，可強(qiáng)制對存儲過程進(jìn)行重新編譯--示例：使用WITHRECOMPILE選項--如果為過程提供的參數(shù)不是典型的參數(shù)，并且新的執(zhí)行計劃不應(yīng)高速緩存或存儲在內(nèi)存中，WITHRECOMPILE子句會很有幫助。USEpubsGOCREATEPROCEDUREtitles_by_author@@LNAME_PATTERNvarchar(30)='%'WITHRECOMPILEASSELECTRTRIM(au_fname)+''+RTRIM(au_lname)AS'Authorsfullname',titleASTitleFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONta.title_id=t.title_idWHEREau_lnameLIKE@@LNAME_PATTERN創(chuàng)建對用戶隱藏存儲過程文本的存儲過程encrypt_this：檢索authors的所有內(nèi)容。（提示：WITHENCRYPTION選項）練習(xí)41精選PPTUSEpubsGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMauthors練習(xí)——答案42精選PPT創(chuàng)建一個更新雇員電話號碼的存儲過程，電話號碼保存在表Employees中，要求執(zhí)行存儲過程時，可將指定的雇員的電話號碼改成新的號碼。練習(xí)43精選PPT練習(xí)——答案CREATEPROCEDUREUpdateEmployeePhone (@EmployeeID_1int, @HomePhone_2 nvarchar(24))ASUPDATENorthwind.dbo.EmployeesSETHomePhone =@HomePhone_2WHEREEmployeeID=@EmployeeID_144精選PPT練習(xí)——答案創(chuàng)建后執(zhí)行存儲過程驗證。EXECUpdateEmployeePhone@EmployeeID_1=6,@HomePhone_2=’(206)-555-7773’45精選PPT練習(xí)創(chuàng)建一個名為FindCustomer1的存儲過程，可以用它來找出SQLSERVER中的northwind數(shù)據(jù)庫的Customer表中，CustomerID為指定值（輸入?yún)?shù)）的記錄的ContactName字段的名稱，另外指定一個輸出參數(shù)LineNum做為輸出參數(shù)，還有必須在存儲過程中判斷CustomerID不能為空串，是的話要打印出出錯信息，并返回錯誤值-1,如果查詢成功在輸出變量LineNum中保留選出的行數(shù)，然后返回值0。寫出相應(yīng)的SQL語句.46精選PPT練習(xí)——答案CREATEPROCFindCustomer@LineNumintOUTPUT,@CustomerIDchar(5)ASIFLEN(@CustomerID)=0BEGINPRINT'YoumustsupplyavalidCustomerID'RETURN-1ENDSELECTcontactNameFromCustomersWHERECustomerID=@CustomerIDSET@LineNum=@@ROWCOUNTRETURN047精選PPT練習(xí)——答案執(zhí)行過程一：declare@LineNumintexecFindCustomer@LineNumOUTPUT,'ALFKI'select@LineNum執(zhí)行過程二：DECLARE@LineNumint,@resultintEXEC@result=FindCustome@LineNumOUTPUT,'ALFKIIF(@result=0) SELECT@LineNumELSE SELECT'ErrorCustomerID'48精選PPT存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程修改存儲過程USENorthwindGOALTERPROCdbo.OverdueOrdersASSELECTCONVERT(char(8),RequiredDate,1)RequiredDate,CONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROMOrdersWHERERequiredDate<GETDATE()ANDShippedDateISNullORDERBYRequiredDateGO10.4修改存儲過程語法ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]10.4修改存儲過程修改存儲過程的注意事項若想修改帶選項創(chuàng)建的存儲過程，例如WITHENCRYPTION選項，則必須在ALTERPROCEDURE語句中包括那些選項，以保留選項的功能ALTERPROCEDURE語句只更改單個表，不影響嵌套的存儲過程ALTERPROCEDURE權(quán)限10.4修改存儲過程存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程存儲過程的兩種執(zhí)行方法；1）單獨執(zhí)行存儲過程（EXECUTE）2）作為INSERT語句的一部分執(zhí)行存儲過程10.5執(zhí)行存儲過程1）單獨執(zhí)行存儲過程語法：[[EXEC[UTE]]{[@返回狀態(tài)=]{存儲過程名[;編號]|

@存儲過程名稱變量}[[@參數(shù)=]{值|@變量[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]例如：EXECOverdueOrders10.5執(zhí)行存儲過程2）在INSERT語句內(nèi)執(zhí)行存儲過程語法：INSERTINTO表名EXEC[UTE]……將本地或遠(yuǎn)程存儲過程返回的結(jié)果集插入本地表中在INSERT語句內(nèi)執(zhí)行的存儲過程必須返回關(guān)系結(jié)果集10.5執(zhí)行存儲過程createprocinsert_procasselect*fromstudentwherestu_sex='女‘執(zhí)行：insertintogirl_studentexecinsert_proc存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程10.6刪除存儲過程刪除存儲過程語法：DROPPROCEDURE{存儲過程名}[,...n]刪除存儲過程的注意事項在刪除存儲過程之前，執(zhí)行sp_depends檢查是否有對象依賴于此存儲過程DROPPROCProduction.LongLeadProducts存儲過程概述設(shè)計存儲過程創(chuàng)建存儲過程修改存儲過程執(zhí)行存儲過程刪除存儲過程常用系統(tǒng)存儲過程建立CLR存儲過程第10章存儲過程10.7常