SQLLOAD使用

在命令行下执行 Oracle 的 sqlldr 命令

比如：
1、[oracle@ ~]$ sqlldr abc/abc@ABC control=user.ctl direct=true;

2、user.ctl文件
OPTIONS (skip=1,rows=128)
unrecoverable
Load DATA
INFILE "/home/share/sqlload/user_data.csv"
INFILE "/home/share/sqlload/user_data1.csv"
REPLACE INTO TABLE users
Fields terminated by ","
Optionally enclosed by '"'
trailing nullcols
(
virtual_column FILLER,
user_id,
user_name,
login_times,
last_login DATE "YYYY-MM-DD HH24:MI:SS"
)

3、INTO TABLE前面可以写如下关键字
1) insert     --为缺省方式，在数据装载开始时要求表为空 
2) append     --在表中追加新记录 
3) replace    --删除旧记录(用 delete from table 语句)，替换成新装载的记录 
4) truncate   --删除旧记录(用 truncate table 语句)，替换成新装载的记录

Oracle数据库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  call_b_no <> '1' 
(   
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))"   
)