什么是存儲(chǔ)過程呢？

定義：

????? 將常用的或很復(fù)雜的工作，預(yù)先用SQL語句寫好并用一個(gè)指定的名稱存儲(chǔ)起來, 那么以后要叫數(shù)據(jù)庫提供與已定義好的存儲(chǔ)過程的功能相同的服務(wù)時(shí),只需調(diào)用execute,即可自動(dòng)完成命令。

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

?????????????????????????????????????? Microsoft公司為什么還要添加這個(gè)技術(shù)呢?

那么存儲(chǔ)過程與一般的SQL語句有什么區(qū)別呢?

存儲(chǔ)過程的優(yōu)點(diǎn)：

?????????????????????? 1.存儲(chǔ)過程只在創(chuàng)造時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過程可提高數(shù)據(jù)庫執(zhí)行速度。

??????????????????????? 2.當(dāng)對(duì)數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行Update,Insert,Query,Delete時(shí)），可將此復(fù)雜操作用存儲(chǔ)過程封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。

?????????????????????? 3.存儲(chǔ)過程可以重復(fù)使用,可減少數(shù)據(jù)庫開發(fā)人員的工作量

?????????????????????? 4.安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過程的使用權(quán)

存儲(chǔ)過程的種類：

??? 1.系統(tǒng)存儲(chǔ)過程：以sp_開頭,用來進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定.取得信息.相關(guān)管理工作,

?????????????????????????????? 如 sp_help就是取得指定對(duì)象的相關(guān)信息

?? 2.擴(kuò)展存儲(chǔ)過程?? 以XP_開頭,用來調(diào)用操作系統(tǒng)提供的功能

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

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

?? 常用格式

?? Create procedure procedue_name

?? [@parameter data_type][output]

?? [with]{recompile|encryption}

?? as

??????? sql_statement

解釋:?

output：表示此參數(shù)是可傳回的

with {recompile|encryption}

recompile:表示每次執(zhí)行此存儲(chǔ)過程時(shí)都重新編譯一次

encryption:所創(chuàng)建的存儲(chǔ)過程的內(nèi)容會(huì)被加密

如:

?? 表book的內(nèi)容如下

?? 編號(hào)??? 書名?????????????????????????? 價(jià)格

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

?? 002????? PowerBuilder報(bào)表開發(fā)? $52

?實(shí)例1:查詢表Book的內(nèi)容的存儲(chǔ)過程

?? create proc query_book

????? as

????? select * from book

?? go

?? exec query_book

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

?? Create proc insert_book

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

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

?? as

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

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

實(shí)例3：設(shè)有兩個(gè)表為Product,Order,其表內(nèi)容如下：
????? Product
?????????? 產(chǎn)品編號(hào)?????? 產(chǎn)品名稱??? 客戶訂數(shù)????
??????????? 001???????????? 鋼筆???????? 30????????
??????????? 002???????????? 毛筆???????? 50????????
??????????? 003???????????? 鉛筆???????? 100???????
????? Order?
?????????? 產(chǎn)品編號(hào)???????? 客戶名???? 客戶訂金
??????????? 001????????????? 南山區(qū)????? $30
??????????? 002????????????? 羅湖區(qū)????? $50
??????????? 003????????????? 寶安區(qū)????? $4
請(qǐng)實(shí)現(xiàn)按編號(hào)為連接條件,將兩個(gè)表連接成一個(gè)臨時(shí)表,該表只含編號(hào).產(chǎn)品名.客戶名.訂金.總金額,
總金額=訂金*訂數(shù),臨時(shí)表放在存儲(chǔ)過程中

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

Stored Procedure（存儲(chǔ)過程）編寫經(jīng)驗(yàn)和優(yōu)化措施

一、 前言： 在經(jīng)過一段時(shí)間的存儲(chǔ)過程開發(fā)之后，寫下了一些開發(fā)時(shí)候的小結(jié)和經(jīng)驗(yàn)與大家共享，希望對(duì)大家有益，主要是針對(duì) Sybase 和 SQL Server 數(shù)據(jù)庫，但其它數(shù)據(jù)庫應(yīng)該有一些共性。

