Oracle数据库SqlLoad常用技巧总结

楼主
我是社区第2639位番薯,欢迎点我头像关注我哦~
Oracle数据库SqlLoad常用技巧的相关知识是本文我们主要要介绍的内容,本文我们总结了14种SqlLoad的使用技巧,并给出了测试用的文件源码,接下来我们就开始一一介绍这部分内容,希望能够对您有所帮助。
1、控制文件中注释用“--”。
2、为防止导入出现中文乱码,在控制文件中加入字符集控制

  • LOAD DATA
  • CHARACTERSET ZHS16GBK
3、让某一列成为行号,用RECNUM关键字

  • load data
  • infile *
  • into table t
  • replace
  • ( seqno RECNUM //载入每行的行号
  • text Position(1:1024))
  • BEGINDATA
  • fsdfasj
4、过滤某一列,用FILLER关键字

  • LOAD DATA
  • TRUNCATE INTO TABLE T1
  • FIELDS TERMINATED BY ','
  • ( field1,
  • field2 FILLER,
  • field3
  • )
5、过滤行
在INTO TABLE table_name后加WHEN过滤条件,但功能有限,如果以竖线分隔符的文件,不能实现字段级的过滤,定长的还好。

  • LOAD DATA
  • INFILE 'mydata.dat'
  • BADFILE 'mydata.bad'
  • DISCARDFILE 'mydata.dis'
  • APPEND
  • INTO TABLE my_selective_table
  • WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '20031217'
  • (
  • region              CONSTANT '31',
  • service_key         POSITION(01:11)   INTEGER EXTERNAL,
  • call_b_no           POSITION(12:29)   CHAR
  • )
6、过滤首行,用OPTIONS (SKIP 1)选项,也可以写在命令行中,如:
sqlldr sms/admin control=test.ctl skip=1  
7、TRAILING NULLCOLS的使用,作用是表的字段没有对应的值时允许为空
如:

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

  • LOAD DATA
  • INFILE *
  • INTO TABLE tmp_test
  • ( rec_no                      "my_db_sequence.nextval",
  • region                      CONSTANT '31',
  • time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
  • data1        POSITION(1:5) ":data1/100",
  • data2        POSITION(6:15) "upper(:data2)",
  • data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
  • )
  • BEGINDATA
  • 11111AAAAAAAAAA991201
  • 22222BBBBBBBBBB990112
(2)、  

  • LOAD DATA
  • INFILE 'mail_orders.txt'
  • BADFILE 'bad_orders.txt'
  • APPEND
  • INTO TABLE mailing_list
  • FIELDS TERMINATED BY ","
  • ( addr,
  • city,
  • state,
  • zipcode,
  • mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",
  • mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",
  • mailing_state
  • )
9、合并多行记录为一行记录
通过关键字concatenate 把几行的记录看成一行记录:

  • LOAD DATA
  • INFILE *
  • concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
  • INTO TABLE DEPT
  • replace
  • FIELDS TERMINATED BY ','
  • (DEPTNO,
  • DNAME "upper(:dname)",
  • LOC "upper(:loc)",
  • LAST_UPDATED date 'dd/mm/yyyy'
  • )
  • BEGINDATA
  • 10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
  • Virginia,
  • 1/5/2000
10、用”|+|”分隔符,避免数据混淆:fields terminated by "|+|"
11、如果数据文件包含在控制文件中,用INFILE *
如下:

  • LOAD DATA
  • INFILE *
  • append
  • INTO TABLE tmp_test
  • FIELDS TERMINATED BY ","
  • OPTIONALLY ENCLOSED BY '"'
  • TRAILING NULLCOLS
  • ( data1,
  • data2
  • )
  • BEGINDATA
  • 11111,AAAAAAAAAA
  • 22222,"A,B,C,D,"
12、一次导入多个文件到同一个表

  • LOAD DATA
  • INFILE file1.dat
  • INFILE file2.dat
  • INFILE file3.dat
  • APPEND
  • INTO TABLE emp
  • ( empno POSITION(1:4)   INTEGER EXTERNAL,
  • ename POSITION(6:15) CHAR,
  • deptno POSITION(17:18) CHAR,
  • mgr    POSITION(20:23) INTEGER EXTERNAL
  • )
