SQL Server的T-SQL

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

 

本篇文章來源于 黑基網(wǎng)-中國最大的網(wǎng)絡安全站點 原文鏈接：http://www./lib/2006-08-07/18282.html