當(dāng)應(yīng)用MSSQLServer創(chuàng)建一個(gè)應(yīng)用程序時(shí)，Transaction-SQL是一種主要的編程語言。若運(yùn)用SQL語言來進(jìn)行編程，有兩種方法。其一是，在本地存儲(chǔ)Transaction-SQL程序，并創(chuàng)建應(yīng)用程序向SQLServer發(fā)送命令來對結(jié)果進(jìn)行處理。其二是，可以把部分用Transaction-SQL編寫的程序作為存儲(chǔ)過程存儲(chǔ)在SQLServer中，并創(chuàng)建應(yīng)用程序來調(diào)用存儲(chǔ)過程，返回結(jié)果，然后對數(shù)據(jù)結(jié)果進(jìn)行處理。
本文介紹的是第二種方法，即使用存儲(chǔ)過程訪問數(shù)據(jù)庫。
2存儲(chǔ)過程簡介
2.1存儲(chǔ)過程概念、優(yōu)點(diǎn)
存儲(chǔ)過程（StoredProcedure）是一組完成特定功能的SQL語句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫中。存儲(chǔ)過程能夠通過接收參數(shù)向調(diào)用者返回結(jié)果集，結(jié)果集的格式由調(diào)用者決定；能夠返回狀態(tài)值給調(diào)用者，指明調(diào)用是成功或是失敗；包括針對數(shù)據(jù)庫的操作語句，并且可以在一個(gè)存儲(chǔ)過程中調(diào)用另一存儲(chǔ)過程。
存儲(chǔ)過程具有以下優(yōu)點(diǎn)：
①存儲(chǔ)過程允許標(biāo)準(zhǔn)組件式編程
存儲(chǔ)過程在被創(chuàng)建以后可以在程序中被多次調(diào)用，而不必重新編寫該存儲(chǔ)過程。而且數(shù)據(jù)庫專業(yè)人員可隨時(shí)對存儲(chǔ)過程進(jìn)行修改，但對應(yīng)用程序源代碼毫無影響（因?yàn)閼?yīng)用程序源代碼只包含存儲(chǔ)過程的調(diào)用語句），從而極大地提高了程序的可移植性。
②存儲(chǔ)過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度
如果某一操作包含大量的SQL代碼或分別被多次執(zhí)行，那么存儲(chǔ)過程要比批處理的執(zhí)行速度快很多。因?yàn)榇鎯?chǔ)過程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí)，查詢優(yōu)化器對其進(jìn)行分析、優(yōu)化，并給出最終被存在系統(tǒng)表中的執(zhí)行計(jì)劃。而批處理的SQL語句在每次運(yùn)行時(shí)都要進(jìn)行編譯和優(yōu)化，因此速度相對要慢一些。
③存儲(chǔ)過程能夠減少網(wǎng)絡(luò)流量
對于同一個(gè)針對數(shù)據(jù)庫對象的操作（如查詢、修改），如果這一操作所涉及到的SQL語句被組織成一個(gè)存儲(chǔ)過程，那么當(dāng)在客戶計(jì)算機(jī)上調(diào)用該存儲(chǔ)過程時(shí)，網(wǎng)絡(luò)中傳送的只是該調(diào)用語句，否則將是多條SQL語句，從而大大增加了網(wǎng)絡(luò)流量，降低網(wǎng)絡(luò)負(fù)載。
④存儲(chǔ)過程可被作為一種安全機(jī)制來充分利用
系統(tǒng)管理員通過對執(zhí)行某一存儲(chǔ)過程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)訪問權(quán)限的限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。存儲(chǔ)過程隱藏了數(shù)據(jù)庫模式和編程細(xì)節(jié)，當(dāng)使用客戶端SQL代碼查詢一個(gè)數(shù)據(jù)庫時(shí)，你需要了解所查詢的表和列的詳情，這會(huì)將數(shù)據(jù)庫的模式呈現(xiàn)在客戶連接和客戶面前，存儲(chǔ)過程則不允許客戶端了解細(xì)節(jié)，客戶或連接掌握的唯一信息就是它們要調(diào)用的存儲(chǔ)過程名，從這個(gè)角度看存儲(chǔ)過程提供了一個(gè)數(shù)據(jù)安全層。
2.2存儲(chǔ)過程類型
SQLServer支持以下五種類型的存儲(chǔ)過程：
系統(tǒng)存儲(chǔ)過程：此類存儲(chǔ)過程內(nèi)置于SQLServer，不能對其任意修改，它們提供有關(guān)數(shù)據(jù)庫模式、對象名、約束、數(shù)據(jù)類型以及許可等等方面的信息。
局部存儲(chǔ)過程：此類存儲(chǔ)過程由數(shù)據(jù)庫管理員或SQLServer開發(fā)人員編寫，是本文討論的重點(diǎn)。
臨時(shí)存儲(chǔ)過程：此類存儲(chǔ)過程由數(shù)據(jù)庫管理員和SQLServer開發(fā)人員編寫，但是只存在于連接期間。
遠(yuǎn)程存儲(chǔ)過程：此類存儲(chǔ)過程存在于遠(yuǎn)程服務(wù)器中，并且可以被一個(gè)初始服務(wù)器引用，遠(yuǎn)程存儲(chǔ)過程用于分布式應(yīng)用程序中。
擴(kuò)展存儲(chǔ)過程：此類存儲(chǔ)過程在功能上類似于局部存儲(chǔ)過程，但它們可以引用SQlServer外部的函數(shù)。
2.3SQLServer處理存儲(chǔ)過程
存儲(chǔ)過程的處理分為兩個(gè)階段。在第一階段，存儲(chǔ)過程創(chuàng)建后首先由SQLServer數(shù)據(jù)庫引擎解析，這以后會(huì)發(fā)生兩件事。其一，SQLServer將該過程的定義、名稱和代碼保存到數(shù)據(jù)庫中，同時(shí)，SQLServer還會(huì)把代碼交付給查詢優(yōu)化器，以確定代碼的最佳執(zhí)行規(guī)劃；其次，代碼被編譯并被置于過程緩存中，只有在客戶連接顯示調(diào)用重編譯時(shí)或執(zhí)行規(guī)劃不再存在于高速緩存中時(shí)，高速緩存中的執(zhí)行規(guī)劃才被刷新。如圖1所示。 圖1SQLServer處理存儲(chǔ)過程階段一
在第二階段，當(dāng)代碼中引用了此存儲(chǔ)過程名時(shí)，查詢規(guī)劃被檢索，過程代碼此時(shí)在調(diào)用它的每個(gè)連接的上下文中執(zhí)行，任何結(jié)果集或返回值都會(huì)返回給每一個(gè)連接。如圖2所示。
圖2SQLServer處理存儲(chǔ)過程階段二
2.4存儲(chǔ)過程中的錯(cuò)誤處理
在存儲(chǔ)過程執(zhí)行過程中，如果遇到錯(cuò)誤，SQLServer會(huì)返回錯(cuò)誤代碼和錯(cuò)誤信息。SQLServer有近3800個(gè)預(yù)定義錯(cuò)誤代碼，存儲(chǔ)在master數(shù)據(jù)庫的sysmessages表中。每一個(gè)錯(cuò)誤代碼都有相應(yīng)的精確的級(jí)別。錯(cuò)誤定義的級(jí)別從0到25。20以上的錯(cuò)誤代表重大錯(cuò)誤，意味著該錯(cuò)誤會(huì)導(dǎo)致存儲(chǔ)進(jìn)程立刻終止，并且所有的連接都要重新初始化。非關(guān)鍵性錯(cuò)誤只是禁止當(dāng)前運(yùn)行的有錯(cuò)誤的代碼，并繼續(xù)執(zhí)行剩余的代碼。所有的錯(cuò)誤代碼都有預(yù)定義的錯(cuò)誤信息?？梢愿鶕?jù)錯(cuò)誤代碼獲得錯(cuò)誤信息，然后返回給存儲(chǔ)過程的調(diào)用程序，利于程序編寫者處理錯(cuò)誤。
3在VS.net中調(diào)用存儲(chǔ)過程
下面以Northwind數(shù)據(jù)庫為例介紹在VisualStudio.net集成開發(fā)環(huán)境中如何調(diào)用存儲(chǔ)過程(C#)。
3.1創(chuàng)建存儲(chǔ)過程
根據(jù)客戶端的需求創(chuàng)建實(shí)現(xiàn)一定功能的存儲(chǔ)過程，可以使用SQlServer企業(yè)管理器中的創(chuàng)建存儲(chǔ)過程向?qū)Щ蛘咧苯泳帉慡QL語句來創(chuàng)建存儲(chǔ)過程。
List.1存儲(chǔ)過程清單
[1]CREATEPROCEDUREcount_CustomerID
[2]@CustomerIDnchar(5),
[3]@numintOUTPUT
[4]AS
[5]SELECT@num=(SELECTCOUNT(*)FROMordersWHERECustomerID=@CustomerID)
[6]Return
[7]GO
以上SQL語句創(chuàng)建一個(gè)名為count_CustomerID的存儲(chǔ)過程，這個(gè)存儲(chǔ)過程有一個(gè)輸入?yún)?shù)（參數(shù)名稱：CustomerID；類型：nchar(5)；意義：標(biāo)識(shí)商品的ID值）和一個(gè)輸出參數(shù)（參數(shù)名稱：num；類型：int；意義：某一商品的訂單數(shù)量）。該存儲(chǔ)過程實(shí)現(xiàn)的功能：根據(jù)客戶端調(diào)用存儲(chǔ)過程時(shí)傳入的商品ID值，然后計(jì)算此商品的訂單數(shù)量，將結(jié)果作為輸出參數(shù)返回給客戶端的調(diào)用程序。
注：創(chuàng)建存儲(chǔ)過程的語法詳見SQLServer聯(lián)機(jī)叢書。
3.2設(shè)計(jì)應(yīng)用程序
數(shù)據(jù)庫和存儲(chǔ)過程設(shè)計(jì)好之后，就可以開始設(shè)計(jì)應(yīng)用程序了。首先要做的是連接數(shù)據(jù)庫，可以通過SqlConnection對象(命名空間：System.Data.SqlClient)、OleDbConnection對象(命名空間：System.Data.OleDb)、OdbcConnection對象(命名空間：System.Data.Odbc)等方法連接數(shù)據(jù)庫。本文介紹使用SqlConnection對象連接數(shù)據(jù)庫。
List.2調(diào)用存儲(chǔ)過程
//添加命名空間
UsingSystem.Data.SqlClient;
//使用SqlConnection對象連接數(shù)據(jù)庫
stringConnStr;
ConnStr="datasource=local;initialcatalog=Northwind;userid=user;password=pw";
SqlConnectionconn=newSqlConnection(ConnStr);
//使用SqlCommand調(diào)用存儲(chǔ)過程
SqlCommandcmd=newSqlCommand(CustOrdersOrders,conn);//創(chuàng)建SqlCommand對象
conn.CommandType=CommandType.StoredProcedure;
SqlParameterCustID=newSqlParameter("@CustomerID",SqlDbType.Char,5);
CustID.value=“ALFKI”;//對輸入?yún)?shù)賦值
cmd.Parameters.Add(CustID);
SqlParameterCount=newSqlParameter("@num",SqlDbType.int,4);
cmd.Parameters.Add(Count);
Count.Direction=ParameterDirection.Output;//指定參數(shù)為輸出參數(shù)
//開始執(zhí)行存儲(chǔ)過程
Conn.Open();//打開數(shù)據(jù)庫連接
cmd.ExecuteNonQuery();//執(zhí)行查詢
conn.Close();
//變量sum的值就是存儲(chǔ)過程返回的指定商品的訂單數(shù)量
intsum=Convert.ToInt32(cmd.Parameters["@total"].Value.ToString());
4存儲(chǔ)過程的其他功能
4.1擴(kuò)展存儲(chǔ)過程
在編寫數(shù)據(jù)訪問的程序時(shí)，有時(shí)需要實(shí)現(xiàn)一些SQLServer本身不能實(shí)現(xiàn)的功能，如訪問本地資源等，這時(shí)就可以使用擴(kuò)展存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程是以動(dòng)態(tài)鏈接庫的形式實(shí)現(xiàn)的，在存儲(chǔ)過程中使用存儲(chǔ)在master數(shù)據(jù)庫中的系統(tǒng)存儲(chǔ)過程sp_addextendedproc將已經(jīng)編寫好的動(dòng)態(tài)鏈接庫(.dll文件)中的函數(shù)聲明為擴(kuò)展存儲(chǔ)過程，聲明之后就可以調(diào)用此函數(shù)，從而實(shí)現(xiàn)所要實(shí)現(xiàn)的一些功能。
具體語法略。
4.2調(diào)用Com組件
Com，即組件對象模型，是一種以組件為發(fā)布單元的對象模型，這種模型是各種軟件組件可以使用統(tǒng)一的方式進(jìn)行交互，不依賴于任何特定的語言和操作系統(tǒng)。Com的這種優(yōu)點(diǎn)使得越來越多的編程開發(fā)者采用這種編程方式。SQLServer中的存儲(chǔ)過程也支持Com這種編程方式。
根據(jù)需求編寫Com組件，發(fā)布組件之后，使用存儲(chǔ)在master數(shù)據(jù)庫中的系統(tǒng)存儲(chǔ)過程sp_OACreate將Com對象實(shí)例化，之后就可以使用Com對象中的函數(shù)。
具體語法略。
5優(yōu)化存儲(chǔ)過程
如果一個(gè)應(yīng)用程序所用的存儲(chǔ)過程較多，書寫又沒有一定的規(guī)范，將會(huì)影響以后的系統(tǒng)維護(hù)和較大存儲(chǔ)過程邏輯的難以理解，另外如果數(shù)據(jù)庫的數(shù)據(jù)量大或?qū)Υ鎯?chǔ)過程的性能要求很高，就會(huì)遇到優(yōu)化的問題，否則速度有可能很慢。一個(gè)經(jīng)過優(yōu)化過的存儲(chǔ)過程要比一個(gè)性能差的存儲(chǔ)過程的效率甚至高幾百倍。
在編寫存儲(chǔ)過程時(shí)應(yīng)該注意以下幾點(diǎn)
①盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力；
②盡量避免反復(fù)訪問同一張或幾張表，尤其是數(shù)據(jù)量較大的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到臨時(shí)表中，然后再做連接,訪問這個(gè)臨時(shí)表；
③盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該改寫；如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作；
④注意where語句的寫法，必須考慮語句順序，應(yīng)該根據(jù)索引順序、范圍大小來確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小；
⑤不要在where子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引；
⑥盡量使用exists代替selectcount(1)來判斷是否存在記錄，count函數(shù)只有在統(tǒng)計(jì)表中所有行數(shù)時(shí)使用，而且count(1)比count(*)更有效率。
⑦盡量使用“>=”，不要使用“>”；
⑧注意一些or子句和union子句之間的替換；
⑨注意表之間連接的數(shù)據(jù)類型，避免不同類型數(shù)據(jù)之間的連接；
此外，還要正確、高效地使用索引和操作臨時(shí)表；當(dāng)然合理的算法使用也是優(yōu)化存儲(chǔ)過程，提高程序執(zhí)行效率的有效手段。
6存儲(chǔ)過程與數(shù)據(jù)安全
從用戶對敏感數(shù)據(jù)的訪問角度，通過向用戶授予對存儲(chǔ)過程（而不是基礎(chǔ)表）的訪問權(quán)限，就可以控制對特定數(shù)據(jù)的訪問，實(shí)現(xiàn)對敏感數(shù)據(jù)的保護(hù)。
存儲(chǔ)過程還可以幫助解決代碼安全問題?？梢苑乐鼓承╊愋偷腟QL插入攻擊-主要是一些使用運(yùn)算符（如AND或OR）將命令附加到有效輸入?yún)?shù)值的攻擊。在應(yīng)用程序受到攻擊時(shí)，存儲(chǔ)過程還可以隱藏業(yè)務(wù)規(guī)則的實(shí)現(xiàn)。
另外，使用存儲(chǔ)過程使您可以使用ADO.NET中提供的SqlParameter類指定存儲(chǔ)過程參數(shù)的數(shù)據(jù)類型。這為驗(yàn)證用戶提供的值類型（作為深層次防御性策略的一部分）提供了一個(gè)簡單方法。
使用存儲(chǔ)過程增強(qiáng)數(shù)據(jù)安全性時(shí)值得注意的是，糟糕的安全性或編碼做法仍然會(huì)受到攻擊。對SQLServer角色創(chuàng)建和分配如果不加注意將導(dǎo)致訪問到不應(yīng)看到的數(shù)據(jù)。同時(shí)，存儲(chǔ)過程不能防止所有的SQL插入代碼攻擊（例如，將數(shù)據(jù)操作語言(DML)附加到輸入?yún)?shù)）。
另外，無論SQL語句位于代碼還是位于存儲(chǔ)過程中，使用參數(shù)進(jìn)行數(shù)據(jù)類型驗(yàn)證都不是萬無一失的。所有用戶提供的數(shù)據(jù)（尤其是文本數(shù)據(jù)）在傳遞到數(shù)據(jù)庫之前都應(yīng)受到附加的驗(yàn)證。
7結(jié)束語
本文簡單介紹了存儲(chǔ)過程的概念、特點(diǎn)和應(yīng)用方法，使大家對存儲(chǔ)過程有了初步了解。存儲(chǔ)過程的廣泛應(yīng)用和它訪問數(shù)據(jù)的機(jī)制使得存儲(chǔ)過程成為當(dāng)前提倡的三層結(jié)構(gòu)系統(tǒng)開發(fā)模型中數(shù)據(jù)訪問層所不可或缺的元素。希望對使用數(shù)據(jù)庫開發(fā)應(yīng)用程序的程序開發(fā)者有一定的借鑒和指導(dǎo)作用。
聯(lián)系客服
