Oracle数据库SqlLoad常用技巧总结

本文我们主要介绍了Oracle数据库SqlLoad常用技巧的相关知识，共包括14种常用的使用技巧以及测试用文件的源代码，希望能够对您有所收获！

AD：【线下活动】三大新锐HTML 5企业汇聚51CTO—大话移动前端技术

Oracle数据库SqlLoad常用技巧的相关知识是本文我们主要要介绍的内容，本文我们总结了14种SqlLoad的使用技巧，并给出了测试用的文件源码，接下来我们就开始一一介绍这部分内容，希望能够对您有所帮助。

1、控制文件中注释用“–”。

2、为防止导入出现中文乱码，在控制文件中加入字符集控制

LOAD DATA CHARACTERSET ZHS16GBK
3、让某一列成为行号，用RECNUM关键字

load data infile * into table t replace ( seqno RECNUM //载入每行的行号 text Position(1:1024)) BEGINDATA fsdfasj
4、过滤某一列，用FILLER关键字

LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ‘,’ ( field1, field2 FILLER, field3 )
5、过滤行

在INTO TABLE table_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。

LOAD DATA INFILE ‘mydata.dat’ BADFILE ‘mydata.bad’ DISCARDFILE ‘mydata.dis’ APPEND INTO TABLE my_selective_table WHEN (01) <> ‘H’ and (01) <> ‘T’ and (30:37) = ‘20031217’ ( region CONSTANT ‘31’, service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR )
6、过滤首行，用OPTIONS (SKIP 1)选项，也可以写在命令行中，如：

sqlldr sms/admin control=test.ctl skip=1

7、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
8、添加、修改数据

（1）、

LOAD DATA INFILE * INTO TABLE tmp_test ( rec_no “my_db_sequence.nextval”, region CONSTANT ‘31’, time_loaded “to_char(SYSDATE, ‘HH24:MI’)”, data1 POSITION(1:5) “:data1/100”, data2 POSITION(6:15) “upper(:data2)”, data3 POSITION(16:22)”to_date(:data3, ‘YYMMDD’)” ) BEGINDATA 11111AAAAAAAAAA991201 22222BBBBBBBBBB990112
（2）、

LOAD DATA INFILE ‘mail_orders.txt’ BADFILE ‘bad_orders.txt’ APPEND INTO TABLE mailing_list FIELDS TERMINATED BY “,” ( addr, city, state, zipcode, mailing_addr “decode(:mailing_addr, null, :addr, :mailing_addr)”, mailing_city “decode(:mailing_city, null, :city, :mailing_city)”, mailing_state )
9、合并多行记录为一行记录

通过关键字concatenate 把几行的记录看成一行记录：

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
10、用”|+|”分隔符，避免数据混淆：fields terminated by “|+|”

11、如果数据文件包含在控制文件中，用INFILE *

如下：

LOAD DATA INFILE * append INTO TABLE tmp_test FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”’ TRAILING NULLCOLS ( data1, data2 ) BEGINDATA 11111,AAAAAAAAAA 22222,”A,B,C,D,”
12、一次导入多个文件到同一个表

LOAD DATA INFILE file1.dat INFILE file2.dat INFILE file3.dat APPEND INTO TABLE emp ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL )
13、将一个文件导入到不同的表

（1）、

LOAD DATA INFILE * INTO TABLE tab1 WHEN tab = ‘tab1’ ( tab FILLER CHAR(4), col1 INTEGER ) INTO TABLE tab2 WHEN tab = ‘tab2’ ( tab FILLER POSITION(1:4), col1 INTEGER ) BEGINDATA tab1|1 tab1|2 tab2|2 tab3|3 ==============
（2）、

LOAD DATA INFILE ‘mydata.dat’ REPLACE INTO TABLE emp WHEN empno != ’ ’ ( empno POSITION(1:4) INTEGER EXTERNAL, ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR, mgr POSITION(20:23) INTEGER EXTERNAL ) INTO TABLE proj WHEN projno != ’ ’ ( projno POSITION(25:27) INTEGER EXTERNAL, empno POSITION(1:4) INTEGER EXTERNAL )
14、过滤掉的数据文件路径指定

/opt/app/oracle/product/10.2.0/bin/sqlldr APS/APS control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL LOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.log bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad rows=10000 readsize=20000000 bindsize=20000000 DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis
15、附：测试用控制文件

LOAD DATA INFILE ‘/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat’ TRUNCATE INTO TABLE AP_CONTRACT WHEN (01)<>’1’ FIELDS TERMINATED BY “|” TRAILING NULLCOLS ( AGMT_NO “(TRIM(:AGMT_NO ))”, CONTRACT_NO FILLER, – “(TRIM(:CONTRACT_NO ))”, LOAN_AMT “(TRIM(:LOAN_AMT ))”, AGMT_HOLDER “(TRIM(:AGMT_HOLDER ))”, LOAN_TYPE_CD “(TRIM(:LOAN_TYPE_CD ))”, CURR_CD “(TRIM(:CURR_CD ))”, BALANCE “(TRIM(:BALANCE ))”, LOAN_DIRC_CD “(TRIM(:LOAN_DIRC_CD ))”, AGMT_START_DATE “(TRIM(:AGMT_START_DATE ))”, AGMT_END_DATE “(TRIM(:AGMT_END_DATE ))”, AGMT_BELONG_ORG_NO “(TRIM(:AGMT_BELONG_ORG_NO ))”, MANAGER_NO “(TRIM(:MANAGER_NO ))”, PROCESS_RATE “(TRIM(:PROCESS_RATE ))”, INSURE_METH_TYPE_CD “(TRIM(:INSURE_METH_TYPE_CD ))”, AGMT_SIGN_DATE “(TRIM(:AGMT_SIGN_DATE ))”, LOAN_PROP_CD “(TRIM(:LOAN_PROP_CD ))”, LOAN_USE_TYPE “(TRIM(:LOAN_USE_TYPE ))”, ENTRUST_LOAN_FLAG “(TRIM(:ENTRUST_LOAN_FLAG ))”, ENTRUST_NAME “(TRIM(:ENTRUST_NAME ))”, FARM_LOAN_FLAG “(TRIM(:FARM_LOAN_FLAG ))”, FARM_LOAN_TYPE_CD “(TRIM(:FARM_LOAN_TYPE_CD ))”, LOAN_BIZ_TYPE_CD “(TRIM(:LOAN_BIZ_TYPE_CD ))”, ID_TEST RECNUM , CHAR_TEST CONSTANT ‘31’, SQ “sqlldr.nextval”, TEST_4 “TO_CHAR(SYSDATE,’YYYYMMDD HH24:MI:SS’)”, TEST_5 “(TRIM(:LOAN_BIZ_TYPE_CD)||’—’||TRIM(:AGMT_NO))” )
关于Oracle数据库SqlLoad常用技巧的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获！