SQL Server的T-SQL
SQL Server的T-SQL編程語言在數(shù)據(jù)存儲(chǔ)和恢復(fù)方面功能強(qiáng)大，但在與SQL Server數(shù)
據(jù)庫(kù)之外的系統(tǒng)交互方面則功能較弱。然而，我們可以通過SQL Server內(nèi)置的COM自動(dòng)操作環(huán)
境來克服這個(gè)限制，SQL Server內(nèi)置的COM自動(dòng)操作環(huán)境可以使用戶在存儲(chǔ)過程中自動(dòng)操作
COM對(duì)象。在SQL Server 7.0和SQL Server 6.5中提供了7個(gè)擴(kuò)展的存儲(chǔ)過程，可以通過自己開
發(fā)的或Office等現(xiàn)成的COM對(duì)象擴(kuò)展SQL Server的功能。SQL Server還提供了一種錯(cuò)誤處理機(jī)
制，可以把出錯(cuò)信息寫到SQL代理日志中。利用COM自動(dòng)化操作服務(wù)，還可以把SQL Server與微
軟的Exchange Server、Index Server和其他可以通過COM自動(dòng)化操作服務(wù)控制其他軟件進(jìn)行集
SQL Server 6.5引進(jìn)了對(duì)象自動(dòng)操作環(huán)境，它最初被稱作OLE。隨著時(shí)間的變遷對(duì)象
操作的名稱也有所變化，然而與SQL Server 6.5相比，SQL Server 7.0中的自動(dòng)操作環(huán)境沒有
改變，因此微軟的文檔中仍然把這一功能稱作OLE操作而不是COM操作，在查閱SQL Server在
線手冊(cè)（BOL）時(shí)尤其需要注意這一點(diǎn)。下面我們來討論如何使用SQL Server的COM自動(dòng)操作
存儲(chǔ)過程以及COM自動(dòng)操作如何幫助我們解決現(xiàn)實(shí)的編程問題。
COM操作的細(xì)節(jié)
表1列出了SQL Server中的7個(gè)用于COM操作的擴(kuò)展存儲(chǔ)過程。當(dāng)自動(dòng)操作一個(gè)COM對(duì)
象時(shí)，需要首先通過調(diào)用sp_OACreate建立一個(gè)COM對(duì)象的實(shí)例，然后通過一系列的
sp_OAGetProperty、sp_OASetProperty和sp_OAMethod調(diào)用完成需要完成的任務(wù)，在完成對(duì)
COM對(duì)象的操作后，還需要調(diào)用sp_OADestroy釋放該對(duì)象。在詳細(xì)地研究每個(gè)儲(chǔ)存過程時(shí)，請(qǐng)
注意二個(gè)很重要的問題。第一，必須提供調(diào)用的所有參數(shù)，因?yàn)樽詣?dòng)操作功能不支持有名參數(shù)
，如果不能使用一個(gè)詳細(xì)的參數(shù)，需要向它傳遞一個(gè)NULL作為占位符；第二，每個(gè)調(diào)用返回一
個(gè)整數(shù)類型的HRESULT，如果調(diào)用成功則該值為0。在后面，我們將討論如何處理返回值為非
存儲(chǔ)過程 描述
sp_OACreate 建立自動(dòng)操作對(duì)象的一個(gè)實(shí)例
sp_OADestroy 釋放一個(gè)對(duì)象的實(shí)例
sp_OAGetErrorInfo 從其他過程返回的HRESULT中獲得錯(cuò)誤描述信息
sp_OAGetProperty 把一個(gè)對(duì)象的屬性存儲(chǔ)在結(jié)果集或局部變量中
sp_OASetProperty 改變一個(gè)對(duì)象屬性的值
sp_OAMethod 執(zhí)行對(duì)象的方法，向方法傳遞參數(shù)，并得到返回值
sp_OAStop 關(guān)閉SQL Server的自動(dòng)操作環(huán)境
表 1: SQL Server的COM自動(dòng)操作存儲(chǔ)過程
COM操作必須以調(diào)用sp_OACreate存儲(chǔ)過程開始，語法格式如下所示：
sp_OACreate progid | clsid, objecttoken OUT.PUT,
[context:]
第一個(gè)參數(shù)是程序ID（ProgID━━一個(gè)應(yīng)用程序名.類名形式的字符串，例如
Excel.Application，）或者一個(gè)類ID（CLSID━━一個(gè)nnnnnnnn-nnn
n-nnnn-nnnn-nnnnnnnnnnnn形式的全球唯一的ID），它標(biāo)明你希望創(chuàng)建實(shí)例的COM對(duì)象。在可
能的情況下，我建議使用ProgID參數(shù)，因?yàn)樗子谳斎牒陀洃?。你?huì)發(fā)現(xiàn)，只有很少的希望自
動(dòng)操作的對(duì)象沒有ProgID，如果偶爾碰上這樣的對(duì)象，就只有使用CLSID了。第二個(gè)變量
objecttoken也是一個(gè)整型變量，一個(gè)對(duì)象的標(biāo)記是指向SQL Server創(chuàng)建的對(duì)象的句柄和指針
，我們需要在隨后的對(duì)對(duì)象的自動(dòng)操作中使用這個(gè)返回的對(duì)象標(biāo)記來確定這個(gè)對(duì)象。最后的
context變量是可選的，可以強(qiáng)迫創(chuàng)建的對(duì)象使用某種自動(dòng)操作的機(jī)制。如果其值為1，則要
求對(duì)象在一個(gè)ActiveX DLL文件中；值為4，則要求對(duì)象在ActiveX EXE服務(wù)器中；如果是缺省
的值5，則可以使用任一自動(dòng)操作。在這里我們建議使用缺省的選項(xiàng)，而無須為context參數(shù)
提供一個(gè)恰當(dāng)?shù)闹?。下面調(diào)用op_OACreate 的命令將創(chuàng)建一個(gè)微軟的Excel程序的實(shí)例：
Declare @Object int
Declare @RetVal int
Exec @RetVal=sp_OACreate 'Excel.Application',
@Object OUTPUT
在創(chuàng)建一個(gè)對(duì)象后，需要獲取其一些屬性。要得到這些屬性，可以通過下面的語法調(diào)
sp_OAGetProperty：
sp_OAGetProperty objecttoken, propertyname[, propertyvalue OUTPUT] [,
第一個(gè)參數(shù)objecttoken的值就是由sp_OACreate返回的值，參數(shù)Propertyname是我
們希望獲取的屬性。在獲取這個(gè)值是有幾種選擇，如果該屬性是一個(gè)單一的值，可以把它存儲(chǔ)
在一個(gè)變量中，或者把它作為一個(gè)單行、單字段的結(jié)果集；如果屬性值是一個(gè)一維或二維的數(shù)
組，則必須把它作為一個(gè)結(jié)果集；如果如果該屬性的值是一個(gè)多于二維的數(shù)組，
sp_OAGetProperty就不能返回它的值，會(huì)出現(xiàn)一個(gè)錯(cuò)誤。要返回一個(gè)結(jié)果集，只須簡(jiǎn)單地不指
定propertyvalue參數(shù)的值即可（如果需要它有一個(gè)值以便使用index參數(shù)，就把NULL賦給它
好了。），否則的話，應(yīng)該賦給propertyvalue一個(gè)適當(dāng)?shù)念愋偷闹?，并且一定要把該參?shù)標(biāo)
記為OUTPUT。如果你訪問的屬性是一個(gè)集合，就需要使用index參數(shù)指定這個(gè)集合中一個(gè)特定
的數(shù)字。如果一個(gè)對(duì)象的屬性是另一個(gè)對(duì)象，就應(yīng)該把這個(gè)對(duì)象存入一個(gè)整數(shù)型變量中，
sp_OAGetProperty返回的也是一個(gè)對(duì)象標(biāo)記，不過與sp_OACreate返回的并不相同。我們可以
使用這個(gè)對(duì)象標(biāo)記對(duì)存儲(chǔ)過程返回的任何對(duì)象進(jìn)行自動(dòng)化操作。下面的命令調(diào)用
sp_OAGetProperty把一個(gè)名字為DefaultFilePath的屬性值存入變量@DFP中：
Exec sp_OAGetProperty @Object, 'DefaultFilePath',
@DFP OUTPUT
可以通過如下格式使用sp_OASetProperty存儲(chǔ)過程改變一個(gè)對(duì)象的屬性值：
sp_OASetProperty objecttoken, propertyname,newvalue [, index]
第一個(gè)參數(shù)objecttoken是由sp_OACreate返回的，參數(shù)Propertyname是要改變的對(duì)象的屬
性名字，Newvalue參數(shù)是想賦給屬性的新變量，可以是一個(gè)變量或一個(gè)文字值。如果設(shè)定的屬
性值是作為一個(gè)集合的一個(gè)對(duì)象，可以使用可選的index參數(shù)來指定這個(gè)集合的一個(gè)特定的位
置。下面的命令調(diào)用sp_OASetProperty把名字為FixedDecimalPlaces的屬性設(shè)置為6：
Exec sp_OASetProperty @Object, 'FixedDecimalPlaces', 6
可以用下面的語法調(diào)用sp_OAMethod存儲(chǔ)過程執(zhí)行一個(gè)對(duì)象的方法：
sp_OAMethod objecttoken, methodname [, returnvalue OUTPUT] [,
Sp_OAMethod是最靈活的，因而也是最復(fù)雜的自動(dòng)操作存儲(chǔ)過程，我們甚至可以用它象調(diào)
用一個(gè)方法那樣調(diào)用一個(gè)屬性，而且還能得到一個(gè)返回值，當(dāng)然，我們也能使用
sp_OAGetProperty來完成這一任務(wù)。該存儲(chǔ)過程的第一個(gè)參數(shù)objecttoken是由sp_OACreate返
回的對(duì)象標(biāo)記，參數(shù)methodname是希望執(zhí)行的方法的名字，如果該方法有返回值，則下一個(gè)參
數(shù)returnvalue應(yīng)當(dāng)是一個(gè)包含該方法返回值的適當(dāng)類型的變量；如果返回值是一個(gè)一維或二
維的數(shù)組，則用NULL作為一個(gè)占位符，該過程將返回一個(gè)結(jié)果集。該存儲(chǔ)過程不能返回一個(gè)超
過二維的數(shù)組作為結(jié)果集合，在這種情況下，SQL Server就會(huì)出錯(cuò)。如果該方法沒有返回類型
如果調(diào)用的方法需要參數(shù)，就需要在調(diào)用sp_OAMethod時(shí)提供這些參數(shù)。如果方法允
許按順序提供參數(shù)，則按要求的順序列出每個(gè)參數(shù)，并用逗號(hào)分隔每個(gè)參數(shù)，還可以用變量或
文字變量作為參數(shù)。如果需要使用有名參數(shù)，SQL Server也提供了相應(yīng)的機(jī)制，只需使用
@變量名=變量值
的形式列出所需的變量即可。需要注意的是不要因?yàn)橛蠤前綴而把變量名當(dāng)作局部變量，
當(dāng)調(diào)用存儲(chǔ)過程sp_OAMethod時(shí)，SQL Server就會(huì)解析出@，因此，即使在調(diào)用的方法中有名字
為HostName的參數(shù)時(shí)，仍然可以使用名字為@HostName的局部變量。
下面是二個(gè)調(diào)用sp_OAMethod的例子。第一個(gè)例子調(diào)用一個(gè)名字為Ce
ntimetersToPoints的方法，它只接受在@CMVal變量中提供的一個(gè)參數(shù)，返回的值存儲(chǔ)在變量
@RetVal中。第二個(gè)例子調(diào)用一個(gè)名字為MailLogon的方法，它接受三個(gè)可選的變量，這個(gè)例
子中根據(jù)名字接受二個(gè)變量，把Name設(shè)置為字符串"MyUserName"，把 Password設(shè)置為字符串
Exec sp_OAMethod @Object, 'CentimetersToPoints',@RetVal OUTPUT, @CMVal
Exec sp_OAMethod @Object, 'MailLogon', NULL,@Name='MyUserName',
不再使用一個(gè)對(duì)象后，需要通過下面的語法調(diào)用存儲(chǔ)過程sp_OADestroy釋放對(duì)該對(duì)象的引
sp_OADestroy objecttoken
調(diào)用sp_OADestroy存儲(chǔ)過程可以釋放由參數(shù)objecttoken指定的對(duì)象，同時(shí)還釋放這
個(gè)對(duì)象所使用的內(nèi)存和其他資源。下面是一個(gè)調(diào)用sp_OADestroy的命令：
Exec sp_OADestroy @Object
需要注意的是，T-SQL中的數(shù)據(jù)類型與其他的編程語言并非是一一對(duì)應(yīng)的，在調(diào)用一
個(gè)需要特定的數(shù)據(jù)類型的方法時(shí)就可能出錯(cuò)。"數(shù)據(jù)類型轉(zhuǎn)換"工具條可以將SQL Server的數(shù)據(jù)
錯(cuò)誤處理
象在前面提到的那樣，如果對(duì)存儲(chǔ)過程的調(diào)用成功了，則會(huì)返回一個(gè)為0的HRESULT值
，其他的HRESULT值則意味著發(fā)生了錯(cuò)誤。要判斷一個(gè)非零的HRESULT值，可以把HRESULT值傳
sp_OAGetErrorInfo [objecttoken] [, source OUTPUT] [, description OUTPUT]
第一個(gè)參數(shù)objecttoken是由sp_OACreate返回的對(duì)象標(biāo)記。下面的四個(gè)參數(shù)返回錯(cuò)誤
信息。Source是產(chǎn)生這一錯(cuò)誤信息的應(yīng)用程序或庫(kù)，Description是該錯(cuò)誤的描述，如果有幫
助文件的話，則該Helpfile是幫助文件的路徑。這三個(gè)參數(shù)都是有符號(hào)或無符號(hào)字符型數(shù)據(jù)，
sp_OAGetErrorInfo會(huì)根據(jù)定義的變量的大小截取返回的值。最后一個(gè)參數(shù)helpid是特定錯(cuò)誤
在幫助文件中的索引號(hào)。下面的命令調(diào)用sp_OAGetErrorInfo以獲得某一個(gè)錯(cuò)誤的更詳細(xì)的信
Declare @Source varchar(100), @Description varchar(255), @HelpFile
Exec sp_OAGetErrorInfo @Object, @Source OUTPUT, @Description OUTPUT,
SQL Server在線手冊(cè)還提供了一個(gè)有關(guān)sp_DisplayOAErrorInfo存儲(chǔ)過程的例子，該
存儲(chǔ)過程可以調(diào)用sp_OAGetErrorInfo把返回的值組織成格式化的字符串，以便把該信息寫入
日志文件中。關(guān)于sp_DisplayOAErrorInfo的更詳細(xì)的信息，請(qǐng)參閱工具條， 另外，調(diào)用
sp_OAStop儲(chǔ)存過程可以關(guān)閉SQL Server的COM自動(dòng)操作環(huán)境，它無需任何參數(shù)。關(guān)閉自動(dòng)操
作環(huán)境在大多數(shù)情況下并非是必需的，第一次調(diào)用sp_OACreate時(shí)自動(dòng)操作環(huán)境會(huì)自動(dòng)開啟，
SQL Server關(guān)閉時(shí)自動(dòng)操作環(huán)境也會(huì)自動(dòng)關(guān)閉。如果一個(gè)存儲(chǔ)過程正在對(duì)一個(gè)對(duì)象進(jìn)行自動(dòng)操
作，而另一個(gè)過程調(diào)用sp_OAStop時(shí)就會(huì)出現(xiàn)錯(cuò)誤，因此我們不建議在程序中調(diào)用sp_OAStop
，只有在調(diào)試一個(gè)沒有運(yùn)行的過程時(shí)，才可以通過一個(gè)查詢窗口調(diào)用它。
在實(shí)際工作中使用COM自動(dòng)操作
至此，我們已經(jīng)學(xué)習(xí)了如何使用每一個(gè)COM自動(dòng)操作存儲(chǔ)過程，我們現(xiàn)在來討論一下
一個(gè)綜合應(yīng)用它們的例子。程序清單1是一個(gè)名字為sp_OpenWordIfCoProcAvailable的過程，
在這個(gè)過程中，我們用sp_OACreate創(chuàng)建了一個(gè)Microsoft Word的實(shí)例，然后使用
sp_OAGetProperty來獲取Word的MathCoProcessorAvailable屬性，如果sp_OAGetProperty返回
1，則sp_OpenWordIfCoProcAvailable向調(diào)用過程返回Word對(duì)象的對(duì)象標(biāo)記；否則，
sp_OpenWordIfCoProcAvailable關(guān)閉Word，并返回0。為了節(jié)省版面，我們只調(diào)用了出錯(cuò)處理
過程一次，在實(shí)際應(yīng)用中，應(yīng)該在每次調(diào)用自動(dòng)操作存儲(chǔ)過程后都調(diào)用出錯(cuò)處理過程。注意，
為對(duì)Word進(jìn)行自動(dòng)操作，應(yīng)該在安裝SQL Server的機(jī)器上安裝Word。
程序清單 1：自動(dòng)操作Word的方法的例子
Create Procedure sp_OpenWordIfCoProcAvailable As
Declare @Object int, @hr int, @RetVal int
Exec @hr = sp_OACreate 'Word.Application', @Object OUTPUT
BEGIN
Exec sp_DisplayOAErrorInfo @Object, @hr
Return 0
END
Exec @hr = sp_OAGetProperty @Object, 'MathCoProcessorAvailable', @RetVal
If @hr=0
BEGIN
