trim

trim

关注
发信
关注(28)粉丝(399)

SqlLoad常用技巧总结

1、控制文件中注释用“--”

2、为防止导入出现中文乱码，在控制文件中加入字符集控制

LOAD DATA

CHARACTERSET ZHS16GBK 

3、让某一列成为行号，用RECNUM关键字

  1. load data
  2. infile *
  3. into table t
  4. replace
  5. ( seqno RECNUM //载入每行的行号
  6. text Position(1:1024))
  7. BEGINDATA
  8. fsdfasj

4、过滤某一列，用FILLER关键字

  1. LOAD DATA
  2. TRUNCATE INTO TABLE T1
  3. FIELDS TERMINATED BY ','
  4. ( field1,
  5. field2 FILLER,
  6. field3
  7. )

5、过滤行

在INTO TABLE table_name后加WHEN过滤条件，但功能有限，如果以竖线分隔符的文件，不能实现字段级的过滤，定长的还好。

  1. LOAD DATA
  2. INFILE 'mydata.dat'
  3. BADFILE 'mydata.bad'
  4. DISCARDFILE 'mydata.dis'
  5. APPEND
  6. INTO TABLE my_selective_table
  7. WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'
  8. (
  9. region              CONSTANT '31',
  10. service_key         POSITION(01:11)   INTEGER EXTERNAL,
  11. call_b_no           POSITION(12:29)   CHAR
  12. )

6、过滤首行，用OPTIONS (SKIP 1)选项

也可以写在命令行中，如：

  1. sqlldr sms/admin control=test.ctl skip=1

7、TRAILING NULLCOLS的使用，作用是表的字段没有对应的值时允许为空

如：

  1. LOAD DATA
  2. INFILE *
  3. INTO TABLE DEPT
  4. REPLACE
  5. FIELDS TERMINATED BY ','
  6. TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
  7. (DEPTNO,
  8. DNAME "upper(:dname)", // 使用函数
  9. LOC "upper(:loc)",
  10. LAST_UPDATED date 'dd/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/2001

8、添加、修改数据

  1. （1）、
  2. LOAD DATA
  3. INFILE *
  4. INTO TABLE tmp_test
  5. ( rec_no                      "my_db_sequence.nextval",
  6. region                      CONSTANT '31',
  7. time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
  8. data1        POSITION(1:5) ":data1/100",
  9. data2        POSITION(6:15) "upper(:data2)",
  10. data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
  11. )
  12. BEGINDATA
  13. 11111AAAAAAAAAA991201
  14. 22222BBBBBBBBBB990112
  15. （2）、
  16. LOAD DATA
  17. INFILE 'mail_orders.txt'
  18. BADFILE 'bad_orders.txt'
  19. APPEND
  20. INTO TABLE mailing_list
  21. FIELDS TERMINATED BY ","
  22. ( addr,
  23. city,
  24. state,
  25. zipcode,
  26. mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",
  27. mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",
  28. mailing_state
  29. )

9、合并多行记录为一行记录

通过关键字concatenate 把几行的记录看成一行记录

  1. LOAD DATA
  2. INFILE *
  3. concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
  4. INTO TABLE DEPT
  5. replace
  6. FIELDS TERMINATED BY ','
  7. (DEPTNO,
  8. DNAME "upper(:dname)",
  9. LOC "upper(:loc)",
  10. LAST_UPDATED date 'dd/mm/yyyy'
  11. )
  12. BEGINDATA
  13. 10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
  14. Virginia,
  15. 1/5/2000

10、用”|+|”分隔符，避免数据混淆

fields terminated by "|+|"

11、如果数据文件包含在控制文件中，用INFILE *

如下：

  1. LOAD DATA
  2. INFILE *
  3. append
  4. INTO TABLE tmp_test
  5. FIELDS TERMINATED BY ","
  6. OPTIONALLY ENCLOSED BY '"'
  7. TRAILING NULLCOLS
  8. ( data1,
  9. data2
  10. )
  11. BEGINDATA
  12. 11111,AAAAAAAAAA
  13. 22222,"A,B,C,D,"

12、一次导入多个文件到同一个表

  1. LOAD DATA
  2. INFILE file1.dat
  3. INFILE file2.dat
  4. INFILE file3.dat
  5. APPEND
  6. INTO TABLE emp
  7. ( empno POSITION(1:4)   INTEGER EXTERNAL,
  8. ename POSITION(6:15) CHAR,
  9. deptno POSITION(17:18) CHAR,
  10. mgr    POSITION(20:23) INTEGER EXTERNAL
  11. )

13、将一个文件导入到不同的表

  1. （1）、
  2. LOAD DATA
  3. INFILE *
  4. INTO TABLE tab1 WHEN tab = 'tab1'
  5. ( tab FILLER CHAR(4),
  6. col1 INTEGER
  7. )
  8. INTO TABLE tab2 WHEN tab = 'tab2'
  9. ( tab FILLER POSITION(1:4),
  10. col1 INTEGER
  11. )
  12. BEGINDATA
  13. tab1|1
  14. tab1|2
  15. tab2|2
  16. tab3|3
  17. ==============
  18. （2）、
  19. LOAD DATA
  20. INFILE 'mydata.dat'
  21. REPLACE
  22. INTO TABLE emp
  23. WHEN empno != ' '
  24. ( empno POSITION(1:4)   INTEGER EXTERNAL,
  25. ename POSITION(6:15) CHAR,
  26. deptno POSITION(17:18) CHAR,
  27. mgr    POSITION(20:23) INTEGER EXTERNAL
  28. )
  29. INTO TABLE proj
  30. WHEN projno != ' '
  31. ( projno POSITION(25:27) INTEGER EXTERNAL,
  32. empno POSITION(1:4)   INTEGER EXTERNAL
  33. )

14、过滤掉的数据文件路径指定

  1. /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、附：测试用控制文件

  1. LOAD DATA
  2. INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'
  3. TRUNCATE
  4. INTO TABLE AP_CONTRACT
  5. WHEN (01)<>'1'
  6. FIELDS TERMINATED BY "|"
  7. TRAILING NULLCOLS
  8. (
  9. AGMT_NO                  "(TRIM(:AGMT_NO               ))",
  10. CONTRACT_NO         FILLER, --     "(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_LOAN_TYPE_CD     ))",
  30. LOAN_BIZ_TYPE_CD         "(TRIM(:LOAN_BIZ_TYPE_CD      ))",
  31. ID_TEST                       RECNUM ,
  32. CHAR_TEST                     CONSTANT '31',
  33. SQ                        "sqlldr.nextval",
  34. TEST_4                    "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",
  35. TEST_5                    "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"
  36. )
05-20 17:40
Powered by LMLPHP ©2024 trim 0.053212