使用SQLLOADER将多行数据合并为一行进行加载

SQL*LOADER支持将多行数据（物理行）合并为一行（逻辑行）处理，本文进行测试验证。

1，语法说明：
1.1，使用CONCATENATE合并固定行数为一行
format:
CONCATENATE  integer

1.2，使用CONTINUEIF根据条件将多行合并为一行
format:
CONTINUEIF THIS/NEXT [PRESERVE] (pos1:pos2) {=|<>|!=} {str|X'hex_str'}
CONTINUEIF LAST [PRESERVE]  {=|<>|!=} {str|X'hex_str'}

比如,如果每个逻辑记录的第二列到第五列为“$|$|”，如下格式将数据中包含换行符的多行数据合并为一行：
CONTINUEIF NEXT PRESERVE (2:5) != '$|$|'

2,测试：
2.0测试表

create table multi_line(
x1 varchar2(100),x2 varchar2(100),
x3 varchar2(100),x4 varchar2(100),
x5 varchar2(100),x6 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,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,
a2,b2,c2,
a3,b3,c3,
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 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
a6         b6         c6
a7         b7         c7
a8         b8         c8
a9         b9         c9

9 rows selected.

test2:
cat fixed_num_lines.ctl

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

BEGINDATA
a1,b1,c1,
a2,b2,c2,
a3,b3,c3,
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         a2         b2         c2
a3         b3         c3         a4         b4         c4
a5         b5         c5         a6         b6         c6
a7         b7         c7         a8         b8         c8
a9         b9         c9

test3:
cat conif_lines.ctl

LOAD DATA
INFILE *
continueif next (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
           b1         c1         a2         b2         c2
           b3         c3         a4         b4         c4
           b5         c5
           b6         c6         a7         b7         c7
           b8         c8
           b9         c9

6 rows selected.

--&gtx1 is null!

test4:
cat conif_lines.ctl

LOAD DATA
INFILE *
continueif next preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         --a2       b2         c2
a3         b3         c3         --a4       b4         c4
a5         b5         c5
a6         b6         c6         --a7       b7         c7
a8         b8         c8
a9         b9         c9

6 rows selected.

test5:
cat conif_lines.ctl

LOAD DATA
INFILE *
continueif next preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4 "replace(:x4,'--','')",
  x5,
  x6
)

BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         a2         b2         c2
a3         b3         c3         a4         b4         c4
a5         b5         c5
a6         b6         c6         a7         b7         c7
a8         b8         c8
a9         b9         c9

6 rows selected.

test6:
cat conif_lines.ctl

LOAD DATA
INFILE *
continueif this preserve (1:2) = '--'
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,
--a2,b2,c2,
a3,b3,c3,
--a4,b4,c4,
a5,b5,c5,
a6,b6,c6,
--a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1
--a2       b2         c2         a3         b3         c3
--a4       b4         c4         a5         b5         c5
a6         b6         c6
--a7       b7         c7         a8         b8         c8
a9         b9         c9

6 rows selected.

test7:
cat conif_lines.ctl

LOAD DATA
INFILE *
continueif last  (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,-
a2,b2,c2,
a3,b3,c3,-
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,-
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         -a2        b2         c2
a3         b3         c3         -a4        b4         c4
a5         b5         c5
a6         b6         c6         -a7        b7         c7
a8         b8         c8
a9         b9         c9

6 rows selected.

--&gt虽没有preserve关键词，“-”仍然保留

 

test7:
--“-”后面加空格

cat conif_lines.ctl

LOAD DATA
INFILE *
continueif last  (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,-  
a2,b2,c2,
a3,b3,c3,-  
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,- 
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

 

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         -  a2      b2         c2
a3         b3         c3         -  a4      b4         c4
a5         b5         c5
a6         b6         c6         -  a7      b7         c7
a8         b8         c8
a9         b9         c9

6 rows selected.

--&gt虽没有preserve关键词，“-”仍然保留，而且空格也保留了

test8:
--“-”后面加空格，增加preserve

cat conif_lines.ctl

LOAD DATA
INFILE *
continueif last preserve (= '-')
INTO TABLE multi_line TRUNCATE
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( x1,
  x2,
  x3,
  x4,
  x5,
  x6
)

BEGINDATA
a1,b1,c1,-  
a2,b2,c2,
a3,b3,c3,-  
a4,b4,c4,
a5,b5,c5,
a6,b6,c6,- 
a7,b7,c7,
a8,b8,c8,
a9,b9,c9,

 

set linesize 100
set pagesize 0
col x1 for a10
col x2 for a10
col x3 for a10
col x4 for a10
col x5 for a10
col x6 for a10
SQL> select * from multi_line;
a1         b1         c1         -  a2      b2         c2
a3         b3         c3         -  a4      b4         c4
a5         b5         c5
a6         b6         c6         -  a7      b7         c7
a8         b8         c8
a9         b9         c9

6 rows selected.

--&gt虽没有preserve关键词，“-”仍然保留，而且空格也保留了

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

转载于:http://blog.itpub.net/18922393/viewspace-743754/