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]