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

1、SQLServer存儲過程的基本概念以及語法【轉(zhuǎn)】存儲過程的概念SQLServer提供了一種方法，它可以將一些固定的操作集中起來由SQLServer數(shù)據(jù)庫服務器來完成，以實現(xiàn)某個任務，這種方法就是存儲過程。存儲過程是SQL語句和可選控制流語句的預編譯集合，存儲在數(shù)據(jù)庫中，可由應用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其他強大的編程功能。在SQLServer中存儲過程分為兩類：即系統(tǒng)提供的存儲過程和用戶自定義的存儲過程?？梢猿鲇谌魏问褂肧QL語句的目的來使用存儲過程，它具有以下優(yōu)點：可以在單個存儲過程中執(zhí)行一系列SQL語句?？梢詮淖约旱拇鎯^程內(nèi)引用其他存儲過程，這可以簡化一

2、系列復雜語句。存儲過程在創(chuàng)建時即在服務器上進行編譯，所以執(zhí)行起來比單個SQL語句快，而且減少網(wǎng)絡通信的負擔。安全性更高。創(chuàng)建存儲過程在SQLServer中，可以使用三種方法創(chuàng)建存儲過程：使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。利用SQLServer企業(yè)管理器創(chuàng)建存儲過程。卜面介紹使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過創(chuàng)建存儲過程前，應該考慮下列幾個事項：不能將CREATEPROCEDURE語句與其它SQL語句組合到單個批處理中,存儲過程可以嵌套使用，嵌套的最大深度不能超過32層。創(chuàng)建存儲過程的權(quán)限默認屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。存儲過程

3、是數(shù)據(jù)庫對象，其名稱必須遵守標識符規(guī)則。只能在當前數(shù)據(jù)庫中創(chuàng)建存儲過程。一個存儲過程的最大尺寸為128M。使用CREATEPROCEDURE創(chuàng)建存儲過程的語法形式如下：QUOTE：CREATEPROCEDUREprocedure_name;number;numberparameterdata_typeVARYING=defaultOUTPUT，nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statementn用CREATEPROCEDURE創(chuàng)建存儲過程的語法參數(shù)的意義如下procedure_name用于指定要

