SQLServer存儲過程基本概念及語法匯總

SQLServer儲藏過程的基本看法以及語法匯總SQLServer儲藏過程的基本看法以及語法匯總SQLServer儲藏過程的基本看法以及語法匯總SQLServer儲藏過程的基本看法以及語法【轉(zhuǎn)】儲藏過程的看法SQLServer供應(yīng)了一種方法,它能夠?qū)⒁恍┕潭ǖ牟僮骷衅鸪鎏嶴QLServer數(shù)據(jù)庫服務(wù)器來完成,以實(shí)現(xiàn)某個任務(wù),這種方法就是儲藏過程。儲藏過程是SQL語句和可選控制流語句的預(yù)編譯會集,儲藏在數(shù)據(jù)庫中,可由應(yīng)用程序經(jīng)過一個調(diào)用執(zhí)行,而且贊同用戶聲明變量、有條件執(zhí)行以及其他富強(qiáng)的編程功能。在SQLServer中儲藏過程分為兩類:即系統(tǒng)供應(yīng)的儲藏過程和用戶自定義的儲藏過程。能夠出于任何使用SQL語句的目的來使用儲藏過程,它擁有以下優(yōu)點(diǎn):能夠在單個儲藏過程中執(zhí)行一系列SQL語句。能夠從自己的儲藏過程內(nèi)引用其他儲藏過程,這能夠簡化一系列復(fù)雜語句。儲藏過程在創(chuàng)辦時即在服務(wù)器進(jìn)步行編譯,所以執(zhí)行起來比單個SQL語句快,而且減少網(wǎng)絡(luò)通信的負(fù)擔(dān)。安全性更高。創(chuàng)辦儲藏過程在SQLServer中,能夠使用三種方法創(chuàng)辦儲藏過程:①使用創(chuàng)辦儲藏過程導(dǎo)游創(chuàng)辦儲藏過程。②利用SQLServer企業(yè)管理器創(chuàng)辦儲藏過程。③使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)辦儲藏過程。下面介紹使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)辦儲藏過程創(chuàng)辦儲藏過程前,應(yīng)該考慮以下幾個事項(xiàng):①不能夠?qū)REATEPROCEDURE語句與其他SQL語句組合到單個批辦理中。②儲藏過程能夠嵌套使用,嵌套的最大深度不能夠高出32層。③創(chuàng)辦儲藏過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者,該所有者可將此權(quán)限授予其他用戶。④儲藏過程是數(shù)據(jù)庫對象,其名稱必定遵守表記符規(guī)則。⑤只幸虧當(dāng)前數(shù)據(jù)庫中創(chuàng)辦儲藏過程。⑥一個儲藏過程的最大尺寸為128M。使用CREATEPROCEDURE創(chuàng)辦儲藏過程的語法形式以下:QUOTE:CREATEPROC[EDURE]procedure_name[;number][;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]用CREATEPROCEDURE創(chuàng)辦儲藏過程的語法參數(shù)的意義以下:procedure_name:用于指定要創(chuàng)辦的儲藏過程的名稱。number:該參數(shù)是可選的整數(shù),它用來對同名的儲藏過程分組,以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。@parameter:過程中的參數(shù)。在CREATEPROCEDURE語句中能夠聲明一個或多個參數(shù)。data_type:用于指定參數(shù)的數(shù)據(jù)種類。VARYING:用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。Default:用于指定參數(shù)的默認(rèn)值。OUTPUT:表示該參數(shù)是一個返回參數(shù)。比方:下面創(chuàng)辦一個簡單的儲藏過程productinfo,用于檢索產(chǎn)品信息。USENorthwindifexists(selectnamefromsysobjectswherename='productinfo'andtype='p'dropprocedureproductinfoGOcreateprocedureproductinfoasselect*fromproductsGO經(jīng)過下述sql語句執(zhí)行該儲藏過程:executeproductinfo即可檢索到產(chǎn)品信息。執(zhí)行儲藏過程直接執(zhí)行儲藏過程能夠使用EXECUTE命令來執(zhí)行,其語法形式以下:[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,...n][WITHRECOMPILE]使用EXECUTE命令傳達(dá)單個參數(shù),它執(zhí)行showind儲藏過程,以titles為參數(shù)值。showind儲藏過程需要參數(shù)(@tabname,它是一個表的名稱。其程序清單以下:EXECshowindtitles自然,在執(zhí)行過程中變量能夠顯式命名:EXECshowind@tabname=titles若是這是isql腳本或批辦理中第一個語句,則EXEC語句能夠省略:showindtitles也許showind@tabname=titles下面的例子使用了默認(rèn)參數(shù)USENorthwindGOCREATEPROCEDUREinsert_Products_1(@SupplierID_2int,@CategoryID_3int,@ProductName_1nvarchar(40='無'ASINSERTINTOProducts(ProductName,SupplierID,CategoryIDVALUES(@ProductName_1,@SupplierID_2,@CategoryID_3GOexecinsert_Products_11,1Select*fromProductswhereSupplierID=1andCategoryID=1GO下面的例子使用了返回參數(shù)USENorthwindGOCREATEPROCEDUREquery_products(@SupplierID_1int,@ProductName_2nvarchar(40outputASselect@ProductName_2=ProductNamefromproductswhereSupplierID=@SupplierID_1執(zhí)行該儲藏過程來盤問SupplierID為1的產(chǎn)品名:declare@productnvarchar(40execquery_products1,@productoutputselect產(chǎn)'品名'=@productgo查察儲藏過程儲藏過程被創(chuàng)辦此后,它的名字就儲藏在系統(tǒng)表sysobjects中,它的源代碼存放在系統(tǒng)表syscomments中。能夠使用使用企業(yè)管理器或系統(tǒng)儲藏過程來查察用戶創(chuàng)辦的儲藏過程。使用企業(yè)管理器查察用戶創(chuàng)辦的儲藏過程在企業(yè)管理器中,打開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng),選綱要創(chuàng)辦儲藏過程的數(shù)據(jù)庫,單擊儲藏過程文件夾,此時在右邊的頁框中顯示該數(shù)據(jù)庫的所有儲藏過程。用右鍵單擊要查察的儲藏過程,從彈出的快捷菜單中選擇屬性選項(xiàng),此時便能夠看到儲藏過程的源代碼。使用系統(tǒng)儲藏過程來查察用戶創(chuàng)辦的儲藏過程可供使用的系統(tǒng)儲藏過程及其語法形式以下:sp_help:用于顯示儲藏過程的參數(shù)及其數(shù)據(jù)種類sp_help[[@objname=]name]參數(shù)name為要查察的儲藏過程的名稱。sp_helptext:用于顯示儲藏過程的源代碼sp_helptext[[@objname=]name]參數(shù)name為要查察的儲藏過程的名稱。sp_depends:用于顯示和儲藏過程相關(guān)的數(shù)據(jù)庫對象sp_depends[@objname=]’object’參數(shù)object為要查察依賴關(guān)系的儲藏過程的名稱。sp_stored_procedures:用于返回當(dāng)前數(shù)據(jù)庫中的儲藏過程列表更正儲藏過程儲藏過程能夠依照用戶的要求也許基表定義的改變而改變。使用ALTERPROCEDURE語句能夠更正先前經(jīng)過執(zhí)行CREATEPROCEDURE語句創(chuàng)辦的過程,但不會更正權(quán)限,也不影響相關(guān)的儲藏過程或觸發(fā)器。其語法形式以下:ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]重命名和刪除儲藏過程重命名儲藏過程更正儲藏過程的名稱能夠使用系統(tǒng)儲藏過程sp_rename,其語法形式以下:sp_rename原儲藏過程名稱,新儲藏過程名稱別的,經(jīng)過企業(yè)管理器也能夠更正儲藏過程的名稱。刪除儲藏過程刪除儲藏過程能夠使用DROP命令,DROP命令能夠?qū)⒁粋€也很多個儲藏過程也許儲藏過程組從當(dāng)前數(shù)據(jù)庫中刪除,其語法形式以下:dropprocedure{procedure}[,n]自然,利用企業(yè)管理器也能夠很方便地刪除儲藏過程。儲藏過程的重新編譯在我們使用了一次儲藏過程后,可能會因?yàn)槟承┰?必定向表中新增加數(shù)據(jù)列也許為表新增加索引,從而改變了數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時,需要對儲藏過程進(jìn)行重新編譯,SQLServer供應(yīng)三種重新編譯儲藏過程的方法:1、在建立儲藏過程時設(shè)定重新編譯語法格式:CREATEPROCEDUREprocedure_nameWITHRECOMPILEASsql_statement2、在執(zhí)行儲藏過程時設(shè)定重編譯語法格式:EXECUTEprocedure_nameWITHRECOMPILE3、經(jīng)過使用系統(tǒng)儲藏過程設(shè)定重編譯語法格式為:EXECsp_recompileOBJECT系統(tǒng)儲藏過程與擴(kuò)展儲藏過程1.系統(tǒng)儲藏過程系統(tǒng)儲藏過程儲藏在master數(shù)據(jù)庫中,并以sp_為前綴,主要用來從系統(tǒng)表中獲守信息,為系統(tǒng)管理員管理SQLServer供應(yīng)幫助,為用戶查察數(shù)據(jù)庫對象供應(yīng)方便。比方用來查看數(shù)據(jù)庫對象信息的系統(tǒng)儲藏過程sp_help、顯示儲藏過程和其他對象的文本的儲藏過程sp_helptext等。2.擴(kuò)展儲藏過程：擴(kuò)展儲藏過程以xp_為前綴，它是關(guān)系數(shù)據(jù)庫引擎的開放式數(shù)據(jù)服務(wù)層的一部分，其能夠使用戶在動向鏈接庫(DLL文件所包括的函數(shù)中實(shí)現(xiàn)邏輯，從而擴(kuò)展了Transact-SQL的功能，而且能夠象調(diào)用Transact-SQL過程那樣從Transact-SQL語句調(diào)用這些函數(shù)。例:利用擴(kuò)展儲藏過程xp_cmdshell為一個操作系統(tǒng)外殼執(zhí)行指定數(shù)令串，并作為文本返回任何輸出。執(zhí)行代碼：usemasterexecxp_cmdshell'dir*.exe'執(zhí)行結(jié)果返回系統(tǒng)目錄下的文件內(nèi)容文本信息。最后給大家舉一個例子：QUOTE:/1、在Northwind數(shù)據(jù)庫中，創(chuàng)辦一個帶盤問參數(shù)的儲藏過程，要求在輸入一個定購金額總數(shù)＠total時，盤問高出該值的所有產(chǎn)品的相關(guān)信息，包括產(chǎn)品名稱和供應(yīng)商名稱、單位數(shù)量、單價、以及該產(chǎn)品的定購金額總數(shù)，并經(jīng)過一個輸出參數(shù)返回滿足盤問條件的產(chǎn)品數(shù)/IFexists(select*fromSysObjectswherename='more_than_total'andtype='p'dropproceduremore_than_totalgoCREATEPROCEDUREMore_Than_Total@totalmoney=0ASDeclare@amountsmallintBEGINselectdistinctP.productName,