Oracle数据库SqlLoad常用技巧总结

Oracle数据库SqlLoad常用技巧的相关知识是本文我们主要要介绍的内容，本文我们总结了14种SqlLoad的使用技巧，并给出了测试用的文件源码，接下来我们就开始一一介绍这部分内容，希望能够对您有所帮助。

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

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

 
 
 
    
  
  
  
  1. LOAD DATA  
    2. 
  
  
  
  2. CHARACTERSET ZHS16GBK  
    3.

 

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

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

 

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

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

 

5、过滤行

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

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

 

6、过滤首行，用OPTIONS (SKIP 1)选项，也可以写在命令行中，如：

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

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

如：

 
 
 
    
  
  
  
  1. LOAD DATA      
    2. 
  
  
  
  2. INFILE *      
    3. 
  
  
  
  3. INTO TABLE DEPT      
    4. 
  
  
  
  4. REPLACE      
    5. 
  
  
  
  5. FIELDS TERMINATED BY ','      
    6. 
  
  
  
  6. TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了      
    7. 
  
  
  
  7. (DEPTNO,      
    8. 
  
  
  
  8. DNAME "upper(:dname)", // 使用函数      
    9. 
  
  
  
  9. LOC "upper(:loc)",      
    10. 
  
  
  
  10. LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等      
    11. 
  
  
  
  11. ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"      
    12. 
  
  
  
  12. )      
    13. 
  
  
  
  13. BEGINDATA      
    14. 
  
  
  
  14. 10,Sales,Virginia,1/5/2000      
    15. 
  
  
  
  15. 20,Accounting,Virginia,21/6/1999      
    16. 
  
  
  
  16. 30,Consulting,Virginia,5/1/2000      
    17. 
  
  
  
  17. 40,Finance,Virginia,15/3/2001  
    18.

 

8、添加、修改数据

（1）、  

 
 
 
    
  
  
  
  1. LOAD DATA    
    2. 
  
  
  
  2. INFILE *    
    3. 
  
  
  
  3. INTO TABLE tmp_test    
    4. 
  
  
  
  4. ( rec_no                      "my_db_sequence.nextval",    
    5. 
  
  
  
  5. region                      CONSTANT '31',    
    6. 
  
  
  
  6. time_loaded                 "to_char(SYSDATE, 'HH24:MI')",    
    7. 
  
  
  
  7. data1        POSITION(1:5) ":data1/100",    
    8. 
  
  
  
  8. data2        POSITION(6:15) "upper(:data2)",    
    9. 
  
  
  
  9. data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"    
    10. 
  
  
  
  10. )    
    11. 
  
  
  
  11. BEGINDATA    
    12. 
  
  
  
  12. 11111AAAAAAAAAA991201    
    13. 
  
  
  
  13. 22222BBBBBBBBBB990112  
    14.

 

（2）、  

 
 
 
    
  
  
  
  1. LOAD DATA    
    2. 
  
  
  
  2. INFILE 'mail_orders.txt'    
    3. 
  
  
  
  3. BADFILE 'bad_orders.txt'    
    4. 
  
  
  
  4. APPEND    
    5. 
  
  
  
  5. INTO TABLE mailing_list    
    6. 
  
  
  
  6. FIELDS TERMINATED BY ","    
    7. 
  
  
  
  7. ( addr,    
    8. 
  
  
  
  8. city,    
    9. 
  
  
  
  9. state,    
    10. 
  
  
  
  10. zipcode,    
    11. 
  
  
  
  11. mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",    
    12. 
  
  
  
  12. mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",    
    13. 
  
  
  
  13. mailing_state    
    14. 
  
  
  
  14. )   
    15.

 

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

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

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

 

10、用”|+|”分隔符，避免数据混淆：fields terminated by "|+|"

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

如下：

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

 

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

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

 

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

（1）、  

 
 
 
    
  
  
  
  1. LOAD DATA    
    2. 
  
  
  
  2. INFILE *    
    3. 
  
  
  
  3. INTO TABLE tab1 WHEN tab = 'tab1'    
    4. 
  
  
  
  4. ( tab FILLER CHAR(4),    
    5. 
  
  
  
  5. col1 INTEGER    
    6. 
  
  
  
  6. )    
    7. 
  
  
  
  7. INTO TABLE tab2 WHEN tab = 'tab2'    
    8. 
  
  
  
  8. ( tab FILLER POSITION(1:4),    
    9. 
  
  
  
  9. col1 INTEGER    
    10. 
  
  
  
  10. )    
    11. 
  
  
  
  11. BEGINDATA    
    12. 
  
  
  
  12. tab1|1    
    13. 
  
  
  
  13. tab1|2    
    14. 
  
  
  
  14. tab2|2    
    15. 
  
  
  
  15. tab3|3    
    16. 
  
  
  
  16. ==============  
    17.

 

