ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [zt] 利用 SQL Server 中的 Linked Server 執行 Oracle 中之預儲函數、程序

[zt] 利用 SQL Server 中的 Linked Server 執行 Oracle 中之預儲函數、程序

原创 Linux操作系统 作者:tolywang 时间:2009-06-11 09:57:08 0 删除 编辑

說明

MSSQL 提供的 linked server 功能,讓我們可以連接到其所支援的異質資料庫引擎,執行各種 DML 相關的 SQL 指令。

我們可以利用這個功能,讓原先使用 MSSQL 滿足應用面需求的程式,在面對必須同時使用來自 Oracle 資料庫之資料,才能滿足的需求時,無需程式設計師自行連結到 Oracle 資料庫,即可讓設計師完成該需求之實作。

[FIXME:這樣做明顯的好處在哪裡?]

建立 Oracle linked server

關於如何在 MSSQL 中,建立連接到 Oracle 資料庫的 linked server ,其相關之步驟,我們可在 MSSQL 的線上文件或是在網路文件中,找到巨細靡遺的說明,在此 shortie 就不再浪費篇幅介紹,基本上包含以下兩個主要的步驟:

  1. 在 MSSQL 所在的機器上,安裝合適的 Oracle 用戶端程式,並設定好可以連接至目標 Oracle 資料庫的連線資訊。
  2. 利用 MSSQL 提供的 Enterprise Manager 管理介面或是 sp_addlinkedserver 預儲函數,建立 linked server 。

使用 Oracle linked server

建立好連接至 Oracle 的 linked server 之後,只要再注意以下幾個容易犯錯的地方,我們便可在 DML 中,輕鬆使用 Oracle 中的資料庫物件。

  1. Oracle 資料庫物件必須使用完整的四部式命名,即 OracleLinkedServerName..OwnerUserName.ObjectName 。
      1. 如果 Oracle 資料庫物件建立時,未使用 " 字元包住,使用全部大寫的 ObjectName 。
      2. 反之,則使用與建立時相同大小寫之 ObjectName 。

範例:

假設我們的 Oracle linked server 名稱為 OraTest ,使用者為 ERD ,物件名稱為 CODEREF 資料表,則以下的 DML SQL 指令,都可以正確的在 Query Analyzer 中執行:

SELECT *
  FROM OraTest..ERD.CODEREF
 WHERE REFID = '168'
UPDATE OraTest..ERD.CODEREF
   SET DESC = DESC + '168'
 WHERE REFID = '168'
INSERT INTO OraTest..ERD.CODEREF (
                REFID, DESC, ....
            )
     VALUES (
                '538', '940', ...
            )
DELETE
  FROM OraTest..ERD.CODEREF
 WHERE REFID = '538'

大部分的需求都可以由這些 DML 來完成。

執行 Oracle linked server 中之預儲函數與程序

本文之標題重點就在這裡,而這也是屬於比較不容易完成的工作。

1. 首先,將 Oracle 中要開放給 MSSQL 呼叫的預儲函式,先用 Package 包裝起來:

Spec:

CREATE OR REPLACE PACKAGE pkg_MSSQL
AS

  TYPE ReturnSerialTbl IS TABLE OF VARCHAR2 (20)
  /* The index is important, otherwise {resultset} doesn't work. */
  INDEX BY BINARY_INTEGER;

  PROCEDURE pr_GetSerialNo (
    ASerialType    IN       VARCHAR2,
    ASerialOwner   IN       VARCHAR2,
    ASerialNo      OUT      ReturnSerialTbl
  );
  
END pkg_MSSQL;
/

Body:

CREATE OR REPLACE PACKAGE BODY pkg_MSSQL
AS

  PROCEDURE pr_GetSerialNo (
      ASerialType    IN       VARCHAR2,
      ASerialOwner   IN       VARCHAR2,
      ASerialNo      OUT      ReturnSerialTbl
  )
  IS
    VSerialNo   VARCHAR2 (20);
  BEGIN
    ERD.pr_GetSerialNo (ASerialType, ASerialOwner, VSerialNo);
    ASerialNo (1) := VSerialNo;
  END pr_GetSerialNo;
  
END pkg_MSSQL;
/

以上的例子,乃是將 Oracle 中的 pr_GetSerialNo 開放給 MSSQL 使用。

註:pr_GetSerialNo 是 shortie 常用的系統取號控制預儲程序。

2. 接著,從 MSSQL 利用 Dynamic SQL 呼叫 Oracle 中的預儲函式:

create table #serialtbl (
  SerialNo VARCHAR(20)
)

Declare
  @ASerialType  VARCHAR(200),
  @ASerialOwner VARCHAR(200),
  @OracleCall   VARCHAR(8000),
  @RetVal       VARCHAR(20)

SET @ASerialType = 'ID'
SET @ASerialOwner = 'BB_BloodOrderId'

SET @OracleCall = 'INSERT INTO #serialtbl '
SET @OracleCall = ' SELECT * '
SET @OracleCall = ' FROM OPENQUERY(MYORACLE , ''{CALL ERD.pkg_mssql.pr_getserialno( '
SET @OracleCall = @OracleCall + ''''''+ @ASerialType + ''''''
SET @OracleCall = @OracleCall + ',' + '''''' +  @ASerialOwner + ''''''
SET @OracleCall = @OracleCall + ',{RESULTSET 25, ASerialNo})}'')'

PRINT @ORACLECALL

EXEC (@OracleCall)

SELECT @RetVal = SerialNo FROM #SerialTbl

DROP TABLE #SerialTbl

PRINT @RetVal
執行結果
INSERT INTO #serialtbl
    SELECT *
    FROM OPENQUERY(oratest_erd ,
         '{CALL ERD.pkg_mssql.pr_getserialno(''ID'',''BB_BloodOrderId'',{RESULTSET 25, ASerialNo})}'
)

(1 row(s) affected)
O000000016

結果顯示執行成功,傳回的序號值為 '0000000016' 。

結論

本文說明在 Mircrosoft SQL Server 中,透過 Linked Server 連接至 Oracle ,然後呼叫 Oracle 中的 Stored Procedure/Function 。基本上,對 shortie 個人而言,知道這個做法純粹是滿足個人「知性」的需求,真正面臨到這個問題時, shortie 應該會使用其他的解決方案,因為大部分 shortie 碰到的 DBA 都會要求自己管理的 DBMS 要「守身如玉」,與其跟這些不入流的 DBA 去 Fight ,還不如繞開他們,山不轉路轉,路不轉人轉!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-605855/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13422818