使用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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-743753/，如需转载，请注明出处，否则将追究法律责任。