Oracle数据库SqlLoad常用技巧总结

Oracle数据库SqlLoad常用技巧的相关知识是本文我们主要要介绍的内容，本文我们总结了14种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 (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))" )