使用SQLLOADER將一行資料拆分為多行進行載入

SQL*LOADER支援將一行資料（物理行）拆分為多行（邏輯行）處理，本文進行測試驗證。

1,測試：
1.0測試表
create table multi_line(
x1 varchar2(100),x2 varchar2(100),x3 varchar2(100));

create table multi_line2(
x1 varchar2(100),x2 varchar2(100),x3 varchar2(100));

2.1測試
test1，一行拆為兩行，在同一表中儲存
cat normal_line.ctl

LOAD DATA
INFILE *
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3
)
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3
)

BEGINDATA
a1,b1,c1,d1,e1,f1,
a2,b2,c2,d2,e2,f2,
a3,b3,c3,d3,e3,f3,
a4,b4,c4,d4,e4,f4,
a5,b5,c5,d5,e5,f5,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
select * from multi_line;
SQL> select * from multi_line;
a1         b1         c1
a2         b2         c2
a3         b3         c3
a4         b4         c4
a5         b5         c5
d1         e1         f1
d2         e2         f2
d3         e3         f3
d4         e4         f4
d5         e5         f5

10 rows selected.

test2，一行拆為兩行，在兩個表中儲存
cat normal_line.ctl

LOAD DATA
INFILE *
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3
)
INTO TABLE multi_line2 TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3
)

BEGINDATA
a1,b1,c1,d1,e1,f1,
a2,b2,c2,d2,e2,f2,
a3,b3,c3,d3,e3,f3,
a4,b4,c4,d4,e4,f4,
a5,b5,c5,d5,e5,f5,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
SQL> select * from multi_line;
a1         b1         c1
a2         b2         c2
a3         b3         c3
a4         b4         c4
a5         b5         c5

SQL> select * from multi_line2；
d1         e1         f1
d2         e2         f2
d3         e3         f3
d4         e4         f4
d5         e5         f5