Oracle 外部表實戰：處理不固定欄位數的複雜平面檔案載入 (電話號碼多值拆分)

Oracle 外部表實戰：處理不固定欄位數的複雜平面檔案載入 (電話號碼多值拆分)

業務場景

平面檔案每行：empno, ename, phone1@@phone2@@phone3..., sal
電話欄位數量不固定，需拆分載入 EMP 主檔與 EMP_CONTACT 明細檔。

傳統 SQL*Loader 無法直接處理

SQL*Loader 要求固定欄位結構，無法動態拆分行內多值。

外部表 + SQL 兩階段解決方案

階段一：建立外部表對應原始檔案

CREATE OR REPLACE DIRECTORY my_dir AS '/home/tkyte';

CREATE TABLE et (
  "EMPNO"  VARCHAR2(10),
  "ENAME"  VARCHAR2(20),
  "TELNOS" VARCHAR2(1000),  -- 整段電話串
  "SAL"    VARCHAR2(10)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    CHARACTERSET US7ASCII
    BADFILE  'MY_DIR':'t.bad'
    LOGFILE  't.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "EMPNO"  CHAR(255) TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
      "ENAME"  CHAR(255) TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
      "TELNOS" CHAR(1000) TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"',
      "SAL"    CHAR(255) TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    )
  )
  LOCATION ('t.dat')
);

驗證外部表讀取

SELECT * FROM et;
-- EMPNO ENAME TELNOS                                  SAL
-- 12    smith 1234556@@1234567@@876556612             1200
-- 14    John  1234@@4567@@56789@@12345@@45778@@34566@@23456 1345

階段二：SQL 拆分電話串並一次性多表插入

-- 目標表
CREATE TABLE emp (empno NUMBER PRIMARY KEY, ename VARCHAR2(10), sal NUMBER);
CREATE TABLE emp_contact (empno NUMBER REFERENCES emp, phone_no NUMBER);

-- 核心邏輯：使用 CONNECT BY 生成序號，SUBSTR+INSTR 切分
INSERT ALL
  WHEN i = 1 THEN INTO emp (empno, ename, sal) VALUES (empno, ename, sal)
  WHEN i > 0 THEN INTO emp_contact (empno, phone_no) VALUES (empno, tno)
SELECT empno, ename, sal, i,
       SUBSTR(tnos, INSTR(tnos, '@@', 1, i) + 2,
              INSTR(tnos, '@@', 1, i + 1) - INSTR(tnos, '@@', 1, i) - 2) tno
FROM (
  SELECT TO_NUMBER(empno) empno, ename, TO_NUMBER(sal) sal,
         COLUMN_VALUE i,
         '@@' || telnos || '@@' tnos
  FROM et,
       TABLE(CAST(MULTISET(
         SELECT LEVEL FROM dual
         CONNECT BY LEVEL <= (LENGTH(telnos) - LENGTH(REPLACE(telnos, '@@', ''))) / 2 + 1
       ) AS SYS.ODCINUMBERLIST))
);

關鍵技術點解析

• CAST(MULTISET(...) AS SYS.ODCINUMBERLIST)：將查詢結果集轉為集合類型，供 TABLE() 展開
• COLUMN_VALUE：集合展開後的單一欄位名 (此處為序號 i)
• @@ || telnos || @@：首尾加分隔符，統一邊界處理邏輯
• INSERT ALL ... WHEN ... THEN INTO ...：單語句多表條件插入，保證事務一致性

執行結果驗證

SELECT * FROM emp;
-- EMPNO ENAME SAL
-- 12    smith 1200
-- 14    John  1345

SELECT * FROM emp_contact;
-- EMPNO PHONE_NO
-- 12    1234556
-- 12    1234567
-- 12    876556612
-- 14    1234
-- 14    4567
-- 14    56789
-- 14    12345
-- 14    45778
-- 14    34566
-- 14    23456

擴展建議

• 大檔案建議加 PARALLEL 子句於外部表定義
• 生產環境增加 REJECT LIMIT、錯誤日誌表 (DBMS_ERRLOG)
• 12c+ 可考慮 JSON_TABLE 或 XMLTABLE 處理結構化多值欄位