SQLLDR 简介

1. SQLLDR导入

1.1 简介

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

2.2 语法和参数

语法： SQLLDR keyword=value [,keyword=value,...];

Sqlldr 参数一览

Keyword	默认值	描述
userid		ORACLE 用户名/口令
control		控制文件名
log		日志文件名
bad		错误文件名
data		数据文件名
discard		废弃文件名
discardmax	全部	允许废弃的文件的数目
skip	0	要跳过的逻辑记录的数目
load	全部	要加载的逻辑记录的数目
errors		允许的错误的数目
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	使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows	5000	直接路径列数组的行数
streamsize	256000	直接路径流缓冲区的大小 (以字节计)
multithreading		在直接路径中使用多线程
resumable	FALSE	启用或禁用当前的可恢复会话
resumable_name		有助于标识可恢复语句的文本字符串
resumable_timeout	7200	RESUMABLE 的等待时间 (以秒计)
date_cache	1000	日期转换高速缓存的大小 (以条目计)

3 范例

利用PLSQL生成测试数据cux_sqlldr_test.txt

BEGIN

FOR iIN1..100

LOOP

IFMOD(i,2)=1THEN

dbms_output.put_line('"'||i||'","column1_'||i||'",'||'"column2_'||i||'",'||'"column3_'||i||'",'||'"show_column_'||i||'",'||'"hide_column_'||i||'","2017-01-01"');

ELSE

dbms_output.put_line('"'||i||'","column1_'||i||'", ,'||'"column3_'||i||'",'||'"show_column_'||i||'",'||'"hide_column_'||i||'"');

ENDIF;

ENDLOOP;

END;

建表

CREATETABLE cux.cux_sqlldr_test

(line_num NUMBER,

seq_num NUMBER,

column1 VARCHAR2(30),

column2 VARCHAR2(30)NOTNULL,

column3 VARCHAR2(30)DEFAULT'column2',

show_column VARCHAR2(30),

hide_column VARCHAR2(30),

creation_date DATE

);

CREATEORREPLACE SYNONYM apps.cux_sqlldr_test FOR cux.cux_sqlldr_test;

CREATESEQUENCE cux.cux_sqlldr_test_sSTARTWITH10001;

CREATEORREPLACESYNONYM apps.cux_sqlldr_test_s FOR cux.cux_sqlldr_test_s;

Sqlldr 有两种使用方式

（1）在控制文件中包涵数据.

创建一个文件命名为cux_sqlldr_test.ctl，在服务器下创建目录Sqlldr,在sqlldr下创建log和bad文件夹，

上传cux_sqlldr_test.ctl至服务器，如下图所示：

cux_sqlldr_test.ctl内容如下。

OPTIONS (skip=3,rows=128)

load data

CHARACTERSET ZHS16GBK

infile *

badfile "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.bad"

discardfile "/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/bad/cux_sqlldr_test.disc"

TRUNCATE into table cux_sqlldr_test

WHEN column1 != "column1_1"

Fields terminated by ","

Optionally enclosed by '"'

TRAILING NULLCOLS

(

line_num RECNUM ,

seq_num "cux_sqlldr_test_s.nextval" ,

column1 ,

column2 ,

column3 NULLIF (column3="column3_4"),

show_column "UPPER(:show_column)" ,

hide_column FILLER ,

creation_date DATE 'YYYY-MM-DD' "CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD') ELSE :creation_date END"

)

BEGINDATA

"1","column1_1","column2_1","column3_1","show_column_1","hide_column_1","2017-01-01"

"2","column1_2", ,"column3_2","show_column_2","hide_column_2"

"3","column1_3","column2_3","column3_3","show_column_3","hide_column_3","2017-01-01"

"4","column1_4", ,"column3_4","show_column_4","hide_column_4"

"5","column1_5","column2_5","column3_5","show_column_5","hide_column_5","2017-01-01"

"6","column1_6", ,"column3_6","show_column_6","hide_column_6"

"7","column1_7","column2_7","column3_7","show_column_7","hide_column_7","2017-01-01"

"8","column1_8", ,"column3_8","show_column_8","hide_column_8"

"9","column1_9","column2_9","column3_9","show_column_9","hide_column_9","2017-01-01"

"10","column1_10", ,"column3_10","show_column_10","hide_column_10"

运行命令

sqlldr userid=apps/appsjiahuicontrol=/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.ctl log=$CUX_TOP/sqlldr/file/cux_sqlldr_test.log

查看结果

查看表

由上图可以看出，运行命令后，在file文件夹下生成了cux_sqlldr_test.log，在bad下生成了cux_sqlldr_test.bad，在表中生成了3条记录，下面分析一下cux_sqlldr_test.ctl的内容和结果

代码	说明
OPTIONS (skip=3,rows=128)	sqlldr 的内容可以写在cotrol文件 load_data的前面，此处跳过前3行,每次提交128行
load data	加载数据
CHARACTERSET ZHS16GBK	字符集编码（如果出现乱码要考虑一下）
infile *	加载的文件，* 表示本文件
badfile	错误的数据所放的文件（校验错误）
discardfile	丢弃的数据放的路径（记录的格式错误或过滤行）
TRUNCATE into table cux_sqlldr_test	先TRUNCATE cux_sqlldr_test再将记录插入表
WHEN column1 != "column1_1"	过滤行，对于值为column1_1的行过滤
Fields terminated by ","	多个字段间用“,”隔开
Optionally enclosed by '"'	单个字段用“"”，“"”开始结束
TRAILING NULLCOLS	对于值为空的字段允许为空
(line_num RECNUM ,	序号，自动生成，并不取自数据
seq_num "cux_sqlldr_test_s.nextval" ,	取每条记录的第一个字段，此处应为1..10，但是这里赋值序列。
代码	说明
column1 ,	column1
column2 ,	column2，表定义为非空字段，虽然上面允许为空，但是如果该值为空，不能插入表种
column3 NULLIF (column3="column3_4"),	column3="column3_4"时候默认为空
show_column "UPPER(:show_column)" ,	大写列（调用UPPER大写函数）
hide_column FILLER ,	FILLER 隐藏列
creation_date DATE 'YYYY-MM-DD' "CASE WHEN :creation_date is null THEN TO_CHAR(sysdate,'YYYY-MM-DD') ELSE :creation_date END"	日期类型，格式为YYYY-MM-DD，为空的时候取系统日期
)
BEGINDATA	数据开始
*******	数据内容，默认每行一条记录

插入表的4种方式

insert，为缺省方式，在数据装载开始时要求表为空
append，在表中追加新记录
replace，(delete table) 删除旧记录，替换成新装载的记录
truncate，(truncate table)删除旧记录，替换成新装载的记录

10条数据由条件skip=3去除三条，因此上面命令运行结果是logic record count 7,查看 file文件夹下的log日记（log是不断叠加的，badfile是重新覆盖的）

从日志可以看出7条数据中，4条记录无法没导入的原因。

查看cux_sqlldr_test.bad，其中记录4条错误的数据。

（2）在控制文件中不包涵数据.

上传cux_sqlldr_test.txt,cux_sqlldr_test.ctl至服务器，cux_sqlldr_test.txt由上面PLSQL脚本生成，cux_sqlldr_test.ctl如下