当前位置:Gxlcms > mysql > Oracle数据库SqlLoad常用技巧总结

Oracle数据库SqlLoad常用技巧总结

时间:2021-07-01 10:21:17 帮助过:14人阅读

欢迎进入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]

人气教程排行