SQL Server存儲(chǔ)過(guò)程編程經(jīng)驗(yàn)技巧
MS SQL Server目前正日益成為WindowNT操作系統(tǒng)上面最為重要的一種數(shù)據(jù)庫(kù)管理系統(tǒng)，隨著MS SQL Server2000的推出，微軟的這種數(shù)據(jù)庫(kù)服務(wù)系統(tǒng)真正的實(shí)現(xiàn)了在WindowsNT／2000系列操作系統(tǒng)一統(tǒng)天下的局面，在微軟的操作系統(tǒng)上面，沒(méi)有任何一種數(shù)據(jù)庫(kù)系統(tǒng)能與之相抗衡，包括數(shù)據(jù)庫(kù)領(lǐng)域的領(lǐng)頭羊甲骨文公司的看家數(shù)據(jù)庫(kù)系統(tǒng)Oracle在內(nèi)。不可否認(rèn)，MS SQL Server最大的缺陷就是只能運(yùn)行在微軟自己的操作系統(tǒng)上面，這一點(diǎn)是MS SQL Server的致命傷口。但在另一方面卻也成了最好的促進(jìn)劑，促使MS SQL Server在自己僅有的“土地”上面將自己的功能發(fā)揮到了極至，最大限度的利用了WindowsNT系列操作系統(tǒng)的各種潛能！作為MS SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中很重要的一個(gè)概念就是存儲(chǔ)過(guò)程，合理的使用存儲(chǔ)過(guò)程，可以有效的提高程序的性能；并且將商業(yè)邏輯封裝在數(shù)據(jù)庫(kù)系統(tǒng)中的存儲(chǔ)過(guò)程中，可以大大提高整個(gè)軟件系統(tǒng)的可維護(hù)性，當(dāng)你的商業(yè)邏輯發(fā)生了改變的時(shí)候，不再需要修改并編譯客戶端應(yīng)用程序以及重新分發(fā)他們到為數(shù)眾多的用戶手中，你只需要修改位于服務(wù)器端的實(shí)現(xiàn)相應(yīng)商業(yè)邏輯的存儲(chǔ)過(guò)程即可。合理的編寫自己需要的存儲(chǔ)過(guò)程，可以最大限度的利用MS SQL Server的各種資源。下面我們來(lái)共同看看各種編寫MS SQL Server存儲(chǔ)過(guò)程和使用存儲(chǔ)過(guò)程的技巧經(jīng)驗(yàn)！
下面我們討論的前提都是您有過(guò)一定的MS SQL Server存儲(chǔ)過(guò)程的編寫經(jīng)驗(yàn)，下面的各種技巧如果沒(méi)有特別注明，則同時(shí)適用于MS SQL Server7．0和MS SQL Server2000。
1、使用OUTPUT類型參數(shù)的存儲(chǔ)過(guò)程的技巧
一般的存儲(chǔ)過(guò)程都是直接返回一段記錄集給調(diào)用者，但是有的時(shí)候我們只需要一些存儲(chǔ)過(guò)程返回的一些參數(shù)的值，這時(shí)候可以指定存儲(chǔ)過(guò)程的OUTPUT參數(shù)，比如：
Create procedure GetName
＠uid nvarchar（1），
＠usernam nvarchar（10）＝＇＇ output
AS
set?。纔sername＝＇hongchao＇
GO
在上面的存儲(chǔ)過(guò)程當(dāng)中我們傳遞進(jìn)的參數(shù)是＠uid，而參數(shù)＠username則在調(diào)有的時(shí)候不需要傳遞進(jìn)去。這樣，存儲(chǔ)過(guò)程會(huì)返回給我們參數(shù)＠username的值是‘hongchao’。上面的都比較的容易，需要注意的是，當(dāng)在SQL2000中的時(shí)候，如果你的存儲(chǔ)過(guò)程只有一個(gè)參數(shù)，并且這個(gè)參數(shù)是OUTPUT類型的，你必須在調(diào)用這個(gè)存儲(chǔ)過(guò)程的時(shí)候給這個(gè)參數(shù)一個(gè)初始的值，否則會(huì)出現(xiàn)調(diào)用錯(cuò)誤的情況！
2、在存儲(chǔ)過(guò)程中的書寫注意事項(xiàng)
這一點(diǎn)在MS SQL Server7．0和MS SQL Server2000種有些地方是不一樣的，也不知道是不是微軟的遺漏，那就是有些系統(tǒng)的關(guān)鍵字在不同的版本之間有所不同，比如關(guān)鍵字level，同樣的一句話：
select ＊ from users where level＝1
在MS SQL Server7當(dāng)中的存儲(chǔ)過(guò)程當(dāng)中運(yùn)行沒(méi)有絲毫的問(wèn)題，但是到了MS SQL Server2000當(dāng)中則會(huì)出現(xiàn)運(yùn)行的錯(cuò)誤，原因就是在于在MS SQL Server2000中“l(fā)evel”被當(dāng)作了關(guān)鍵字（奇怪的是SQL7當(dāng)中也同樣是關(guān)鍵字，卻沒(méi)問(wèn)題），所以在SQL2000當(dāng)中，上面的語(yǔ)句應(yīng)當(dāng)改為：
select?。rom users where ［level］＝1
從上面的例子中我們可以看到，在你編寫存儲(chǔ)過(guò)程的時(shí)候，最好在有可能和系統(tǒng)關(guān)鍵字的地方使用“［”和“］”將他包圍起來(lái)，以避免在移植過(guò)程中出現(xiàn)的運(yùn)行錯(cuò)誤問(wèn)題。
3、在存儲(chǔ)過(guò)程中使用系統(tǒng)存儲(chǔ)過(guò)程SP＿Executesql的注意事項(xiàng)
我們?cè)诰帉懽约旱拇鎯?chǔ)過(guò)程的時(shí)候，往往在很多的情況下，會(huì)使用到系統(tǒng)的存儲(chǔ)過(guò)程SP＿Execute。但是需要的注意的是，如果你在這個(gè)存儲(chǔ)過(guò)程的參數(shù)（一般是一段SQL語(yǔ)句）當(dāng)中進(jìn)行了臨時(shí)Table的操作，那末對(duì)于調(diào)用者來(lái)說(shuō)，這個(gè)臨時(shí)Table是不可見的，也就是說(shuō)你無(wú)法通過(guò)臨時(shí)Table來(lái)在調(diào)用者和被調(diào)用者之間傳遞值。解決的方法是使用全局臨時(shí)Table，也就是“＃?！遍_頭的Table。
4、在存儲(chǔ)過(guò)程中使用臨時(shí)Table和游標(biāo)的注意事項(xiàng)
如果我們的商業(yè)邏輯比較復(fù)雜，在存儲(chǔ)過(guò)程當(dāng)中，就需要一些媒介作為中轉(zhuǎn)臺(tái)，這時(shí)候臨時(shí)表舅發(fā)揮了作用，但是請(qǐng)務(wù)必記得在使用完之后，即使刪除使用到的臨時(shí)Table。
而在存儲(chǔ)過(guò)程當(dāng)中想要依次遍歷一個(gè)記錄集的唯一方法就是使用系統(tǒng)游標(biāo)，同樣要注意的是，在使用完成之后及時(shí)關(guān)閉和銷毀游標(biāo)對(duì)象釋放他用到的資源。并且不在萬(wàn)不得已的情況下，不要隨意使用游標(biāo)，因?yàn)樗麜?huì)占用較多的系統(tǒng)資源，尤其是對(duì)于大并發(fā)量的情況下，很容易使得系統(tǒng)資源耗盡而崩潰。
使用臨時(shí)Table和游標(biāo)各有利弊，在使用的過(guò)程中要適當(dāng)?shù)睦眉纯桑?/p>
5、在存儲(chǔ)過(guò)程中調(diào)用外部的ActiveX DLL程序
有些特殊的情況下，我們可能會(huì)需要調(diào)用外部的ActiveX DLL程序，這個(gè)時(shí)候就需要使用到系統(tǒng)的存儲(chǔ)過(guò)程sp＿OACreate以及其他的相關(guān)系統(tǒng)存儲(chǔ)過(guò)程，都是以sp＿OA開頭的存儲(chǔ)過(guò)程，可以自由的在自己的存儲(chǔ)過(guò)程當(dāng)中調(diào)用ActiveX DLL的各種方法和屬性。比如下面的例子：
DECLARE?。纎bject int
DECLARE ＠hr int
DECLARE?。纏roperty varchar（255）
DECLARE ＠return varchar（255）
DECLARE?。纒rc varchar（255），?。纃esc varchar（255）
－－ 建立一個(gè)對(duì)象（SQLDMO．SQLServer）．
EXEC?。纇r ＝ sp＿OACreate?。QLDMO．SQLServer＇，?。纎bject OUT
IF?。纇r?。迹尽?
BEGIN
EXEC sp＿OAGetErrorInfo?。纎bject，?。纒rc OUT， ＠desc OUT
SELECT hr＝convert（varbinary（4），＠hr）， Source＝＠src， Description＝＠desc
RETURN
END
－－ 設(shè)置對(duì)象的屬性．
EXEC?。纇r?。健p＿OASetProperty ＠object，?。ostName＇，?。izmo＇
IF?。纇r?。迹尽?
BEGIN
EXEC sp＿OAGetErrorInfo?。纎bject， ＠src OUT， ＠desc OUT
SELECT hr＝convert（varbinary（4），＠hr）， Source＝＠src， Description＝＠desc
RETURN
END
－－ 通過(guò)OUTPUT參數(shù)獲取對(duì)象的屬性值．
EXEC ＠hr?。健p＿OAGetProperty ＠object， ＇HostName＇， ＠property OUT
IF?。纇r ＜＞ 0
BEGIN
EXEC sp＿OAGetErrorInfo?。纎bject， ＠src OUT，?。纃esc OUT
SELECT hr＝convert（varbinary（4），＠hr）， Source＝＠src， Description＝＠desc
RETURN
END
PRINT ＠property
－－ 調(diào)用對(duì)象的方法
EXEC?。纇r?。健p＿OAMethod?。纎bject，?。onnect＇， NULL，?。y＿server＇，?。y＿login＇， ＇my＿password＇
IF?。纇r?。迹尽?
BEGIN
EXEC sp＿OAGetErrorInfo?。纎bject， ＠src OUT，?。纃esc OUT
SELECT hr＝convert（varbinary（4），＠hr）， Source＝＠src， Description＝＠desc
RETURN
END
－－ 銷毀已經(jīng)創(chuàng)建的ActiveX對(duì)象
EXEC?。纇r ＝ sp＿OADestroy?。纎bject
IF ＠hr?。迹尽?
BEGIN
EXEC sp＿OAGetErrorInfo?。纎bject，?。纒rc OUT，?。纃esc OUT
SELECT hr＝convert（varbinary（4），＠hr）， Source＝＠src， Description＝＠desc
RETURN
END
6、在存儲(chǔ)過(guò)程中使用數(shù)據(jù)庫(kù)事務(wù)處理
在很多的情況下，我們?cè)诖鎯?chǔ)過(guò)程中都會(huì)遇到需要同時(shí)操作多個(gè)表的情況，這時(shí)候就需要避免在操作的過(guò)程中由于以外而造成的數(shù)據(jù)的不一致性。這時(shí)候就需要將操作多個(gè)表的操作放入到事務(wù)中進(jìn)行處理。
但是需要注意的是，不能在事務(wù)中使用return語(yǔ)句強(qiáng)行退出，這樣會(huì)引發(fā)事務(wù)的非正常錯(cuò)誤，不能保證數(shù)據(jù)的一致性。
并且，一旦將多個(gè)處理放入事務(wù)當(dāng)中，系統(tǒng)的處理速度會(huì)有所降低，所以應(yīng)當(dāng)將頻繁操作的多個(gè)可分割的處理過(guò)程放入到多個(gè)存儲(chǔ)過(guò)程當(dāng)中，這樣會(huì)大大提高系統(tǒng)的響應(yīng)速度，但是前提是不違背數(shù)據(jù)的一致性。
看完了上面的這些編寫SQL Server存儲(chǔ)過(guò)程當(dāng)中的技巧，相信對(duì)您或多或少會(huì)有些幫助，也希望通過(guò)上面的一些經(jīng)驗(yàn)總結(jié)，可以使得您在應(yīng)用SQL Server存儲(chǔ)過(guò)程的時(shí)候，有意識(shí)的可以避免一些彎路。
（以上所有程序在Windows Advance Server2000中文版+MS SQL Server7.0/2000中文版測(cè)試通過(guò)）
作者:蘇紅超
來(lái)源：月光博客