（2）、  

 
 
 
    
  
  
  
  1. LOAD DATA    
    2. 
  
  
  
  2. INFILE 'mydata.dat'    
    3. 
  
  
  
  3. REPLACE    
    4. 
  
  
  
  4. INTO TABLE emp    
    5. 
  
  
  
  5. WHEN empno != ' '    
    6. 
  
  
  
  6. ( empno POSITION(1:4)   INTEGER EXTERNAL,    
    7. 
  
  
  
  7. ename POSITION(6:15) CHAR,    
    8. 
  
  
  
  8. deptno POSITION(17:18) CHAR,    
    9. 
  
  
  
  9. mgr    POSITION(20:23) INTEGER EXTERNAL    
    10. 
  
  
  
  10. )    
    11. 
  
  
  
  11. INTO TABLE proj    
    12. 
  
  
  
  12. WHEN projno != ' '    
    13. 
  
  
  
  13. ( projno POSITION(25:27) INTEGER EXTERNAL,    
    14. 
  
  
  
  14. empno POSITION(1:4)   INTEGER EXTERNAL    
    15. 
  
  
  
  15. )   
    16.

 

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 
    2.

 

15、附：测试用控制文件

 
 
 
    
  
  
  
  1. LOAD DATA    
    2. 
  
  
  
  2. INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'    
    3. 
  
  
  
  3. TRUNCATE    
    4. 
  
  
  
  4. INTO TABLE AP_CONTRACT    
    5. 
  
  
  
  5. WHEN (01)<>'1'    
    6. 
  
  
  
  6. FIELDS TERMINATED BY "|"    
    7. 
  
  
  
  7. TRAILING NULLCOLS    
    8. 
  
  
  
  8. (    
    9. 
  
  
  
  9. AGMT_NO                  "(TRIM(:AGMT_NO               ))",        
    10. 
  
  
  
  10. CONTRACT_NO         FILLER, --     "(TRIM(:CONTRACT_NO           ))",     
    11. 
  
  
  
  11. LOAN_AMT                 "(TRIM(:LOAN_AMT              ))",    
    12. 
  
  
  
  12. AGMT_HOLDER              "(TRIM(:AGMT_HOLDER           ))",    
    13. 
  
  
  
  13. LOAN_TYPE_CD             "(TRIM(:LOAN_TYPE_CD          ))",    
    14. 
  
  
  
  14. CURR_CD                  "(TRIM(:CURR_CD               ))",    
    15. 
  
  
  
  15. BALANCE                  "(TRIM(:BALANCE               ))",    
    16. 
  
  
  
  16. LOAN_DIRC_CD             "(TRIM(:LOAN_DIRC_CD          ))",    
    17. 
  
  
  
  17. AGMT_START_DATE          "(TRIM(:AGMT_START_DATE       ))",    
    18. 
  
  
  
  18. AGMT_END_DATE            "(TRIM(:AGMT_END_DATE         ))",    
    19. 
  
  
  
  19. AGMT_BELONG_ORG_NO       "(TRIM(:AGMT_BELONG_ORG_NO    ))",    
    20. 
  
  
  
  20. MANAGER_NO               "(TRIM(:MANAGER_NO            ))",    
    21. 
  
  
  
  21. PROCESS_RATE             "(TRIM(:PROCESS_RATE          ))",    
    22. 
  
  
  
  22. INSURE_METH_TYPE_CD      "(TRIM(:INSURE_METH_TYPE_CD   ))",    
    23. 
  
  
  
  23. AGMT_SIGN_DATE           "(TRIM(:AGMT_SIGN_DATE        ))",    
    24. 
  
  
  
  24. LOAN_PROP_CD             "(TRIM(:LOAN_PROP_CD          ))",    
    25. 
  
  
  
  25. LOAN_USE_TYPE            "(TRIM(:LOAN_USE_TYPE         ))",    
    26. 
  
  
  
  26. ENTRUST_LOAN_FLAG        "(TRIM(:ENTRUST_LOAN_FLAG     ))",    
    27. 
  
  
  
  27. ENTRUST_NAME             "(TRIM(:ENTRUST_NAME          ))",    
    28. 
  
  
  
  28. FARM_LOAN_FLAG           "(TRIM(:FARM_LOAN_FLAG        ))",    
    29. 
  
  
  
  29. FARM_LOAN_TYPE_CD        "(TRIM(:FARM_LOAN_TYPE_CD     ))",    
    30. 
  
  
  
  30. LOAN_BIZ_TYPE_CD         "(TRIM(:LOAN_BIZ_TYPE_CD      ))",    
    31. 
  
  
  
  31. ID_TEST                       RECNUM ,    
    32. 
  
  
  
  32. CHAR_TEST                     CONSTANT '31',    
    33. 
  
  
  
  33. SQ                        "sqlldr.nextval",    
    34. 
  
  
  
  34. TEST_4                    "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",    
    35. 
  
  
  
  35. TEST_5                    "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"    
    36. 
  
  
  
  36. )   
    37.

 

关于Oracle数据库SqlLoad常用技巧的相关知识就介绍到这里了，希望本次的介绍能够对您有所收获！

本文名称：Oracle数据库SqlLoad常用技巧总结
网站URL：http://www.shufengxianlan.com/qtweb/news11/387611.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

建站公司知识

行业网站建设

广汉网站建设　　 　重庆网站设计　　 　江油网站建设　　 　盐亭网站建设　　 　宜宾网站设计　　 　梓潼网站建设　　 　成都app软件开发公司　　 　百度推广公司　　 　宣威网站建设　　 　微信小程序开发　　 　德阳定制网站建设　　 　红锐电子　　 　成都定制网站　　 　微信开发　　 　成都康明斯发电机　　 　免备案虚拟主机空间　　 　网站设计　　 　成都网站营销推广　　 　品牌官网设计　　 　和正兴机械