Oracle数据库SqlLoad常用技巧总结.doc

1、控制文件中注释用“-”。2、为防止导入出现中文乱码，在控制文件中加入字符集控制1. LOADDATA 2. CHARACTERSETZHS16GBK3、让某一列成为行号，用RECNUM关键字1. loaddata 2. infile* 3. intotablet 4. replace 5. (seqnoRECNUM/载入每行的行号 6. textPosition(1:1024) 7. BEGINDATA 8. fsdfasj4、过滤某一列，用FILLER关键字1. LOADDATA 2. TRUNCATEINTOTABLET1 3. FIELDSTERMINATEDBY, 4. (field1, 5. field2FILLER, 6. field3 7. )5、过滤行在INTO TABLE table_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。1. LOADDATA 2. INFILEmydata.dat 3. BADFILEmydata.bad 4. DISCARDFILEmydata.dis 5. APPEND 6. INTOTABLEmy_selective_table 7. WHEN(01)Hand(01)Tand(30:37)=20031217 8. ( 9. regionCONSTANT31, 10. service_keyPOSITION(01:11)INTEGEREXTERNAL, 11. call_b_noPOSITION(12:29)CHAR 12. )6、过滤首行，用OPTIONS (SKIP 1)选项，也可以写在命令行中，如：sqlldrsms/admincontrol=test.ctlskip=17、TRAILING NULLCOLS的使用，作用是表的字段没有对应的值时允许为空如：1. LOADDATA 2. INFILE* 3. INTOTABLEDEPT 4. REPLACE 5. FIELDSTERMINATEDBY, 6. TRAILINGNULLCOLS/其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为10,Sales,Virginia,1/5/2000,就不用TRAILINGNULLCOLS了 7. (DEPTNO, 8. DNAMEupper(:dname),/使用函数 9. LOCupper(:loc), 10. LAST_UPDATEDdatedd/mm/yyyy,/日期的一种表达方式还有dd-mon-yyyy等 11. ENTIRE_LINE:deptno|:dname|:loc|:last_updated 12. ) 13. BEGINDATA 14. 10,Sales,Virginia,1/5/2000 15. 20,Accounting,Virginia,21/6/1999 16. 30,Consulting,Virginia,5/1/2000 17. 40,Finance,Virginia,15/3/20018、添加、修改数据（1）、1. LOADDATA 2. INFILE* 3. INTOTABLEtmp_test 4. (rec_nomy_db_sequence.nextval, 5. regionCONSTANT31, 6. time_loadedto_char(SYSDATE,HH24:MI), 7. data1POSITION(1:5):data1/100, 8. data2POSITION(6:15)upper(:data2), 9. data3POSITION(16:22)to_date(:data3,YYMMDD) 10. ) 11. BEGINDATA 12. 11111AAAAAAAAAA991201 13. 22222BBBBBBBBBB990112（2）、1. LOADDATA 2. INFILEmail_orders.txt 3. BADFILEbad_orders.txt 4. APPEND 5. INTOTABLEmailing_list 6. FIELDSTERMINATEDBY, 7. (addr, 8. city, 9. state, 10. zipcode, 11. mailing_addrdecode(:mailing_addr,null,:addr,:mailing_addr), 12. mailing_citydecode(:mailing_city,null,:city,:mailing_city), 13. mailing_state 14. )9、合并多行记录为一行记录通过关键字concatenate 把几行的记录看成一行记录：1. LOADDATA 2. INFILE* 3. concatenate3/通过关键字concatenate把几行的记录看成一行记录 4. INTOTABLEDEPT 5. replace 6. FIELDSTERMINATEDBY, 7. (DEPTNO, 8. DNAMEupper(:dname), 9. LOCupper(:loc), 10. LAST_UPDATEDdatedd/mm/yyyy 11. ) 12. BEGINDATA 13. 10,Sales,/其实这3行看成一行10,Sales,Virginia,1/5/2000 14. Virginia, 15. 1/5/200010、用”|+|”分隔符，避免数据混淆：fields terminated by |+|11、如果数据文件包含在控制文件中，用INFILE *如下：1. LOADDATA 2. INFILE* 3. append 4. INTOTABLEtmp_test 5. FIELDSTERMINATEDBY, 6. OPTIONALLYENCLOSEDBY 7. TRAILINGNULLCOLS 8. (data1, 9. data2 10. ) 11. BEGINDATA 12. 11111,AAAAAAAAAA 13. 22222,A,B,C,D,12、一次导入多个文件到同一个表1. LOADDATA 2. INFILEfile1.dat 3. INFILEfile2.dat 4. INFILEfile3.dat 5. APPEND 6. INTOTABLEemp 7. (empnoPOSITION(1:4)INTEGEREXTERNAL, 8. enamePOSITION(6:15)CHAR, 9. deptnoPOSITION(17:18)CHAR, 10. mgrPOSITION(20:23)INTEGEREXTERNAL 11. )13、将一个文件导入到不同的表（1）、1. LOADDATA 2. INFILE* 3. INTOTABLEtab1WHENtab=tab1 4. (tabFILLERCHAR(4), 5. col1INTEGER 6. ) 7. INTOTABLEtab2WHENtab=tab2 8. (tabFILLERPOSITION(1:4), 9. col1INTEGER 10. ) 11. BEGINDATA 12. tab1|1 13. tab1|2 14. tab2|2 15. tab3|3 16. =（2）、1. LOADDATA 2. INFILEmydata.dat 3. REPLACE 4. INTOTABLEemp 5. WHENempno!= 6. (empnoPOSITION(1:4)INTEGEREXTERNAL, 7. enamePOSITION(6:15)CHAR, 8. deptnoPOSITION(17:18)CHAR, 9. mgrPOSITION(20:23)INTEGEREXTERNAL 10. ) 11. INTOTABLEproj 12. WHENprojno!= 13. (projnoPOSITION(25:27)INTEGEREXTERNAL, 14. empnoPOSITION(1:4)INTEGEREXTERNAL 15. )14、过滤掉的数据文件路径指定1. /opt/app/oracle/product/10.2.0/bin/sqlldrAPS/APScontrol=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTLLOG=/home/oracle/APS_LOAD/log/$yesterday/AP_CONTRACT_$yesterday.logbad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.badrows=10000readsize=20000000bindsize=20000000DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis15、附：测试用控制文件1. LOADDATA 2. INFILE/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat 3. TRUNCATE 4. INTOTABLEAP_CONTRACT 5. WHEN(01)1 6. FIELDSTERMINATEDBY| 7. TRAILINGNULLCOLS 8. ( 9. AGMT_NO(TRIM(:AGMT_NO), 10. CONTRACT_NOFILLER,-(TRIM(:CONTRACT_NO), 11. LOAN_AMT(TRIM(:LOAN_AMT), 12. AGMT_HOLDER(TRIM(:AGMT_HOLDER), 13. LOAN_TYPE_CD(TRIM(:LOAN_TYPE_CD), 14. CURR_CD(TRIM(:CURR_CD), 15. BALANCE(TRIM(:BALANCE), 16. LOAN_DIRC_CD(TRIM(:LOAN_DIRC_CD), 17. AGMT_START_DATE(TRIM(:AGMT_START_DATE), 18. AGMT_END_DATE(TRIM(:AGMT_END_DATE), 19. AGMT_BELONG_ORG_NO(TRIM(:AGMT_BELONG_ORG_NO), 20. MANAGER_NO(TRIM(:MANAGER_NO), 21. PROCESS_RATE(TRIM(:PROCESS_RATE), 22. INSURE_METH_TYPE_CD(TRIM(:INSURE_METH_TYPE_CD), 23. AGMT_SIGN_DATE(TRIM(:AGMT_SIGN_DATE), 24. LOAN_PROP_CD(TRIM(:LOAN_PROP_CD), 25. LOAN_USE_TYPE(TRIM(:LOAN_USE_TYPE), 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_TYPE_CD(TRIM(:FARM