Sql Server的存儲過程是一個被命名的存儲在服務器上的Transacation-Sql語句集合,是封裝重復性工作的一種方法,它支持用戶聲明的變量、條件執(zhí)行和其他強大的編程功能。
存儲過程相對于其他的數(shù)據(jù)庫訪問方法有以下的優(yōu)點：
（1）重復使用。存儲過程可以重復使用，從而可以減少數(shù)據(jù)庫開發(fā)人員的工作量。
（2）提高性能。存儲過程在創(chuàng)建的時候就進行了編譯，將來使用的時候不用再重新編譯。一般的SQL語句每執(zhí)行一次就需要編譯一次，所以使用存儲過程提高了效率。
（3）減少網(wǎng)絡(luò)流量。存儲過程位于服務器上，調(diào)用的時候只需要傳遞存儲過程的名稱以及參數(shù)就可以了，因此降低了網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。
（4）安全性。參數(shù)化的存儲過程可以防止SQL注入式的攻擊，而且可以將Grant、Deny以及Revoke權(quán)限應用于存儲過程。
存儲過程一共分為了三類：用戶定義的存儲過程、擴展存儲過程以及系統(tǒng)存儲過程。
其中，用戶定義的存儲過程又分為Transaction-SQL和CLR兩種類型。
Transaction-SQL 存儲過程是指保存的Transaction-SQL語句集合，可以接受和返回用戶提供的參數(shù)。
CLR存儲過程是指對.Net Framework公共語言運行時(CLR)方法的引用，可以接受和返回用戶提供的參數(shù)。他們在.Net Framework程序集中是作為類的公共靜態(tài)方法實現(xiàn)的。（本文就不作介紹了）
創(chuàng)建存儲過程的語句如下：
Code
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,
n ]
[ WITH <procedure_option> [ ,n ]
[ FOR REPLICATION ]
AS { <sql_statement> [;][ n ] | <method_specifier> }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }
<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
[schema_name]: 代表的是存儲過程所屬的架構(gòu)的名稱
例如：
Create Schema yangyang8848
Go
Create Proc yangyang8848.AllGoods
As Select * From Master_Goods
Go
執(zhí)行：Exec AllGoods 發(fā)生錯誤。
執(zhí)行：Exec yangyang8848.AllGoods 正確執(zhí)行。
[；Number]: 用于對同名過程進行分組的可選整數(shù)。使用一個 DROP PROCEDURE 語句可將這些分組過程一起刪除。
例如：
Create Proc S1 ;1
AS
Select * From Master_Goods
Go
Create Proc S1 ;2
As
Select * From Master_Location
Go
創(chuàng)建完畢了兩個存儲過程。它們在同一個組S1里，如果執(zhí)行Exec S1 則存儲過程默認執(zhí)行 Exec S1 ;1 。如果我們想得到所有據(jù)點信息則需要執(zhí)行Exec S1 ;2。當我們要刪除存儲過程的時候，只能執(zhí)行Drop Exec S1 則該組內(nèi)所有的存儲過程被刪除。
[@ parameter]: 存儲過程中的參數(shù)，除非將參數(shù)定義的時候有默認值或者將參數(shù)設(shè)置為等于另一個參數(shù)，否則用戶必須在調(diào)用存儲過程的時候為參數(shù)賦值。
存儲過程最多有2100個參數(shù)。
例如：
Create Proc yangyang8848.OneGoods
@GoodsCode varchar(10)
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Go
調(diào)用的代碼：
Declare @Code varchar(10)
Set @Code = '0004'
Exec yangyang8848.OneGoods @Code
在參數(shù)的后邊加入Output 表明該參數(shù)為輸出參數(shù)。
Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) output,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go
調(diào)用方法：
Declare @VV2 varchar(10)
Exec yangyang8848.OneGoods @Code out
注意：如果存儲過程的兩個參數(shù)一個有默認值一個沒有，那么我們要把有默認值得放在后邊，不然會出問題哦~~
細心的朋友，可能看到上邊的語句有一些不同，比如，存儲過程用的是output，而調(diào)用語句用的是out。我要告訴您，兩者是一樣的。
[RECOMPILE]：指示數(shù)據(jù)庫引擎 不緩存該過程的計劃，該過程在運行時編譯。如果指定了 FOR REPLICATION，則不能使用此選項。對于 CLR 存儲過程，不能指定 RECOMPILE。
這個說一個非常好用的函數(shù) OBJECT_ID ：返回架構(gòu)范圍內(nèi)對象的數(shù)據(jù)庫對象標識號。
例如：我們創(chuàng)建存儲過程時，可以如下寫代碼
If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go
Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go
針對于上邊的這個存儲過程，我們調(diào)用以下SQL查詢
Select definition From sys.sql_modules
Where object_id = Object_ID('yangyang8848.OneGoods');
我們是可以查到結(jié)果的。
可是如果我們對該存儲過程加入[ ENCRYPTION ] 那么你將無法看到任何結(jié)果
If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods
Go
Create Proc yangyang8848.OneGoods
@GoodsCode2 varchar(10) out,@GoodsCode varchar(10) = '0011'
With Encryption
As
Select * From Master_Goods Where GoodsCode = @GoodsCode
Set @GoodsCode2 = '0005'
Go
然后我們查詢 sys.sql_modules 目錄視圖，將返回給你Null。
然后我們執(zhí)行以下SQL: Exec sp_helptext 'yangyang8848.OneGoods'
你將得到以下結(jié)果：The text for object 'yangyang8848.OneGoods' is encrypted.
說到這里你應該明白了，參數(shù)[ ENCRYPTION ]：是一種加密的功能， 將 CREATE PROCEDURE 語句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對系統(tǒng)表或數(shù)據(jù)庫文件沒有訪問權(quán)限的用戶不能檢索模糊文本。但是，可通過 DAC 端口訪問系統(tǒng)表的特權(quán)用戶或直接訪問數(shù)據(jù)庫文件的特權(quán)用戶可使用此文本。此外，能夠向服務器進程附加調(diào)試器的用戶可在運行時從內(nèi)存中檢索已解密的過程。
前兩天寫了一篇關(guān)于游標的介紹文章 ，下邊寫一個例子，將游標與存儲過程一起使用上：
If Object_ID('dbo.GetMasterGoods') Is Not Null
Drop Proc dbo.GetMasterGoods
Go
Create Proc GetMasterGoods
@MyCursor Cursor Varying Output
With Encryption
As
Set @MyCursor = Cursor
For
Select GoodsCode,GoodsName From Master_Goods
Open @MyCursor
Go
--下邊建立另外一個存儲過程，用于遍歷游標輸出結(jié)果
Create Proc GetAllGoodsIDAndName
As
Declare @GoodsCode varchar(18)
Declare @GoodsName nvarchar(20)
Declare @MasterGoodsCursor Cursor
Exec GetMasterGoods @MasterGoodsCursor out
Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
While(@@Fetch_Status = 0)
Begin
Begin
Print @GoodsCode + ':' + @GoodsName
End
Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
End
Close @MasterGoodsCursor
Deallocate @MasterGoodsCursor
Go
最后執(zhí)行Exec GetAllGoodsIDAndName結(jié)果為以下內(nèi)容
0003:品0003
0004:品0004
0005:123123
0006:品0006
0007:品0007
0008:品0008
0009:品0009
0010:品0010
0011:品0011
0012:品0012
0013:品0013
0014:品0014
posted on 2010-01-21 17:37
老馬驛站 閱讀(209)
評論(0) 編輯 收藏 引用 所屬分類:
DataBase