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

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

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

LOADDATA 
CHARACTERSETZHS16GBK

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

loaddata 
infile* 
intotablet 
replace 
(seqnoRECNUM//载入每行的行号 
textPosition(1:1024)) 
BEGINDATA 
fsdfasj

4、过滤某一列，用FILLER关键字

LOADDATA 
TRUNCATEINTOTABLET1 
FIELDSTERMINATEDBY',' 
(field1, 
field2FILLER, 
field3 
)

5、过滤行

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

LOADDATA 
INFILE'mydata.dat' 
BADFILE'mydata.bad' 
DISCARDFILE'mydata.dis' 
APPEND 
INTOTABLEmy_selective_table 
WHEN(01)<>'H'and(01)<>'T'and(30:37)='20031217' 
( 
regionCONSTANT'31', 
service_keyPOSITION(01:11)INTEGEREXTERNAL, 
call_b_noPOSITION(12:29)CHAR 
)

6、过滤首行，用OPTIONS (SKIP 1)选项，也可以写在命令行中，如：

sqlldrsms/admincontrol=test.ctlskip=1

7、TRAILING NULLCOLS的使用，作用是表的字段没有对应的值时允许为空

如：

LOADDATA 
INFILE* 
INTOTABLEDEPT 
REPLACE 
FIELDSTERMINATEDBY',' 
TRAILINGNULLCOLS//其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为10,Sales,Virginia,1/5/2000,,就不用TRAILINGNULLCOLS了 
(DEPTNO, 
DNAME"upper(:dname)",//使用函数 
LOC"upper(:loc)", 
LAST_UPDATEDdate'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）、

LOADDATA 
INFILE* 
INTOTABLEtmp_test 
(rec_no"my_db_sequence.nextval", 
regionCONSTANT'31', 
time_loaded"to_char(SYSDATE,'HH24:MI')", 
data1POSITION(1:5)":data1/100", 
data2POSITION(6:15)"upper(:data2)", 
data3POSITION(16:22)"to_date(:data3,'YYMMDD')" 
) 
BEGINDATA 
11111AAAAAAAAAA991201 
22222BBBBBBBBBB990112

（2）、

LOADDATA 
INFILE'mail_orders.txt' 
BADFILE'bad_orders.txt' 
APPEND 
INTOTABLEmailing_list 
FIELDSTERMINATEDBY"," 
(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 把几行的记录看成一行记录：

LOADDATA 
INFILE* 
concatenate3//通过关键字concatenate把几行的记录看成一行记录 
INTOTABLEDEPT 
replace 
FIELDSTERMINATEDBY',' 
(DEPTNO, 
DNAME"upper(:dname)", 
LOC"upper(:loc)", 
LAST_UPDATEDdate'dd/mm/yyyy' 
) 
BEGINDATA 
10,Sales,//其实这3行看成一行10,Sales,Virginia,1/5/2000 
Virginia, 
1/5/2000

10、用”|+|”分隔符，避免数据混淆：fields terminated by "|+|"

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

如下：

LOADDATA 
INFILE* 
append 
INTOTABLEtmp_test 
FIELDSTERMINATEDBY"," 
OPTIONALLYENCLOSEDBY'"' 
TRAILINGNULLCOLS 
(data1, 
data2 
) 
BEGINDATA 
11111,AAAAAAAAAA 
22222,"A,B,C,D,"

12、一次导入多个文件到同一个表

LOADDATA 
INFILEfile1.dat 
INFILEfile2.dat 
INFILEfile3.dat 
APPEND 
INTOTABLEemp 
(empnoPOSITION(1:4)INTEGEREXTERNAL, 
enamePOSITION(6:15)CHAR, 
deptnoPOSITION(17:18)CHAR, 
mgrPOSITION(20:23)INTEGEREXTERNAL 
)

13、将一个文件导入到不同的表

（1）、

LOADDATA 
INFILE* 
INTOTABLEtab1WHENtab='tab1' 
(tabFILLERCHAR(4), 
col1INTEGER 
) 
INTOTABLEtab2WHENtab='tab2' 
(tabFILLERPOSITION(1:4), 
col1INTEGER 
) 
BEGINDATA 
tab1|1 
tab1|2 
tab2|2 
tab3|3 
==============

（2）、

LOADDATA 
INFILE'mydata.dat' 
REPLACE 
INTOTABLEemp 
WHENempno!='' 
(empnoPOSITION(1:4)INTEGEREXTERNAL, 
enamePOSITION(6:15)CHAR, 
deptnoPOSITION(17:18)CHAR, 
mgrPOSITION(20:23)INTEGEREXTERNAL 
) 
INTOTABLEproj 
WHENprojno!='' 
(projnoPOSITION(25:27)INTEGEREXTERNAL, 
empnoPOSITION(1:4)INTEGEREXTERNAL 
)

14、过滤掉的数据文件路径指定

/opt/app/oracle/product/10.2.0/bin/sqlldrAPS/APScontrol=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTLLOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.logbad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.badrows=10000readsize=20000000bindsize=20000000DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis

15、附：测试用控制文件

LOADDATA 
INFILE'/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat' 
TRUNCATE 
INTOTABLEAP_CONTRACT 
WHEN(01)<>'1' 
FIELDSTERMINATEDBY"|" 
TRAILINGNULLCOLS 
( 
AGMT_NO"(TRIM(:AGMT_NO))", 
CONTRACT_NOFILLER,--"(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_TESTRECNUM, 
CHAR_TESTCONSTANT'31', 
SQ"sqlldr.nextval", 
TEST_4"TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS')", 
TEST_5"(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))" 
)