13、将一个文件导入到不同的表
(1)、  

  • LOAD DATA
  • INFILE *
  • INTO TABLE tab1 WHEN tab = 'tab1'
  • ( tab FILLER CHAR(4),
  • col1 INTEGER
  • )
  • INTO TABLE tab2 WHEN tab = 'tab2'
  • ( tab FILLER POSITION(1:4),
  • col1 INTEGER
  • )
  • BEGINDATA
  • tab1|1
  • tab1|2
  • tab2|2
  • tab3|3
  • ==============
(2)、  

  • LOAD DATA
  • INFILE 'mydata.dat'
  • REPLACE
  • INTO TABLE emp
  • WHEN empno != ' '
  • ( empno POSITION(1:4)   INTEGER EXTERNAL,
  • ename POSITION(6:15) CHAR,
  • deptno POSITION(17:18) CHAR,
  • mgr    POSITION(20:23) INTEGER EXTERNAL
  • )
  • INTO TABLE proj
  • WHEN projno != ' '
  • ( projno POSITION(25:27) INTEGER EXTERNAL,
  • empno POSITION(1:4)   INTEGER EXTERNAL
  • )
14、过滤掉的数据文件路径指定

  • /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、附:测试用控制文件

  • LOAD DATA
  • INFILE '/home/oracle/APS_LOAD/dat/APS_AP_CONTRACT.dat'
  • TRUNCATE
  • INTO TABLE AP_CONTRACT
  • WHEN (01)<>'1'
  • FIELDS TERMINATED BY "|"
  • TRAILING NULLCOLS
  • (
  • AGMT_NO                  "(TRIM(:AGMT_NO               ))",
  • CONTRACT_NO         FILLER, --     "(TRIM(:CONTRACT_NO           ))",
  • LOAN_AMT                 "(TRIM(:LOAN_AMT              ))",
  • AGMT_HOLDER              "(TRIM(:AGMT_HOLDER           ))",
  • LOAN_TYPE_CD             "(TRIM(:LOAN_TYPE_CD          ))",
  • CURR_CD                  "(TRIM(:CURR_CD               ))",
  • BALANCE                  "(TRIM(:BALANCE               ))",
  • LOAN_DIRC_CD             "(TRIM(:LOAN_DIRC_CD          ))",
  • AGMT_START_DATE          "(TRIM(:AGMT_START_DATE       ))",
  • AGMT_END_DATE            "(TRIM(:AGMT_END_DATE         ))",
  • AGMT_BELONG_ORG_NO       "(TRIM(:AGMT_BELONG_ORG_NO    ))",
  • MANAGER_NO               "(TRIM(:MANAGER_NO            ))",
  • PROCESS_RATE             "(TRIM(:PROCESS_RATE          ))",
  • INSURE_METH_TYPE_CD      "(TRIM(:INSURE_METH_TYPE_CD   ))",
  • AGMT_SIGN_DATE           "(TRIM(:AGMT_SIGN_DATE        ))",
  • LOAN_PROP_CD             "(TRIM(:LOAN_PROP_CD          ))",
  • LOAN_USE_TYPE            "(TRIM(:LOAN_USE_TYPE         ))",
  • ENTRUST_LOAN_FLAG        "(TRIM(:ENTRUST_LOAN_FLAG     ))",
  • ENTRUST_NAME             "(TRIM(:ENTRUST_NAME          ))",
  • FARM_LOAN_FLAG           "(TRIM(:FARM_LOAN_FLAG        ))",
  • FARM_LOAN_TYPE_CD        "(TRIM(:FARM_LOAN_TYPE_CD     ))",
  • LOAN_BIZ_TYPE_CD         "(TRIM(:LOAN_BIZ_TYPE_CD      ))",
  • ID_TEST                       RECNUM ,
  • CHAR_TEST                     CONSTANT '31',
  • SQ                        "sqlldr.nextval",
  • TEST_4                    "TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS')",
  • TEST_5                    "(TRIM(:LOAN_BIZ_TYPE_CD)||'---'||TRIM(:AGMT_NO))"
  • )

分享扩散:

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

0回帖数 1关注人数 6065浏览人数
最后回复于:2021-6-4 15:25

返回顶部 返回列表