SqlLoad常用技巧总结

 

原文:http://blog.csdn.net/nsj820/article/details/6740664
1、控制文件中注释用“--”

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

LOAD DATA

CHARACTERSET ZHS16GBK

3、让某一列成为行号，用RECNUM关键字
view plainprint?
load data
infile *
into table t
replace
( seqno RECNUM //载入每行的行号
text Position(1:1024))
BEGINDATA
fsdfasj

4、过滤某一列，用FILLER关键字
view plainprint?
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

5、过滤行

在INTO TABLEtable_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。
view plainprint?
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)选项

也可以写在命令行中，如：
view plainprint?
sqlldr sms/admin control=test.ctl skip=1

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

如：
view plainprint?
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、添加、修改数据
view plainprint?
（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 把几行的记录看成一行记录
view plainprint?
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 *

如下：
view plainprint?
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、一次导入多个文件到同一个表
view plainprint?
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、将一个文件导入到不同的表
view plainprint?
（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、过滤掉的数据文件路径指定
view plainprint?
/opt/app/oracle/product/10.2.0/bin/sqlldr APS/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.bad rows=10000readsize=20000000 bindsize=20000000DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis

15、附：测试用控制文件
view plainprint?
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))"
)