Oracle sqlldr 应用

我们可以用Oracle的sqlldr工具来导入数据：

Sqlldr userid=lgone/tiger control=a.ctl

着重关注以下几个参数：

userid -- Oracle 的 username/password[@servicename]
control -- 控制文件，可能包含表的数据
log -- 记录导入时的日志文件，默认为控制文件(去除扩展名).log
bad -- 坏数据文件，默认为控制文件(去除扩展名).bad
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次，默认为 64
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述

有两种使用方法：

（1）使用一个控制文件(作为模板) 和一个数据文件

一般为了利于模板和数据的分离，以及程序的不同分工会使用第一种方式,下面是一个比较完整的例子：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE "users_data.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
// INFILE 'tt.date' // 导入多个文件 
--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，
truncate --操作类型，用 truncate table 来清除表中原有记录
INTO TABLE users -- 要插入记录的表
Fields terminated by "," -- 数据中每行记录用 "," 分隔
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时
trailing nullcols --表的字段没有对应的值时允许为空
(
  virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号
  user_id number, --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示
  user_name,
  login_times,
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换
)

其中append为数据装载方式，还有其他选项：
  a、insert，为缺省方式，在数据装载开始时要求表为空
  b、append，在表中追加新记录
  c、replace，删除旧记录(用 delete from table 语句)，替换成新装载的记录
  d、truncate，删除旧记录(用 truncate table 语句)，替换成新装载的记录

（2）只使用一个控制文件，在这个控制文件中包含数据

