Oracle数据库SqlLoad常用技巧总结

数据库 Oracle
本文我们主要介绍了Oracle数据库SqlLoad常用技巧的相关知识,共包括14种常用的使用技巧以及测试用文件的源代码,希望能够对您有所收获!

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

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

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

 

  1. LOAD DATA  
  2. 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)选项,也可以写在命令行中,如:

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

 

(2)、  

 

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

 

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

 

(2)、  

 

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

 

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

 

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

【编辑推荐】

  1. Oracle学习笔记之DECODE及常用窗口函数
  2. Oracle数据库各类控制语句的使用详细介绍
  3. Oracle数据库日期范围查询的两种实现方式
  4. Oracle数据库只读模式的CACHE BUFFERS CHAINS测试
  5. Oracle 10g数据库中UNDO_RETENTION参数的使用详解
责任编辑:赵鹏 来源: CSDN博客
相关推荐

2011-09-02 10:37:15

Oraclesqlload用法

2011-03-17 14:09:25

Oracle数据库字符

2010-11-29 11:51:59

Sybase数据库维护

2011-08-01 13:59:22

Oracle数据库命名空间

2011-04-12 10:09:33

Oracle数据库关闭

2010-03-18 09:28:14

Oracle数据库迁移

2010-04-06 11:19:28

Oracle数据库

2010-04-06 16:50:07

Oracle数据库

2010-04-20 10:41:49

Oracle数据库

2011-06-14 15:11:59

ORACLE

2010-04-13 10:32:40

Oracle数据库编程

2011-03-17 16:35:20

Oracle数据库段管理

2011-03-17 16:19:42

Oracle数据库管理

2009-05-18 13:18:54

字符Oracle字符串

2010-04-06 11:02:30

Oracle 数据库

2019-08-28 07:11:00

Oracle数据库LOB

2010-11-15 10:30:04

Oracle数据库启动

2016-12-12 13:07:57

数据库优化SQL

2011-03-11 16:25:53

Oracle数据库

2011-04-11 16:50:13

Oracle数据库索引
点赞
收藏

51CTO技术栈公众号