使用ORACLE外部表装载复杂数据

原文：http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13asktom⑴886639.html

I am using SQL Loader to load data into tables from a flat file. Some sample data in the file might be:

我打算使用SQL Loader装载来自平面文件数据。样例数据以下：

12,smith,1234556@@1234567 @@876556612,1200
14,John,1234@@4567@@56789 @@12345@@45778@@34566@@23456,1345


The field values are empno, empname, phone numbers, and salary. The table structures are

表结构以下：

create table emp ( empno number(5) primary key, ename varchar2(10), Sal number(10,2) )
create table emp_contact ( empno references emp, phone_no number(10) )


I want to insert the data into the emp and emp_contact tables. I don’t know how many values for phone_no the file contains (the number of values for phone_no is not fixed). How do I insert the data into the tables?
我想将以上数据分别装入2个表中: emp和emp_contact
问题是：我没法肯定第3列（电话号码）有多少个，此列不固定。我将如何将数据装载进表？


这个问题很狡猾，看上去我们必须将第3列拆成多行插入。
对号称“21世纪奇异数据装载工具”--SQL Loader 来说也没法直接实现！！！
此处，我建议使用21世纪数据装载工具--外部表 解决。思路是：将平面文件装入外部表，然后通过编写SQL进行拆分，最后按特定插入规则1次性插入指定表中。
下面，看我演示：
-⑴）进行数据装载
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'
   )
 )
/


SQL> select * from et;


EMPNO  ENAME   TELNOS                                          SAL
―――――  ―――――   ―――――――――――――――――――――――――――――――――――――――――――――   ――――
12     smith   1234556@@1234567@@876556612                     1200
14     John    1234@@4567@@56789@@12345@@45778@@34566@@23456   1345


-⑵）编写拆分SQL
SQL> 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(et.empno) empno,
       et.ename,
       to_number(et.sal) sal,
       column_value i,
       '@@'||et.telnos||'@@' tnos
  from et,
       table( cast( multiset(
          select level
            from dual
         connect by level <=
            (length(et.telnos)
               -length(replace(et.telnos,'@@','')))/2+1 )
             as sys.odciNumberList ) )
       )
/


EMPNO  ENAME    SAL    I  TNO
―――――  ―――――   ――――   ――  ―――――――
   12  smith   1200    1  1234556
   12  smith   1200    2  1234567
   12  smith   1200    3  876556612
   14  John    1345    1  1234
   14  John    1345    2  4567
   14  John    1345    3  56789
   14  John    1345    4  12345
   14  John    1345    5  45778
   14  John    1345    6  34566
   14  John    1345    7  23456


10 rows selected.


--注意：这里使用了cast multiset语法，column_value是TABLE(odciNumberList)中1列




-⑶）编写插入SQL
SQL> create table emp
  2  ( empno number primary key,
  3    ename varchar2(10),
  4    sal   number
  5  );
Table created.


SQL> create table emp_contact
  2  ( empno    number references emp,
  3    phone_no number
  4  );
Table created.

SQL> 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(et.empno) empno,
       et.ename,
       to_number(et.sal) sal,
       column_value i,
       '@@'||et.telnos||'@@' tnos
  from et,
       table( cast( multiset(
          select level
            from dual
         connect by level <=
            (length(et.telnos)
               -length(replace(et.telnos,'@@','')))/2+1 )
             as sys.odciNumberList ) )
       )
/
12 rows created.


SQL> select * from emp;


EMPNO  ENAME    SAL
―――――  ――――――  ―――――
   12  smith   1200
   14  John    1345


SQL> 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

10 rows selected.

------------------------------------

Dylan    Presents.