什么是存儲過程呢？

定義：

????? 將常用的或很復雜的工作，預先用SQL語句寫好并用一個指定的名稱存儲起來, 那么以后要叫數據庫提供與已定義好的存儲過程的功能相同的服務時,只需調用execute,即可自動完成命令。

講到這里,可能有人要問：這么說存儲過程就是一堆SQL語句而已啊？

?????????????????????????????????????? Microsoft公司為什么還要添加這個技術呢?

那么存儲過程與一般的SQL語句有什么區別呢?

存儲過程的優點：

?????????????????????? 1.存儲過程只在創造時進行編譯，以后每次執行存儲過程都不需再重新編譯，而一般SQL語句每執行一次就編譯一次,所以使用存儲過程可提高數據庫執行速度。

??????????????????????? 2.當對數據庫進行復雜操作時(如對多個表進行Update,Insert,Query,Delete時），可將此復雜操作用存儲過程封裝起來與數據庫提供的事務處理結合一起使用。

?????????????????????? 3.存儲過程可以重復使用,可減少數據庫開發人員的工作量

?????????????????????? 4.安全性高,可設定只有某此用戶才具有對指定存儲過程的使用權

存儲過程的種類：

??? 1.系統存儲過程：以sp_開頭,用來進行系統的各項設定.取得信息.相關管理工作,

?????????????????????????????? 如 sp_help就是取得指定對象的相關信息

?? 2.擴展存儲過程?? 以XP_開頭,用來調用操作系統提供的功能

????????????????????????????? exec master..xp_cmdshell 'ping 10.8.16.1'

?? 3.用戶自定義的存儲過程,這是我們所指的存儲過程

?? 常用格式

?? Create procedure procedue_name

?? [@parameter data_type][output]

?? [with]{recompile|encryption}

?? as

??????? sql_statement

解釋:?

output：表示此參數是可傳回的

with {recompile|encryption}

recompile:表示每次執行此存儲過程時都重新編譯一次

encryption:所創建的存儲過程的內容會被加密

如:

?? 表book的內容如下

?? 編號??? 書名?????????????????????????? 價格

?? 001????? C語言入門?????????????????? $30

?? 002????? PowerBuilder報表開發? $52

?實例1:查詢表Book的內容的存儲過程

?? create proc query_book

????? as

????? select * from book

?? go

?? exec query_book

?實例2:加入一筆記錄到表book,并查詢此表中所有書籍的總金額

?? Create proc insert_book

?? @param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

?? with encryption? ---------加密

?? as

?? insert book(編號,書名，價格） Values(@param1,@param2,@param3)
?? select @param4=sum(價格) from book
? go

? 執行例子:
? declare @total_price money
? exec insert_book '003','Delphi 控件開發指南',$100,@total_price
? print '總金額為'+convert(varchar,@total_price)
? go
存儲過程的3種傳回值:
?? 1.以Return傳回整數
?? 2.以output格式傳回參數
?? 3.Recordset
傳回值的區別:
?????? output和return都可在批次程式中用變量接收,而recordset則傳回到執行批次的客戶端中?

實例3：設有兩個表為Product,Order,其表內容如下：
????? Product
?????????? 產品編號?????? 產品名稱??? 客戶訂數????
??????????? 001???????????? 鋼筆???????? 30????????
??????????? 002???????????? 毛筆???????? 50????????
??????????? 003???????????? 鉛筆???????? 100???????
????? Order?
?????????? 產品編號???????? 客戶名???? 客戶訂金
??????????? 001????????????? 南山區????? $30
??????????? 002????????????? 羅湖區????? $50
??????????? 003????????????? 寶安區????? $4
請實現按編號為連接條件,將兩個表連接成一個臨時表,該表只含編號.產品名.客戶名.訂金.總金額,
總金額=訂金*訂數,臨時表放在存儲過程中

代碼如下:
???? Create proc temp_sale
???? as
?????? select a.產品編號,a.產品名稱,b.客戶名,b.客戶訂金,a.客戶訂數* b.客戶訂金 as總金額
?????? into #temptable from Product a inner join Order b on a.產品編號=b.產品編號
??? if? @@error=0
?????? print 'Good'
??? else
?????? print 'Fail'
???? go

Stored Procedure（存儲過程）編寫經驗和優化措施

一、 前言： 在經過一段時間的存儲過程開發之后，寫下了一些開發時候的小結和經驗與大家共享，希望對大家有益，主要是針對 Sybase 和 SQL Server 數據庫，但其它數據庫應該有一些共性。

二、 適合讀者對象： 數據庫開發程序員，數據庫的數據量很多，涉及到對 SP （存儲過程）的優化的項目開發人員，對數據庫有濃厚興趣的人。

三、 介紹： 在數據庫的開發過程中，經常會遇到復雜的業務邏輯和對數據庫的操作，這個時候就會用 SP 來封裝數據庫操作。如果項目的 SP 較多，書寫又沒有一定的規范，將會影響以后的系統維護困難和大 SP 邏輯的難以理解，另外如果數據庫的數據量大或者項目對 SP 的性能要求很，就會遇到優化的問題，否則速度有可能很慢，經過親身經驗，一個經過優化過的 SP 要比一個性能差的 SP 的效率甚至高幾百倍。

四、 ??????? 內容：

1 、開發人員如果用到其他庫的 Table 或 View ，務必在當前庫中建立 View 來實現跨庫操作，最好不要直接使用“ databse.dbo.table_name ”，因為 sp_depends 不能顯示出該 SP 所使用的跨庫 table 或 view ，不方便校驗。

?

2 、開發人員在提交 SP 前，必須已經使用 set showplan on 分析過查詢計劃，做過自身的查詢優化檢查。

?

3、 高程序運行效率，優化應用程序，在 SP 編寫過程中應該注意以下幾點：

a) ??????? SQL 的使用規范：

???????????????????????? i. ????????????? 盡量避免大事務操作，慎用 holdlock 子句，提高系統并發能力。

?????????????????????? ii. ????????????? 盡量避免反復訪問同一張或幾張表，尤其是數據量較大的表，可以考慮先根據條件提取數據到臨時表中，然后再做連接。

????????????????????? iii. ????????????? 盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過 1 萬行，那么就應該改寫；如果使用了游標，就要盡量避免在游標循環中再進行表連接的操作。

???????????????????? iv. ????????????? 注意 where 字句寫法，必須考慮語句順序，應該根據索引順序、范圍大小來確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小。

?????????????????????? v. ????????????? 不要在 where 子句中的“ = ”左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

???????????????????? vi. ????????????? 盡量使用 exists 代替 select count(1) 來判斷是否存在記錄， count 函數只有在統計表中所有行數時使用，而且 count(1) 比 count(*) 更有效率。

??????????????????? vii. ????????????? 盡量使用“ >= ”，不要使用“ > ”。

????????????????? viii. ????????????? 注意一些 or 子句和 union 子句之間的替換

???????????????????? ix. ????????????? 注意表之間連接的數據類型，避免不同類型數據之間的連接。

?????????????????????? x. ????????????? 注意存儲過程中參數和數據類型的關系。

???????????????????? xi. ????????????? 注意 insert 、 update 操作的數據量，防止與其他應用沖突。如果數據量超過 200 個數據頁面（ 400k ），那么系統將會進行鎖升級，頁級鎖會升級成表級鎖。

?

b) ??????? 索引的使用規范：

