大家好,又见面了,我是你们的朋友全栈君。
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 |
使用外部表进行加载; |
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 |
加载数据 |
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” , |
取每条记录的第一个字段,此处应 |
代码 |
说明 |
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如下
OPTIONS (skip=3,rows=128) load data CHARACTERSET ZHS16GBK infile “/u02/ebsuat/app/fs2/EBSapps/appl/cux/12.0.0/sqlldr/file/cux_sqlldr_test.txt” 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_4” Fields terminated by “,” Optionally enclosed by ‘”‘ TRAILING NULLCOLS ( line_num RECNUM , seq_num “cux_sqlldr_test_s.nextval” , column1 , column2 “nvl(: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” ) |
运行命令
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
100条数据由于skip = 3 从第4条开始处理变成97条,第四条数据由于WHEN column1 != “column1_4”
被丢弃在bad的cux_sqlldr_test.disc路径下,没有错误数据。查看表共96条数据,如下图所示:
column2 “nvl(:column2,’***’)”, 对于 column2默认为 “***” .
其他
此外,sqlload可以实现同时加载多个文件,同时把数据加载到多个表。
参考网址:
http://www.cnblogs.com/jyzhao/p/4819884.html
http://m635674608.iteye.com/blog/1895316
http://blog.csdn.net/zq9017197/article/details/7352627
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/125016.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...