linux将数据一行分为两行,使用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