Oracle数据库SqlLoad常用技巧总结

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))"    
)