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