Transact-SQL SQL Server 新增、修改、刪除、查詢 ORACLE 資料 (OLE DB Provider：OraOLEDB.Oracle)

本文將介紹如何以 SQL Server 指令對 ORACLE 資料進行新增、修改、刪除、查詢等作業 (OLE DB Provider：OraOLEDB.Oracle)。


測試環境

(此處將 SQL Server Express、ORACLE Express 安裝於同一台電腦)
Windows XP Professional SP3、SQL Server 2008 Express、ORACLE 11g Express (安裝步驟請參考此篇文章)


ORACLE 部分

假設已存在一個名為 JERRY 的 Workspace，使用以下指令建立資料表 TESTDATA。

CREATE TABLE TEST (SN int, FIRST_NAME nvarchar2(20), LAST_NAME varchar2(20), BIRTHDAY timestamp);

SQL Server 部分

1) 於 SQL Server 中，建立遠端 ORACLE 伺服器設定 (使用 OLE DB)

use master;

-- 檢查遠端伺服器的連接設定 Oracle_test01 是否已存在；若存在，則將其刪除。
if exists (select name from sys.servers where server_id <> 0 and name = N'Oracle_test01') 
 exec sp_dropserver @server = N'Oracle_test01', @droplogins = 'droplogins' ;
  
-- 建立遠端伺服器的連接設定 Oracle_test01
exec sp_addlinkedserver @server     = 'Oracle_test01', 
                        @srvproduct = 'XE',     --> wxy: XE 來自 tnsnames.ora 的 SERVICE_NAME
                        @provider   = 'OraOLEDB.Oracle', 
                        @datasrc    = 'XE ';    --> wxy: XE 來自 tnsnames.ora 的 SERVICE_NAME

exec sp_addlinkedsrvlogin @rmtsrvname = 'Oracle_test01', @useself = 'FALSE', @rmtuser = 'SYSTEM', @rmtpassword = 'Oracle 密碼' ;

-- 建立 SQL Server 伺服器與遠端伺服器的登入對應 
exec sp_addlinkedsrvlogin @rmtsrvname  = N'Oracle_test01', 
                          @locallogin  = N'sa', 
                          @useself     = N'False', 
                          @rmtuser     = N'SYSTEM', 
                          @rmtpassword = N'Oracle 密碼' ;
  
-- 當上述對應關係不存在時，此二伺服器間不建立其他連接。
exec sp_droplinkedsrvlogin @rmtsrvname = N'Oracle_test01', @locallogin = NULL ;

2) 新增

insert openquery (Oracle_test01, 'select SN, FIRST_NAME, LAST_NAME, BIRTHDAY from JERRY.TEST') 
   values (1, 'Tom', 'Smith', '1988/10/10 01:02:03.456') ;
   
insert openquery (Oracle_test01, 'select SN, FIRST_NAME, LAST_NAME, BIRTHDAY from JERRY.TEST') 
   values (2, '約翰', 'Smith', '1990/01/01 01:02:03.456') ;

3) 查詢

select * from openquery(Oracle_test01,'select * from JERRY.TEST');

select * from openquery(Oracle_test01,'select * from JERRY.TEST where FIRST_NAME LIKE ''%翰%'' ');

select * from openquery(Oracle_test01,'select * from JERRY.TEST where BIRTHDAY < to_date(''1990/01/01 00:00:00'',''yyyy/mm/dd hh24:mi:ss'') ');

4) 修改

update openquery (Oracle_test01, 'select FIRST_NAME from JERRY.TEST where FIRST_NAME = ''約翰'' ') 
   SET FIRST_NAME = 'John';

update openquery (Oracle_test01, 'select BIRTHDAY from JERRY.TEST where BIRTHDAY < to_date(''1990/01/01 00:00:00'',''yyyy/mm/dd hh24:mi:ss'') ')
   SET BIRTHDAY = '2000/12/12 00:00:00.000';
   
-- 檢查結果
select * from openquery(Oracle_test01,'select * from JERRY.TEST');

5) 刪除

delete openquery (Oracle_test01, 'select * from JERRY.TEST where FIRST_NAME = ''Tom''');

delete openquery (Oracle_test01, 'select * from JERRY.TEST where BIRTHDAY >= to_date(''1990/01/01 00:00:00'',''yyyy/mm/dd hh24:mi:ss'') ');

-- 檢查結果
select * from openquery(Oracle_test01,'select * from JERRY.TEST');