PlSql Loader 详细学习

1、SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据
仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。

2、在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。
如Windows下执行：
C:\Documents and Settings\Administrator>sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 4月 18 11:45:19 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

3、在Windows下执行：C:\Documents and Settings\Administrator>sqlldr
可以获取如下帮助信息如下，补充了自己的一部分认识：
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
userid -- ORACLE 用户名/口令：Oracle 的 username/password[@servicename]
control -- 控制文件名
log -- 日志文件名：记录导入时的日志文件，默认为 控制文件(去除扩展名).log
bad -- 错误文件名：坏数据文件，默认为 控制文件(去除扩展名).bad
data -- 数据文件名：数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目 (全部默认)
skip -- 要跳过的逻辑记录的数目 (默认 0)
load -- 要加载的逻辑记录的数目 (全部默认)
errors -- 允许的错误的数目 (默认 50)
rows -- 常规路径绑定数组中或直接路径保存数据间的行数：多少条记录提交一次
(默认: 常规路径 64, 所有直接路径)
bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000)
silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
direct -- 使用直接路径 (默认 FALSE)
parfile -- 参数文件: 包含参数说明的文件的名称
parallel -- 执行并行加载 (默认 FALSE)
file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)

commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE)
readsize -- 读取缓冲区的大小 (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数 (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)
multithreading -- 在直接路径中使用多线程
resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE)

PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlldr scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo userid=scott/tiger'。
位置指定参数的时间必须早于但不可迟于由关键字指定的参数。
例如,
允许 'sqlldr scott/tiger control=foo logfile=log',
但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。

4、sql loader 使用例子
前期描述：
数据将导入的表的表结构如下
create table user_test
(
id VARCHAR2(32) not null,
username VARCHAR2(64),
password VARCHAR2(128),
birthday VARCHAR2(32)
)

数据文件Excel：test.xls

a)、SQL*LOADER 将Excel 数据文件导入到 Oracle数据库指定表中。
(1.创建SQL*Loader 输入数据所需要的文件,均保存到C:\，用记事本编辑：
数据文件：test.txt 是由数据文件test.xls 转换生成的，使用的是制表符为分隔符。

控制文件：input.ctl，内容如下：
　　load data　　　　　　　　　　 --1、控制文件标识
　　infile 'C:\test.txt'　　　　　--2、数据源文件名为test.txt
　　append into table user_test　 --3、向表test中追加记录
　　fields terminated by X'09'　　--4、字段终止于X'09'，是一个制表符（TAB）
　　(id,username,password,birthday)　　 -----定义列对应顺序

其中：
a、insert，为缺省方式，在数据装载开始时要求表为空
b、append，在表中追加新记录
c、replace，删除旧记录，替换成新装载的记录
d、truncate，同上

在DOS窗口下使用SQL*Loader命令实现数据的输入：
C:\>sqlldr userid=用户名/密码@服务名 control=input.ctl
默认日志文件名为：input.log
默认坏记录文件为：input.bad

(2.还有一种方法
可以把Excel文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔
LOAD DATA
INFILE 'C:\test.csv'
APPEND INTO TABLE user_test
FIELDS TERMINATED BY ","
(id,username,password,birthday)

b)、在控制文件中直接导入数据
(1、控制文件test.ctl的内容：
LOAD DATA
INFILE 'C:\test.csv'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
id,
username,
password NULLIF (CONTINENTID="NULL"),
birthday DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (birthday="NULL")
)
BEGINDATA
"1";"JP";"Japan";"09/16/2004 16:31:32";
"2";"CN";NULL;"09/16/2004 16:31:32";
"3";"IN";"India";NULL;

(2、执行导入命令: C:\>sqlldr userid=用户/密码@服务名 control=test.ctl

如果想去掉字段中含双引号方式，可以改写控制文件，使用如下：
控制文件test.ctl的内容：
LOAD DATA
INFILE 'C:\test.csv'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
id "substr(:id,2,length(:id)-2)",
username "substr(:username,2,length(:username)-2)",
password "substr(:password,2,length(:password)-2)" NULLIF (CONTINENTID="NULL"),
birthday "substr(:birthday,2,length(:birthday)-2)" DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (birthday="NULL")
)
BEGINDATA
"1";"JP";"Japan";"09/16/2004 16:31:32";
"2";"CN";"NULL";"09/16/2004 16:31:32";
"3";"IN";"India";"NULL";

5、常用技巧
（1、***** 常用的 sqlldr 命令显示的选项
OPTIONS (skip=2,rows=128) -- skip=2 用来跳过数据中的前两行，
load data
infile 'C:\test.csv'
append
into table user_test
Fields terminated by ','
(id,username,password,birthday)

或可以直接在执行命令的时候显示地加入参数：sqlldr 用户名/密码@服务名 input.ctl skip=2

（2、***** 普通装载
LOAD DATA
INFILE *
INTO TABLE user_test
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 列将为空

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

（3、***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
LOAD DATA
INFILE *
INTO TABLE user_test
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- FIELDS TERMINATED BY x'09'
(id,
username,
password,
birthday
)
BEGINDATA
20 lili 123456a* 19900807

（ 4、***** 指定不装载那一列
LOAD DATA
INFILE *
INTO TABLE user_test
REPLACE
FIELDS TERMINATED BY ','
(id,
username,
password FILLER, --下面的 "123456a*" 将不会被装载
birthday
)
BEGINDATA
20,lili,123456a*,19900807

（5、***** 使用函数日期的一种表达 TRAILING NULLCOLS 的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的。如果第一行改为 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、***** 使用自定义的函数,解决的时间问题
create or replace
function my_to_date( p_string in varchar2 ) return date
as
/

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

（7、***** 合并多行记录为一行记录
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, // 其实以下3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000

（8、***** 载入有换行符的数据
注意: unix 和 windows 不同 & /n
< 1 > 使用一个非换行符的字符
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,'n',chr(10))" // replace 的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia

< 2 > 使用fix属性
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo17.dat
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

// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同

LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo18.dat
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"

< 3 > 使用var属性
LOAD DATA
INFILE demo19.dat "var 3"
// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This is the Sales
Office in Virginia
07820,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
Office in Virginia
07140,Finance,Virginia,987268297,This is the Finance
Office in Virginia

< 4 > 使用str属性
最灵活的一种可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
此列中记录是以 a|rn 结束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
结果 7C0D0A

LOAD DATA
INFILE demo20.dat "str X'7C0D0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS
)

demo20.dat
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|

==============================================================================