???????????????????????? i. ????????????? 索引的創建要與應用結合考慮，建議大的 OLTP 表不要超過 6 個索引。

?????????????????????? ii. ????????????? 盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時可以通過 index index_name 來強制指定索引

????????????????????? iii. ????????????? 避免對大表查詢時進行 table scan ，必要時考慮新建索引。

???????????????????? iv. ????????????? 在使用索引字段作為條件時，如果該索引是聯合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用。

?????????????????????? v. ????????????? 要注意索引的維護，周期性重建索引，重新編譯存儲過程。

?

c) ??????? tempdb 的使用規范：

???????????????????????? i. ????????????? 盡量避免使用 distinct 、 order by 、 group by 、 having 、 join 、 cumpute ，因為這些語句會加重 tempdb 的負擔。

?????????????????????? ii. ????????????? 避免頻繁創建和刪除臨時表，減少系統表資源的消耗。

????????????????????? iii. ????????????? 在新建臨時表時，如果一次性插入數據量很大，那么可以使用 select into 代替 create table ，避免 log ，提高速度；如果數據量不大，為了緩和系統表的資源，建議先 create table ，然后 insert 。

???????????????????? iv. ????????????? 如果臨時表的數據量較大，需要建立索引，那么應該將創建臨時表和建立索引的過程放在單獨一個子存儲過程中，這樣才能保證系統能夠很好的使用到該臨時表的索引。

?????????????????????? v. ????????????? 如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統表的較長時間鎖定。

???????????????????? vi. ????????????? 慎用大的臨時表與其他大表的連接查詢和修改，減低系統表負擔，因為這種操作會在一條語句中多次使用 tempdb 的系統表。

?

d) ??????? 合理的算法使用：

根據上面已提到的 SQL 優化技術和 ASE Tuning 手冊中的 SQL 優化內容 , 結合實際應用 , 采用多種算法進行比較 , 以獲得消耗資源最少、效率最高的方法。具體可用 ASE 調優命令： set statistics io on, set statistics time on , set showplan on 等。

存儲過程入門與提高