二、 適合讀者對(duì)象： 數(shù)據(jù)庫開發(fā)程序員，數(shù)據(jù)庫的數(shù)據(jù)量很多，涉及到對(duì) SP （存儲(chǔ)過程）的優(yōu)化的項(xiàng)目開發(fā)人員，對(duì)數(shù)據(jù)庫有濃厚興趣的人。

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

四、 ??????? 內(nèi)容：

1 、開發(fā)人員如果用到其他庫的 Table 或 View ，務(wù)必在當(dāng)前庫中建立 View 來實(shí)現(xiàn)跨庫操作，最好不要直接使用“ databse.dbo.table_name ”，因?yàn)? sp_depends 不能顯示出該 SP 所使用的跨庫 table 或 view ，不方便校驗(yàn)。

?

2 、開發(fā)人員在提交 SP 前，必須已經(jīng)使用 set showplan on 分析過查詢計(jì)劃，做過自身的查詢優(yōu)化檢查。

?

3、 高程序運(yùn)行效率，優(yōu)化應(yīng)用程序，在 SP 編寫過程中應(yīng)該注意以下幾點(diǎn)：

a) ??????? SQL 的使用規(guī)范：

???????????????????????? i. ????????????? 盡量避免大事務(wù)操作，慎用 holdlock 子句，提高系統(tǒng)并發(fā)能力。

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

????????????????????? iii. ????????????? 盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過 1 萬行，那么就應(yīng)該改寫；如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。

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

?????????????????????? v. ????????????? 不要在 where 子句中的“ = ”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

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

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

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

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

?????????????????????? x. ????????????? 注意存儲(chǔ)過程中參數(shù)和數(shù)據(jù)類型的關(guān)系。

???????????????????? xi. ????????????? 注意 insert 、 update 操作的數(shù)據(jù)量，防止與其他應(yīng)用沖突。如果數(shù)據(jù)量超過 200 個(gè)數(shù)據(jù)頁面（ 400k ），那么系統(tǒng)將會(huì)進(jìn)行鎖升級(jí)，頁級(jí)鎖會(huì)升級(jí)成表級(jí)鎖。

?

b) ??????? 索引的使用規(guī)范：

???????????????????????? i. ????????????? 索引的創(chuàng)建要與應(yīng)用結(jié)合考慮，建議大的 OLTP 表不要超過 6 個(gè)索引。

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

????????????????????? iii. ????????????? 避免對(duì)大表查詢時(shí)進(jìn)行 table scan ，必要時(shí)考慮新建索引。

???????????????????? iv. ????????????? 在使用索引字段作為條件時(shí)，如果該索引是聯(lián)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用。

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

?

c) ??????? tempdb 的使用規(guī)范：

???????????????????????? i. ????????????? 盡量避免使用 distinct 、 order by 、 group by 、 having 、 join 、 cumpute ，因?yàn)檫@些語句會(huì)加重 tempdb 的負(fù)擔(dān)。

?????????????????????? ii. ????????????? 避免頻繁創(chuàng)建和刪除臨時(shí)表，減少系統(tǒng)表資源的消耗。

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

???????????????????? iv. ????????????? 如果臨時(shí)表的數(shù)據(jù)量較大，需要建立索引，那么應(yīng)該將創(chuàng)建臨時(shí)表和建立索引的過程放在單獨(dú)一個(gè)子存儲(chǔ)過程中，這樣才能保證系統(tǒng)能夠很好的使用到該臨時(shí)表的索引。

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

???????????????????? vi. ????????????? 慎用大的臨時(shí)表與其他大表的連接查詢和修改，減低系統(tǒng)表負(fù)擔(dān)，因?yàn)檫@種操作會(huì)在一條語句中多次使用 tempdb 的系統(tǒng)表。

?

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

根據(jù)上面已提到的 SQL 優(yōu)化技術(shù)和 ASE Tuning 手冊(cè)中的 SQL 優(yōu)化內(nèi)容 , 結(jié)合實(shí)際應(yīng)用 , 采用多種算法進(jìn)行比較 , 以獲得消耗資源最少、效率最高的方法。具體可用 ASE 調(diào)優(yōu)命令： set statistics io on, set statistics time on , set showplan on 等。

存儲(chǔ)過程入門與提高