Oracle的SQLLDR用法简介

目录SQLLDR导入1.简介2.语法和参数 3. 范例 4.Sqlldr 有两种使用方式其他SQLLDR导入1.简介

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

2.语法和参数

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

Sqlldr 参数一览

Keyword默认值描述useridORACLE 用户名/口令 control控制文件名log日志文件名bad错误文件名data数据文件名discard废弃文件名discardmax全部允许废弃的文件的数目skip0要跳过的逻辑记录的数目load全部要加载的逻辑记录的数目errors允许的错误的数目rows常规：64 默认路径：全部常规路径绑定数组中或直接路径保存数据间的行数bindsize256000常规路径绑定数组的大小silent运行过程中隐藏消息directFALSE使用直接路径parfile参数文件: 包含参数说明的文件的名称parallelFALSE执行并行加载file执行文件skip_unusable_indexesFALSE不允许/允许使用无用的索引或索引分区skip_index_maintenanceFALSE没有维护索引, 将受到影响的索引标记为无用commit_discontinuedFALSE提交加载中断时已加载的行readsize1048576读取缓冲区的大小external_tableNOT_USED使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTEcolumnarrayrows5000直接路径列数组的行数streamsize256000直接路径流缓冲区的大小 (以字节计)multithreading在直接路径中使用多线程resumableFALSE启用或禁用当前的可恢复会话resumable_name有助于标识可恢复语句的文本字符串resumable_timeout7200RESUMABLE 的等待时间 (以秒计)date_cache1000日期转换高速缓存的大小 (以条目计)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;4.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，但是这里赋值序列。

表2

代码说明column1column1column2column2，表定义为非空字段，虽然上面允许为空，但是如果该值为空，不能插入表种column3 NULLIF (column3="column3_4")column3="column3_4"时候默认为空show_column "UPPER(:show_column)" 大写列（调用UPPER大写函数）hide_column FILLERFILLER 隐藏列creation_date DATE 'YYYY-MM-DD'"CASE WHEN :creation_date is null THENTO_CHAR(sysdate,'YYYY-MM-DD') ELSE :creation_date END"日期类型，格式为YYYY-MM-DD，为空的时候取系统日期BEGINDATA数据开始*******数据内容，默认每行一条记录

从日志可以看出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可以实现同时加载多个文件，同时把数据加载到多个表。

到此这篇关于Oracle的SQLLDR用法简介的文章就介绍到这了,更多相关Oracle SQLLDR内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！