Oracle数据库SqlLoad常用技巧总结

1、1、控制文件中注释用“2、为防止导入出现中文乱码，在控制文件中加入字符集控制1.2.LOAD DATACHARACTERSET ZHS16GBK3、让某一列成为行号，用 RECNU关键字.5.6.7.load datainfile *into table trepl ace(seqno RECNUM /载入每行的行号text P osition(1:1024)BEGINDATA8.fsdfasj4、过滤某一列，用 FILLER关键字1.2.LOAD DATATRUNCATE INTO TABLE T.7.FIELDS TERMINATED BY ',

2、9;(field1,field2 FILLER,field35、过滤行INTO TABLE table name 后加WHEI过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。1.2.3.LOAD DATAINFILE 'mydata.dat'BADFILE 'mydata.bad'4.5.DISCARDFILE 'mydata.dis'APPEND6.7.8.INTO TABLE my_selective_tableWHEN (01)<> 'H' and (01)<> 

3、9;T' and (30:37) = '20031217'9.10.regionservice_keyCONSTANT '31',P OSITION(01:11)INTEGER EXTERNAL,11.12.call b noP OSITION(12:29)CHAR6、过滤首行,OPTIONS (SKIP 1)选项，也可以写在命令行中，如:sqlldr sms/adm in con trol=test.ctl skip=17、TRAILING NULLCOLS勺使用，作用是表的字段没有对应的值时允许为空如:1.load data2.3.infile *i

4、nto table dept4.REPlace5.6.fields terminated by ','trailing nullcols /其实下面的entire line 在begindata后面的数据中是没有直接对应的列的值的如果第一行改为10,Sales,Virginia,1/5/2000,就不用 trailing nullcols7.(DEP TNO,8.9.10.DNAME " upp er(:dname)", /LOC "upp er(:loc)",LAST_U PDATED date 'dd/mm/yyyy'

5、, /使用函数日期的一种表达方式还有'dd-mon-yyyy'11.12.ENTIRE_ LIN E ":de ptno|:dname|:loc|:last_ up dated"13.14.15.begindata10,Sales,Virginia,1/5/200020,Accounting,Virginia,21/6/199916.17.30,Consulting,Virginia,5/1/200040,Finance,Virginia,15/3/2001添加、修改数据1.2.3.load datainfile *into table tmp_test4.

6、5.(rec_no"my_db_sequence.nextval".regionconstant '31',6.7.8.time loadeddata1data2"to_char(SYSDATE, 'HH24:MI')",P OSITION(1:5) ":data1/100",P OSITION(6:15) "upp er(:data2)",9.10.data3P OSITION(16:22)"to_date(:data3, 'YYMMDD')"1

7、1.12.13.begindata11111AAAAAAAAAA99120122222BBBBBBBBBB9901121.load data2.3.INFILE 'mail orders.txt'BADFILE 'bad orders.txt'4.APPEND5.6.7.INTO TABLE mailing_listFIELDS TERMINATED BY ","(addr,8.9.10.city,state,zip code,4.mailing_addr "decode(:mailing_addr, null,

8、 :addr, :mailing_addr)".mailing_city "decode(:mailing_city, null, :city, :mailing_city)".mailing_state9、合并多行记录为一行记录通过关键字concatenate 把几行的记录看成一行记录:1.2.3.LOAD DATAINFILE *concatenate 3 /通过关键字 concatenate把几行的记录看成一行记录4.INTO TABLE DEPT5.6.repl aceFIELDS TERMINATED BY ','7.(DEP TNO,8

9、.9.DNAME " upp er(:dname)",LOC "upp er(:loc)",3.14.LAST_U PDATED date 'dd/mm/yyyy'BEGINDATA10,Sales, /Virginia,其实这 3 行看成一行 10,Sales,Virginia,1/5/200015.1/5/200010、用”1+1 ”分隔符，避免数据混淆:fields termi nated by "|+|"11、如果数据文件包含在控制文件中，用INFILE *如下:1.LOAD DATA2.3

10、.INFILE *append4.5.INTO TABLE tmp_testFIELDS TERMINATED BY ","6.OP TIONALLY ENCLOSED BY '"'8.(datal.9.data210.11.BEGINDATA12.11111,AAAAAAAAAA13.22222,"A,B,C,D,"12、一次导入多个文件到同一个表1.LOAD DATA2.INFILE file1.dat3.INFILE file2.dat4.INFILE file3.dat5.APPEND6.INTO TABLE emp7.

11、(empno PO SITION(1:4) INTEGER EXTERNAL,8.ename P OSITION(6:15) CHAR,9.de ptno P OSITION(17:18) CHAR,10.mgr P OSITION(2O:23) INTEGER EXTERNAL11.13、将一个文件导入到不同的表1.LOAD DATA2.INFILE *3.INTO TABLE tab1 WHENtab'tab1'4.(tab FILLER CHAR(4),5.col1 INTEGER6.7.INTO TABLE tab2 WHENtab'tab2'8.(ta

12、b FILLER P OSITION(1:4),9.col1 INTEGER10.11.BEGINDATA12.tab1|113.tab1|214.tab2|215.tab3| 31.LOAD DATA3.REPLACE4.INTO TABLE emp5.WHEN empno !=''6.7.(empno PO SITION(1:4) INTEGER EXTERNAL,8.ename POSITION(6:15) CHAR, deptno POSITION(17:18) CHAR,9.10.11.mgr P OSITION(2O:23) INTEGER EXTERNAL)INT

13、O TABLE proj5.WHEN projno !=''(projno P OSITION(25:27) INTEGER EXTERNAL,empno P OSITION(1:4) INTEGER EXTERNAL14、过滤掉的数据文件路径指定1./op t/a pp/oracle /p roduct/10.2.0/bin/sqlldr AP S/A PScontrol=/home/oracle/A PS_LOAD/ctl/AP_CONTRACT.CTLLOG=/home/oracle/A PS_LOAD/log/$yesterday/AP_CONTRA

14、CT_$yesterday.logbad =/home/oracle/A PS_LOAD/bad/DUE_BIL$yesterday.badrows =10000 readsize =20000000 bindsize =20000000DISCARD =/home/oracle/A PS_LOAD/bad/discard ts.dis15、附：测试用控制文件1.2.LOAD DATAINFILE 7home/oracle/APS LOAD/dat/APS AP CONTRACT.dat'3.TRUNCATE4.5.INTO TABLE AP CONTRACTWHEN (01) <

15、;>'1'6.7.FIELDS TERMINATED BY "|"TRAILING NULLCOLS8.9.10.AGMT NOCONTRACT NO"(TRIM(:AGMT_NO)",FILLER, - "(TRIM(:CONTRACT_NO)",11.12.LOAN AMTAGMT HOLDER"(TRIM(:LOAN_AMT)",13.14.15.LOAN TYPE CDCURR CDBALANCE"(TRIM(:AGMT_HOLDER"(TRIM(:LOAN_T Y

16、P E_CD"(T RIM(:CURR_CD)","(TRIM(:BALANCE)",)",)",16.17.LOAN DIRC CDAGMT START DATE"(TRIM(:LOAN_DIRC_CD"(TRIM(:AGMT_START_DATE)",)",18.19.20.AGMT END DATEAGMT BELONG ORGMANAGER NO"(TRIM(:AGMT_END_DATE)",NO"(TRIM(:AGMT_BELONG_ORG_NO )&qu

17、ot;,"(TRIM(:MANAGER_NO)",21.22.PROCESS RATE"(TRIM(: PROCESS_RATE)",23.INSURE_METH_T YP E_CD "(TRIM(:INSURE_METH_T YP E_CD )","(TRIM(:AGMT_SIGN_DATE)",AGMT SIGN DATE24.LOAN PROP CD"(TRIM(:LOANPROP _CD)",25.LOAN USE TYPE"(TRIM(:LOAN_USE_T YPE )",26.ENTRUST LOAN FLAG"(TRIM(:ENTRUST_LOAN_FLAG )",27.ENTRUST NAME"(TRIM(:ENTRUST_NAME)",28.FARM LOAN FLAG"(TRIM(:FARM_LOAN_FLAG)",29.FARM_LOAN_T YP E_CD"(T RIM(:FARM_LOAN_T YP E_CD )",30.LOAN_BIZ_T YP E_CD&quo