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]