欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle数据库SqlLoad常用技巧总结

程序员文章站 2022-04-08 08:53:21
...

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 1、控制文件中注释用"--". 2、为防止导入出现中文乱码,在控制文件中加入字符集控制 LOAD DATA CHARACTERSET ZHS16GBK 3、让某一列成为行号,用RECNUM关键字 load data infile * into table t repla

欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入

  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

  )

[1] [2]

Oracle数据库SqlLoad常用技巧总结