4、創(chuàng)建的存儲過程的名稱。number該參數(shù)是可選的整數(shù)，它用來對同名的存儲過程分組，以使用一條DROPPROCEDURE語句即可將同組的過程一起除去。parameter過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。data_type用于指定參數(shù)的數(shù)據(jù)類型。VARYING:用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。Default:用于指定參數(shù)的默認值。OUTPUT:表明該參數(shù)是一個返回參數(shù)。例如:下面創(chuàng)建一個簡單的存儲過程productinfo,用于檢索產(chǎn)品信息。USENorthwindifexists(selectnamefromsysobjectswheren

5、ame='productinfo'andtype='p'dropprocedureproductinfoGOcreateprocedureproductinfoasselect*fromproductsGO通過下述sql語句執(zhí)行該存儲過程:executeproductinfo即可檢索到產(chǎn)品信息。執(zhí)行存儲過程直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，其語法形式如下：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAU

6、LT，nWITHRECOMPILE使用EXECUTE命令傳遞單個參數(shù)，它執(zhí)行showind存儲過程,以titles為參數(shù)值。showind存儲過程需要參數(shù)（tabname它是一個表的名稱。其程序清單如下EXECshowindtitles當然，在執(zhí)行過程中變量可以顯式命名：EXECshowindtabname=titles如果這是isql腳本或批處理中第一個語句，則EXEC語句可以省略：showindtitles或者showindtabname=titles下面的例子使用了默認參數(shù)USENorthwindGOCREATEPROCEDUREinsert_Products_1(SupplierID_

7、2int,CategoryID_3int,ProductName_1nvarchar(40='c'ASINSERTINTOProducts(ProductName,SupplierID,CategoryIDVALUES(ProductName_1,SupplierID_2,CategoryID_3GOexecinsert_Products_11,1Select*fromProductswhereSupplierID=1andCategoryID=1GO下面的例子使用了返回參數(shù)USENorthwindGOCREATEPROCEDUREquery_products(Supplier

8、ID_1int,ProductName_2nvarchar(40outputASselectProductName_2=ProductNamefromproductswhereSupplierlD=SupplierID_1執(zhí)行該存儲過程來查詢SupplierID為1的產(chǎn)品名：declareproductnvarchar(40execquery_products1,productoutputselect產(chǎn)品名'=productgo查看存儲過程存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂檬褂闷髽I(yè)管理器或系統(tǒng)存儲過

9、程來查看用戶創(chuàng)建的存儲過程。使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程在企業(yè)管理器中，打開指定的服務器和數(shù)據(jù)庫項，選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫單擊存儲過程文件夾，此時在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲過程。用右鍵單擊要查看的存儲過程，從彈出的快捷菜單中選擇屬性選項，此時便可以看到存儲過程的源代碼。使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程可供使用的系統(tǒng)存儲過程及其語法形式如下：sp_help用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型sp_helpobjname=name參數(shù)name為要查看的存儲過程的名稱。sp_helptext用于顯示存儲過程的源代碼sp_helptextobjname=name參數(shù)name

10、為要查看的存儲過程的名稱。sp_dependsffl于顯示和存儲過程相關的數(shù)據(jù)庫對象sp_dependsobjname='object'參數(shù)object為要查看依賴關系的存儲過程的名稱。sp_stored_procedure卻于返回當前數(shù)據(jù)庫中的存儲過程列表修改存儲過程存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTERPROCEDURE語句可以更改先前通過執(zhí)行CREATEPROCEDURE語句倉U建的過程,但不會更改權(quán)限也不影響相關的存儲過程或觸發(fā)器。其語法形式如下：ALTERPROCEDUREprocedure_name;numberparameterdata

11、_typeVARYING=defaultOUTPUT，nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n重命名和刪除存儲過程1.重命名存儲過程修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename其語法形式如下：sp_rename原存儲過程名稱，新存儲過程名稱另外，通過企業(yè)管理器也可以修改存儲過程的名稱。刪除存儲過程刪除存儲過程可以使用DROP命令,DROP命令可以將一個或者多個存儲過程或者存儲過程組從當前數(shù)據(jù)庫中刪除，其語法形式如下：dropprocedureprocedure,n當然，

12、利用企業(yè)管理器也可以很方便地刪除存儲過程。存儲過程的重新編譯在我們使用了一次存儲過程后，可能會因為某些原因，必須向表中新增加數(shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時，需要對存儲過程進行重新編譯,SQLServer提供三種重新編譯存儲過程的方法：1、在建立存儲過程時設定重新編譯語法格式：CREATEPROCEDUREprocedure_nameWITHRECOMPILEASsql_statement2、在執(zhí)行存儲過程時設定重編譯語法格式：EXECUTEprocedure_nameWITHRECOMPILE3、通過使用系統(tǒng)存儲過程設定重編譯語法格式為：EXECsp_recompi

13、leOBJECT系統(tǒng)存儲過程與擴展存儲過程1.系統(tǒng)存儲過程系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，并以sp_為前綴，主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQLServer提供幫助，為用戶查看數(shù)據(jù)庫對象提供方便。比如用來查看數(shù)據(jù)庫對象信息的系統(tǒng)存儲過程sp_help顯示存儲過程和其它對象的文本的存儲過程sp_helptext等。2.擴展存儲過程：擴展存儲過程以xp為前綴，它是關系數(shù)據(jù)庫引擎的開放式數(shù)據(jù)服務層的一部分，其可以使用戶在動態(tài)鏈接庫(DLL文件所包含的函數(shù)中實現(xiàn)邏輯，從而擴展了Transact-SQL的功能，并且可以象調(diào)用Transact-SQL過程那樣從Transact-SQL

14、語句調(diào)用這些函數(shù)。例：利用擴展存儲過程xp_cmdshell為一個操作系統(tǒng)外殼執(zhí)行指定命令申，并作為文本返回任何輸出。執(zhí)行代碼：usemasterexecxp_cmdshell'dir*.exe行結(jié)果返回系統(tǒng)目錄下的文件內(nèi)容文本信息。最后給大家舉一個例子：QUOTE:/*1、在Northwind數(shù)據(jù)庫中，創(chuàng)建一個帶查詢參數(shù)的存儲過程，要求在輸入一個定購金額總額total時，查詢超出該值的所有產(chǎn)品的相關信息，包括產(chǎn)品名稱和供應商名稱、單位數(shù)量、單價、以及該產(chǎn)品的定購金額總額，并通過一個輸出參數(shù)返回滿足查詢條件的產(chǎn)品數(shù)*/IFexists(select*fromSysObjectswherename='more_than_total'andtype='p'dropproceduremore_than_totalgoCREATEPROCEDUREMore_Than_Totaltotalmoney=0ASDeclareamountsmallintBEGINselectdistinctP.productName,S.contact