把 users_data.cvs 中的内容补到 users.ctl 中，并以 BEGINDATA 连接，还要把 INFILE "users_data.csv" 改为 INFILE *。同时为了更大化的说明问题，把数据处理了一下。此时，完整的 users.ctl 文件内容是：

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE *  -- 因为数据同控制文件在一起，所以用 * 表示
append    -- 这里用了 append 来操作，在表 users 中附加记录 
INTO TABLE users
when LOGIN_TIMES<>'8'  -- 还可以用 when 子句选择导入符合条件的记录
Fields terminated by ","
trailing nullcols
(
  virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号
  user_id "user_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值
  user_name "'Hi '||upper(:user_name)",--,还能用SQL函数或运算对数据进行加工处理
  login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符
  last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这里开始
   ,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄莺,8,2009-1-2
4,4,Kypfos,NULL,NULL
5,5,不知秋,1,2008-12-23

逐一举例说明：

1、普通装载

注：BEGINDATA后的数值前面不能有空格

LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  
(DEPTNO,
 DNAME,
 LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA" 
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia --loc 列将为空
60,"Finance",,Virginia   --loc 列将为空

2、TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况

注：x'09'表示字符ASCII码的16进制数值，为tab分隔符

TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ：装载这种数据: 10,lg,"""lg""","lg,lg"

TERMINATED BY WRITESPACE：装载这种数据: 10 lg lg

LOAD DATA  
INFILE *  
INTO TABLE DEPT  
REPLACE  
FIELDS TERMINATED BY WHITESPACE  
--FIELDS TERMINATED BY x'20'  
(DEPTNO,  
 DNAME,  
 LOC  
)
BEGINDATA  
10 Sales Virginia

3、指定不装载那一列

LOAD DATA  
INFILE *  
INTO TABLE DEPT  
REPLACE  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  
(DEPTNO,  
 FILLER_1 FILLER,  --下面的 "Something Not To Be Loaded" 将不会被装载  
 DNAME,  
 LOC  
)  
BEGINDATA  
20,Something Not To Be Loaded,Accounting,"Virginia,USA"

4、position：当没声明FIELDS TERMINATED BY ',' 时，用位置告诉字段装载数据

结果：10 | Accounting Vir | ginia,USA | 10Accounting Virginia,USA

LOAD DATA  
INFILE *  
INTO TABLE DEPT  
REPLACE  
(DEPTNO position(1:2),  
 DNAME position(*:16), --这个字段的开始位置在前一字段的结束位置  
 LOC position(*:29),  
 ENTIRE_LINE position(1:29)  
)  
BEGINDATA 
10Accounting Virginia,USA

5、使用函数日期的一种表达TRAILING NULLCOLS的使用

注：可以通过:dname类型调用函数，特别注意date函数的使用。

LOAD DATA  
INFILE *  
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
--这句的意思是将没有对应值的列都置为null
--如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了  
(DEPTNO,  
 DNAME "upper(:dname)", --使用函数  
 LOC "upper(:loc)",  
 LAST_UPDATED date 'dd/mm/yyyy', --日期的一种表达方式。还有'dd-mon-yyyy'等
 ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"  
)  
BEGINDATA  
10,Sales,Virginia,1/5/2000  
20,Accounting,Virginia,21/6/1999  
30,Consulting,Virginia,5/1/2000  
40,Finance,Virginia,15/3/2001

6、合并多行记录为一行记录

注：例如有些文本文件以N行为一循环记录数据，则可以这样导入。

LOAD DATA  
INFILE *  
concatenate 3 --通过关键字concatenate 把几行的记录看成一行记录  
INTO TABLE DEPT  
replace  
FIELDS TERMINATED BY ','  
(DEPTNO,  
 DNAME "upper(:dname)",  
 LOC "upper(:loc)",  
 LAST_UPDATED date 'dd/mm/yyyy' 
)  
BEGINDATA  
10,Sales,
Virginia,
1/5/2000

7、使用continueif来合并记录行

上例可直接使用continueif last= ','来告诉Oracle如果前一个数据以','结尾，则这个附加到上一行

注：ContinueIf还可以使用this或next选项

LOAD DATA  
INFILE *  
continueif last= ','
INTO TABLE DEPT  
replace  
FIELDS TERMINATED BY ','  
(DEPTNO,  
 DNAME "upper(:dname)",  
 LOC "upper(:loc)",  
 LAST_UPDATED date 'dd/mm/yyyy' 
)  
BEGINDATA  
10,Sales,
Virginia,
1/5/2000

8、载入每行的行号，关键字：RECNUM

LOAD DATA  
INFILE *  
INTO TABLE DEPT 
replace  
(DEPTNO      RECNUM //载入每行的行号  
 ENTIRE_LINE Position(1:1024)
)  
BEGINDATA  
fsdfasj     --自动分配行号到DEPTNO字段，此行为1  
fasdjfasdfl --自动递增，此行为2

9、载入有换行符的数据

①使用一个非换行符的字符

注：换行的特殊字符如果使用'\n'，则会在Windows编译过程中直接换成换行符，导致无法转换

LOAD DATA  
INFILE *  
INTO TABLE DEPT  
REPLACE  
FIELDS TERMINATED BY ','  
TRAILING NULLCOLS  
(DEPTNO,  
DNAME "upper(:dname)",  
LOC "upper(:loc)",  
LAST_UPDATED "my_to_date( :last_updated )",  
COMMENTS "replace(:comments,'%%',chr(10))"  --用replace函数转换成换行符  
)  
BEGINDATA  
10,Sales,Virginia,01-april-2001,This is the Sales%%Office in Virginia  
20,Accounting,Virginia,13/04/2001,This is the Accounting%%Office in Virginia  
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting%%Office in Virginia  
40,Finance,Virginia,987268297,This is the Finance%%Office in Virginia

②使用fix属性： fix告诉每次取一行的范围，然后，通过看这一行相应的分隔符去分开对应字段中的数据

注：fix只能加在外部文件数据导入时，另外需要每行数据长度都相等。

Load DATA  
INFILE demo1.dat "fix 68"  
INTO TABLE t1_a  
REPLACE  
FIELDS TERMINATED BY ','  
TRAILING NULLCOLS  
(DEPTNO,  
DNAME "upper(:dname)",  
LOC "upper(:loc)",  
LAST_UPDATED Date 'dd/mm/yyyy',
ENTIRE_LINE  
)
-------------------------------------------------
demo1.dat  
----------
10,aaaab,Virginia,01/05/2001,This is the aaaab
Office in Virginia
20,aaaac,Virginia,13/04/2001,This is the aaaac
Office in Virginia
30,aaaad,Virginia,14/04/2001,This is the aaaad
Office in Virginia
40,aaaae,Virginia,16/02/2001,This is the aaaae
Office in Virginia

③ 使用var属性：其实就是每次开头告诉sqlldr每行变化取多少位，相当于每次都指定个fix多少（由前面的数据决定位数）的值。这也就是变长的做法。

Load DATA  
INFILE demo17.dat "var 3"  
INTO TABLE t1_a  
REPLACE  
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS  
(DEPTNO,  
 DNAME "upper(:dname)",  
 LOC "upper(:loc)",  
 LAST_UPDATED Date 'dd/mm/yyyy',
 ENTIRE_LINE  
)
----------------------------------------------
demo17.dat  
----------
03510,Sales,Virginia,01/01/2001,This
03920,Accounting,Virginia,13/04/2001,Thi
04530,Consulting,Virginia,14/04/2001,This is t
07140,Finance,Virginia,14/04/2001,This is the Finance Office
 in Virginia
注：var 3 表示前三位用于说明该条记录的长度 (但是谁告诉我长度怎么数的？-_-|||)

④使用str属性
可使用str来定义一个行结尾符
计算以|\r\n 结束的值：
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
结果：7C0D0A

注意：同样需要在外部文件数据导入中使用，且最后一个不用加；另外注意不要有空格

LOAD DATA  
INFILE demo17.dat "str X'7C0D0A'"  
INTO TABLE t1_a  
REPLACE  
FIELDS TERMINATED BY ','  
TRAILING NULLCOLS  
(DEPTNO,  
 DNAME "upper(:dname)",  
 LOC "upper(:loc)",  
 LAST_UPDATED Date 'dd/mm/yyyy',
 ENTIRE_LINE  
)  
-----------------------------------------------
demo17.dat  
----------
10,Sales,Virginia,01/01/2001,This is the Sales  
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting  
Office in Virginia|
30,Consulting,Virginia,14/04/2001,This is the Consulting  
Office in Virginia|
40,Finance,Virginia,14/04/2002,This is the Finance  
Office in Virginia

10、nullif导入

注：需要注意的是在前面指定的数据类型以及后面的引号！

LOAD DATA
INFILE *
INTO TABLE t1_a
REPLACE
(DEPTNO position(1:2) integer external nullif DEPTNO='1',
 -- 当导入deotno的值为'1'时，则该条记录不导入
 DNAME position(3:8)
)
BEGINDATA
1 10
20lg

11、使用自定义的函数。以为是解决的时间问题的例子：

create or replace 
my_to_date( p_string in varchar2 ) return date 
as 
type fmtArray is table of varchar2(25); 

l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 
'dd/mm/yyyy', 
'dd/mm/yyyy hh24:mi:ss' ); 
l_return date; 
begin 
for i in 1 .. l_fmts.count 
loop 
begin 
l_return := to_date( p_string, l_fmts(i) ); 
exception 
when others then null; 
end; 
EXIT when l_return is not null; 
end loop; 

if ( l_return is null ) 
then 
l_return := 
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * 
p_string, 'GMT', 'EST' ); 
end if; 

return l_return; 
end;

LOAD DATA 
INFILE * 
INTO TABLE DEPT 
REPLACE 
FIELDS TERMINATED BY ',' 
TRAILING NULLCOLS 
(DEPTNO, 
DNAME "upper(:dname)", 
LOC "upper(:loc)", 
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数 
) 
BEGINDATA 
10,Sales,Virginia,01-april-2001 
20,Accounting,Virginia,13/04/2001 
30,Consulting,Virginia,14/04/2001 12:02:02 
40,Finance,Virginia,987268297 
50,Finance,Virginia,02-apr-2001 
60,Finance,Virginia,Not a date

posted on 2015-03-30 17:49 小林子的烂笔头阅读(1302) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Oracle sqlldr 应用

导航