Oracle数据库SqlLoad常用技巧总结
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 1、控制文件中注释用"--". 2、为防止导入出现中文乱码，在控制文件中加入字符集控制 LOAD DATA CHARACTERSET ZHS16GBK 3、让某一列成为行号，用RECNUM关键字 load data infile * into table t repla
欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入
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
）
[1] [2]