使用外部表關聯MySQL資料到Oracle

因為業務需要，有個臨時的活動需要DBA來支援一些資料業務，問題來了，需要從MySQL端同步一部分資料到Oracle端，然後從Oracle端匹配查到相應的資料返回給MySQL，至於原因，也是不同的業務系統，不同的許可權分配，還沒法做到一個應用端去讀取這些資訊，而且也有安全的考慮，大體就是兩部分的資料也是互相補充，但又彼此獨立，是一個全集和子集的關係。
這個流程本來從開發的角度來看似乎是一頭霧水，所以交給他們來規劃就容易出現問題，最後溝通後的流程是下面的形式。
下面這個圖左邊是Oracle的環境，右邊是MySQL的環境，兩個環境的表中都存在一個共同的欄位就是序列號serial_no，而且MySQL段的序列號是Oracle端的子集，兩者是存在一一對映關係的。

現在的問題是MySQL端可以提供uid，但是無法得知cn_number,因為這部分資訊在Oracle端。Oracle端又沒有uid的概念，所以需要MySQL端提供serial_no來對映才可以。
所以一來二去，得到的流程就是需要5個步驟。
首先開發部門提供需要的uid（1），然後MySQL端抽取後把檔案同步到Oracle端(2),然後在Oracle端進行關聯查詢，得到一個uid和cn_number的組合（3），然後同步到MySQL端，
最後MySQL端得到這部分資料，最終開發的需求就完成了。

很快就得到了MySQL端同步過來的資料，是個本文檔案，內容如下：
687914 | 0d6c3956-d53d-4e14-9fba-cb73cec661e6
694786 | 41159bb3-970b-4b6b-9c5d-46e1f3d388be
746010 | 011d632d-149a-4e3d-ad00-dcae53f60825
1226533| 78dd80d3-6ad0-4bd5-aa1d-843c32b7ddab
1399846| 3dcc5982-bcc7-4cbf-9f99-b5a51b932b1d
1400221| 4fc505eb-20a6-451c-8674-5667e33167e7
因為推送過來的表的資料可能會有變化的，但是每次都去更新表的資料還是有些繁瑣，一個方便的辦法就是外部表了。
CREATE TABLE passport_ext
      (uid    number,
       serial_no  varchar2(100))
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_dir
      ACCESS PARAMETERS
        (
        FIELDS TERMINATED BY '|'        
        )
      LOCATION ('mysql_uid_serial_no.lst')
     );
所以一旦MySQL端推送檔案過來了，我只需要替換文字檔案即可，然後就不用反覆做資料的匯入了。
直接拿過來關聯即可。
但是建立外部表的時候老師拋錯，最後發現uid是保留字，用下面的例子來驗證。
>  CREATE TABLE passport_ext (uid varchar2(10));
 CREATE TABLE passport_ext (uid varchar2(10))
                                          *
ERROR at line 1:
ORA-00904: : invalid identifier
>  CREATE TABLE passport_ext (uidd varchar2(10));
Table created.
其實後面經過老貓指點，還是可以用"uid"來代替的，這個用法就跟MySQL裡面的反引號類似了。
create table test1(`int` int);
Query OK, 0 rows affected (0.00 sec)
不過這個時候還是要注意。下面的輸出結果，其實如果用雙引號，還是不規範的，而且需要應用端去修改，這樣就是一個隱藏的雷。多謝懷總指點。
SQL> create table test("uid" number);
Table created.
SQL> select uid,UID,"uid" from test;
UID UID uid
---------- ---------- ----------
0 0 1
所以在資料匯入之後還是最好把欄位名改過來，我就直接改成了uidd,因為欄位名錶關聯沒有強制要求uid這個列名。
關於保留字可以通過下面的方式來查詢
SQL> select * from v$reserved_words where keyword='UID';
外部表載入了之後，關聯的時候發現竟然沒有匹配的資料，最後發現還是得trim一下資料
select t1.uidd ,t2.cn_number from passport_ext t1,passport t2 where trim(t1.serial_no)=t2.seriao_no;
通過這種方式就得到了一個資料清單，可以再次推送給MySQL端了。
這個案例還是很簡單的，但是把這個過程做了多步的分解，可以看出在資料遷移中還是有很多的潛在因素需要考慮。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1816455/，如需轉載，請註明出處，否則將追究法律責任。