引言 本文旨在介紹使用存儲過程封裝應用程序所需的 Transact
SQL (T
SQL) 的主要優缺點
以便您對如何在您的環境中使用存儲過程做出明智的決策
對於那些可以利用存儲過程的人員
本文還介紹了與在
NET 應用程序中使用存儲過程相關的工具和最佳做法
在這方面
大家的看法並不一致
有些人認為業務邏輯只應被編碼到中間層或數據庫中
有些人認為內聯查詢是唯一的選擇
有些人則認為存儲過程應用於任何情況
所有這些方法都具有優點和缺點
重要的是應考慮
對您的應用程序和環境至關重要的是什麼
因此
讓我們了解一下存儲過程的概念
並考慮使用它們封裝 T
SQL 代碼的某些原因
考慮使用存儲過程的理由 也許您曾經在多處編寫過使用 SqlCommand 對象的 T
SQL
但卻從未考慮過是否有一個比將它並入數據訪問代碼更好的位置
由於應用程序隨著時間的推移增添了一些功能
因此其內部可能包含一些復雜的 T
SQL 過程代碼
存儲過程為封裝此代碼提供了一個替換位置
大多數人可能對存儲過程已有所了解
但對於那些不了解存儲過程的人員而言
存儲過程是指一組作為單個代碼單元一起存儲於數據庫中的 T
SQL 語句
您可以使用輸入參數傳入運行時信息
並取回作為結果集或輸出參數的數據
存儲過程在首次運行時將被編譯
這將產生一個執行計劃
實際上是 Microsoft® SQL Server&#
; 為在存儲過程中獲取由 T
SQL 指定的結果而必須采取的步驟的記錄
然後
執行計劃在內存中得到緩存
以備以後使用
這樣會改善存儲過程的性能
因為 SQL Server 無需為確定如何處理代碼而重新分析它
而只需引用緩存的計劃即可
這個緩存的計劃一直可用
直到 SQL Server 重新啟動
或直到它由於使用率較低而溢出內存
性能 緩存的執行計劃曾使存儲過程較之查詢更有性能優勢
但對於 SQL Server 的幾個最新版本
執行計劃已針對所有 T
SQL 批處理進行了緩存
而不管它們是否在存儲過程中
因此
基於此功能的性能已不再是存儲過程的賣點
任何使用靜態語法
且提交頻率足以阻止執行計劃溢出內存的 T
SQL 批處理將會獲得同樣的性能好處
靜態
部分是關鍵
任何更改
即使像添加注釋這樣無關緊要的更改
也將導致無法與緩存的計劃相匹配
從而將無法重復使用計劃
但是
當存儲過程可以用於降低網絡流量時
它們仍然能夠提供性能好處
您只需在網絡中發送 EXECUTE stored_proc_name 語句
而非整個 T
SQL 例程
這可以在復雜操作中廣泛使用
設計良好的存儲過程可以將客戶端與服務器之間的許多往返過程簡化為單個調用
此外
使用存儲過程使您能夠增強對執行計劃的重復使用
由此可以通過使用遠程過程調用 (RPC) 處理服務器上的存儲過程而提高性能
使用 StoredProcedure 的 SqlCommand
CommandType 時
存儲過程通過 RPC 執行
RPC 封裝參數和調用服務器端過程的方式使引擎能夠輕松地找到匹配的執行計劃
並只需插入更新的參數值
考慮使用存儲過程提高性能時
最後要考慮是否要充分利用 T
SQL 的優點
請考慮要如何處理數據
&#
; 是否要使用基於集合的操作
或執行 T
SQL 中完全支持的其他操作？那麼存儲過程就是一個選擇
而內聯查詢也可以使用
&#
; 是否嘗試執行基於行的操作
或復雜的字符串處理？那麼可能要重新考慮在 T
SQL 中進行這種處理
這不包括使用存儲過程
至少要到 Yukon 發布並且公共語言運行庫 (CLR) 集成可用後
才能使用存儲過程
可維護性和抽象 要考慮的另一個潛在優勢是可維護性
理想情況下
數據庫架構從不更改
業務規則不被修改
但在現實環境中
情況則完全不同
既然情況如此
那麼如果可以修改存儲過程以包括新 X
Y 和 Z 表（為支持新的銷售活動而添加了這些表）中的數據
而不是在應用程序代碼中的某個位置更改此信息
則維護對您來說可能比較容易
在存儲過程中更改此信息使得更新對應用程序而言具有透明性
您仍然返回相同的銷售信息
即使存儲過程的內部實現已經更改
更新存儲過程通常比更改
測試以及重新部署程序集需要較少的時間和精力
另外
通過抽象化實現並將此代碼保存在存儲過程中
任何需要訪問數據的應用程序均可以獲取一致的數據
您無需在多個位置維護相同的代碼
用戶便可獲取一致的信息
在存儲過程中存儲 T
SQL 的另一個可維護性優點是更好的版本控制
您可以對創建和修改存儲過程的腳本進行版本控制
就像可以對任何其他源代碼模塊進行版本控制一樣
通過使用 Microsoft Visual SourceSafe® 或某個其他源代碼控制工具
您可以輕松地恢復到或引用舊版本的存儲過程
在使用存儲過程提高可維護性時應值得注意的一點是
它們無法阻止您對架構和規則進行所有可能的更改
如果更改范圍大到需要對輸入存儲過程的參數進行更改
或者要更改由其返回的數據
則您仍需要更新程序集中的代碼以添加參數
更新 GetValue() 調用
等等
要注意的另一個問題是
由於存儲過程將應用程序綁定到 SQL Server
因此使用存儲過程封裝業務邏輯將限制應用程序的可移植性
如果應用程序的可移植性在您的環境中非常重要
則將業務邏輯封裝在不特定於 RDBMS 的中間層中可能是一個更佳的選擇
安全性 考慮使用存儲過程的最終原因是它們可用於增強安全性
就管理用戶對信息的訪問而言
通過向用戶授予對存儲過程（而不是基礎表）的訪問權限
它們可以提供對特定數據的訪問
您可以將存儲過程看成是 SQL Server 視圖（如果您對它們熟悉的話）
除非存儲過程接受用戶的輸入以動態更改顯示的數據
存儲過程還可以幫助您解決代碼安全問題
它們可以防止某些類型的 SQL 插入攻擊
主要是一些使用運算符（如 AND 或 OR）將命令附加到有效輸入參數值的攻擊
在應用程序受到攻擊時
存儲過程還可以隱藏業務規則的實現
這對於將此類信息視為知識產權的公司非常重要
另外
使用存儲過程使您可以使用 ADO
NET 中提供的 SqlParameter 類指定存儲過程參數的數據類型
這為驗證用戶提供的值類型（作為深層次防御性策略的一部分）提供了一個簡單方法
在縮小可接受用戶輸入的范圍方面
參數在內聯查詢中與在存儲過程中一樣有用
使用存儲過程增強安全性時值得注意的是
糟糕的安全性或編碼做法仍然會使您受到攻擊
對 SQL Server 角色創建和分配如果不加注意將導致人們訪問到不應看到的數據
同時
如果認為使用存儲過程便可防止所有 SQL 插入代碼攻擊（例如
將數據操作語言 (DML) 附加到輸入參數）
後果將是一樣的
另外
無論 T
SQL 位於代碼還是位於存儲過程中
使用參數進行數據類型驗證都不是萬無一失的
所有用戶提供的數據（尤其是文本數據）在傳遞到數據庫之前都應受到附加的驗證
存儲過程對我是否適用？
或許適合吧
讓我們概括一下它們的優點
&#
; 通過降低網絡流量提高性能
&#
; 提供單點維護
&#
; 抽象化業務規則
以確保一致性和安全性
&#
; 通過將某些形式的攻擊降至最低
以增強安全性
&#
; 支持執行計劃重復使用
如果您的環境允許利用存儲過程提供的好處（如上所述）
強烈建議使用它們
對於改進數據在環境中的處理方式而言
它們提供了一個很好的工具
另一方面
如果您的環境中存在可移植性
大量使用非 T
SQL 友好的進程或者不穩定的數據庫架構等削弱這些優點的因素
則您可能要考慮其他方法
另一個要注意的事項是機構內部所擁有的 T
SQL 專業人員的數量
您有足夠的 T
SQL 知識嗎？您願意學習嗎？或者
您有 DBA 或合適的人員幫您編寫存儲過程嗎？掌握的 T
SQL 知識越多
存儲過程就會越好
維護它們就會越容易
例如
T
SQL 主要用於基於集合的操作
而不是基於行的操作
依賴於光標（因為它們向您提示數據集）將導致性能降低
如果您不太了解 T
SQL
請將本文作為一次學習機會
無論您將它用在何處
本文介紹的知識都將改善您的代碼
因此
如果您認為存儲過程會為應用程序增添特殊的效果
請繼續閱讀本文
我們將回顧一些簡化存儲過程使用的工具
並了解一些創建存儲過程的最佳做法
Visual Studio NET 工具 Microsoft Visual Studio®
NET 提供了一些工具
使您能夠查看和操作 SQL Server 存儲過程（以及其他數據庫對象）
讓我們簡要介紹一下您可能期望獲得的功能
查看存儲過程 您可以使用服務器資源管理器查看現有的存儲過程
查看它們所需的參數或查看它們的內部實現
如果您已經連接到安裝了 SQL Server 的服務器
則可以依次展開
ServerName
SQL Server
SQLInstanceName
Northwind
存儲過程
並展開
CustOrdersDetail
該資源管理器將顯示存儲過程所需的任何參數及其返回的任何列
如果簽出以上任何項的屬性
便會發現數據類型表示為 ADO 類型
框架文檔提供了這些類型與
NET 類型之間的簡單映射
當然
在 ADO
NET 代碼中使用參數時
可能要將它們的數據類型表示為 SqlDbType 枚舉的成員
您會看到
NET 類型與 SqlDbType 類型之間的映射
如果雙擊存儲過程
Visual Studio 將會在 SQL 編輯器中打開它
其中顯示了便於查看的顏色編碼和所有內容
注意
它並不顯示 CREATE PROCEDURE 語法（該語法實際上在數據庫中已存在）
由於它假定您要修改存儲過程
因此提供了 ALTER PROCEDURE 語法
創建和修改存儲過程
您具有在相應
From:http://tw.wingwit.com/Article/program/Java/hx/201311/26691.html
