[转]sql load 的一点小总结

  sqlldr   userid=lgone/tiger   control=a.ctl    
  LOAD   DATA    
  INFILE   't.dat'   //   要导入的文件    
  //   INFILE   'tt.date'   //   导入多个文件    
  //   INFILE   *   //   要导入的内容就在control文件里   下面的BEGINDATA后面就是导入的内容    
   
  INTO   TABLE   table_name   //   指定装入的表    
  BADFILE   'c:\bad.txt'   //   指定坏文件地址    
   
  *************   以下是4种装入表的方式    
  APPEND   //   原先的表有数据   就加在后面    
  //   INSERT   //   装载空表   如果原先的表有数据   sqlloader会停止   默认值    
  //   REPLACE   //   原先的表有数据   原先的数据会全部删除    
  //   TRUNCATE   //   指定的内容和replace的相同   会用truncate语句删除现存数据    
   
  *************   指定的TERMINATED可以在表的开头   也可在表的内部字段部分    
  FIELDS   TERMINATED   BY   ','   OPTIONALLY   ENCLOSED   BY   '"'    
  //   装载这种数据:   10,lg,"""lg""","lg,lg"    
  //   在表中结果:   10   lg   "lg"   lg,lg    
  //   TERMINATED   BY   X   '09'   //   以十六进制格式   '09'   表示的    
  //   TERMINATED   BY   WRITESPACE   //   装载这种数据:   10   lg   lg    
   
  TRAILING   NULLCOLS   *************   表的字段没有对应的值时允许为空    
   
  *************   下面是表的字段    
  (    
  col_1   ,   col_2   ,col_filler   FILLER   //   FILLER   关键字   此列的数值不会被装载    
  //   如:   lg,lg,not   结果   lg   lg    
  )    
  //   当没声明FIELDS   TERMINATED   BY   ','   时    
  //   (    
  //   col_1   [interger   external]   TERMINATED   BY   ','   ,    
  //   col_2   [date   "dd-mon-yyy"]   TERMINATED   BY   ','   ,    
  //   col_3   [char]   TERMINATED   BY   ','   OPTIONALLY   ENCLOSED   BY   'lg'    
  //   )    
  //   当没声明FIELDS   TERMINATED   BY   ','用位置告诉字段装载数据    
  //   (    
  //   col_1   position(1:2),    
  //   col_2   position(3:10),    
  //   col_3   position(*:16),   //   这个字段的开始位置在前一字段的结束位置    
  //   col_4   position(1:16),    
  //   col_5   position(3:10)   char(8)   //   指定字段的类型    
  //   )    
   
  BEGINDATA   //   对应开始的   INFILE   *   要导入的内容就在control文件里    
  10,Sql,what    
  20,lg,show    
   
  =====================================================================================    
     注意begindata后的数值前面不能有空格    
   
  1   *****   普通装载    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','   OPTIONALLY   ENCLOSED   BY   '"'    
  (DEPTNO,    
  DNAME,    
  LOC    
  )    
  BEGINDATA    
  10,Sales,"""USA"""    
  20,Accounting,"Virginia,USA"    
  30,Consulting,Virginia    
  40,Finance,Virginia    
  50,"Finance","",Virginia   //   loc   列将为空    
  60,"Finance",,Virginia   //   loc   列将为空    
   
  2   *****   FIELDS   TERMINATED   BY   WHITESPACE   和   FIELDS   TERMINATED   BY   x'09'   的情况    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   WHITESPACE    
  --   FIELDS   TERMINATED   BY   x'09'    
  (DEPTNO,    
  DNAME,    
  LOC    
  )    
  BEGINDATA    
  10   Sales   Virginia    
   
  3   *****   指定不装载那一列    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','   OPTIONALLY   ENCLOSED   BY   '"'    
  (   DEPTNO,    
  FILLER_1   FILLER,   //   下面的   "Something   Not   To   Be   Loaded"   将不会被装载    
  DNAME,    
  LOC    
  )    
  BEGINDATA    
  20,Something   Not   To   Be   Loaded,Accounting,"Virginia,USA"    
   
  4   *****   position的列子    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  (   DEPTNO   position(1:2),    
  DNAME   position(*:16),   //   这个字段的开始位置在前一字段的结束位置    
  LOC   position(*:29),    
  ENTIRE_LINE   position(1:29)    
  )    
  BEGINDATA    
  10Accounting   Virginia,USA    
   
  5   *****   使用函数   日期的一种表达   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    
   
  6   *****   使用自定义的函数   //   解决的时间问题    
  create   or   replace    
  function   my_to_date(   p_string   in   varchar2   )   return   date    
  as    
  type   fmtArray   is   table   of   varchar2(25);    
   
  l_fmts   fmtArray   :=   fmtArray(   'dd-mon-yyyy',   'dd-month-yyyy',    
  'dd/mm/yyyy',    
  'dd/mm/yyyy   hh24:mi:ss'   );    
  l_return   date;    
  begin    
  for   i   in   1   ..   l_fmts.count    
  loop    
  begin    
  l_return   :=   to_date(   p_string,   l_fmts(i)   );    
  exception    
  when   others   then   null;    
  end;    
  EXIT   when   l_return   is   not   null;    
  end   loop;    
   
  if   (   l_return   is   null   )    
  then    
  l_return   :=    
  new_time(   to_date('01011970','ddmmyyyy')   +   1/24/60/60   *    
  p_string,   'GMT',   'EST'   );    
  end   if;    
   
  return   l_return;    
  end;    
  /    
   
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )"   //   使用自定义的函数    
  )    
  BEGINDATA    
  10,Sales,Virginia,01-april-2001    
  20,Accounting,Virginia,13/04/2001    
  30,Consulting,Virginia,14/04/2001   12:02:02    
  40,Finance,Virginia,987268297    
  50,Finance,Virginia,02-apr-2001    
  60,Finance,Virginia,Not   a   date    
   
  7   *****   合并多行记录为一行记录    
  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    
  //   这列子用   continueif   list=","   也可以    
  告诉sqlldr在每行的末尾找逗号   找到逗号就把下一行附加到上一行   
LOAD   DATA    
  INFILE   *    
  continueif   this(1:1)   =   '-'   //   找每行的开始是否有连接字符   -   有就把下一行连接为一行    
  //   如   -10,Sales,Virginia,    
  //   1/5/2000   就是一行   10,Sales,Virginia,1/5/2000    
  //   其中1:1   表示从第一行开始   并在第一行结束   还有continueif   next   但continueif   list最理想    
  INTO   TABLE   DEPT    
  replace    
  FIELDS   TERMINATED   BY   ','    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   date   'dd/mm/yyyy'    
  )    
  BEGINDATA   //   但是好象不能象右面的那样使用    
  -10,Sales,Virginia,   -10,Sales,Virginia,    
  1/5/2000   1/5/2000    
  -40,   40,Finance,Virginia,13/04/2001    
  Finance,Virginia,13/04/2001    
   
  8   *****   载入每行的行号    
   
  load   data    
  infile   *    
  into   table   t    
  replace    
  (   seqno   RECNUM   //载入每行的行号    
  text   Position(1:1024))    
  BEGINDATA    
  fsdfasj   //自动分配一行号给载入   表t   的seqno字段   此行为   1    
  fasdjfasdfl   //   此行为   2   ...    
   
  9   *****   载入有换行符的数据    
  注意:   unix   和   windows   不同   \\n   &   /n    
     使用一个非换行符的字符    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )",    
  COMMENTS   "replace(:comments,'\n',chr(10))"   //   replace   的使用帮助转换换行符    
  )    
  BEGINDATA    
  10,Sales,Virginia,01-april-2001,This   is   the   Sales\nOffice   in   Virginia    
  20,Accounting,Virginia,13/04/2001,This   is   the   Accounting\nOffice   in   Virginia    
  30,Consulting,Virginia,14/04/2001   12:02:02,This   is   the   Consulting\nOffice   in   Virginia    
  40,Finance,Virginia,987268297,This   is   the   Finance\nOffice   in   Virginia    
   
     使用fix属性    
  LOAD   DATA    
  INFILE   demo17.dat   "fix   101"    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )",    
  COMMENTS    
  )    
  demo17.dat    
  10,Sales,Virginia,01-april-2001,This   is   the   Sales    
  Office   in   Virginia    
  20,Accounting,Virginia,13/04/2001,This   is   the   Accounting    
  Office   in   Virginia    
  30,Consulting,Virginia,14/04/2001   12:02:02,This   is   the   Consulting    
  Office   in   Virginia    
  40,Finance,Virginia,987268297,This   is   the   Finance    
  Office   in   Virginia    
   
  //   这样装载会把换行符装入数据库   下面的方法就不会   但要求数据的格式不同    
   
  LOAD   DATA    
  INFILE   demo18.dat   "fix   101"    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','   OPTIONALLY   ENCLOSED   BY   '"'    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )",    
  COMMENTS    
  )    
  demo18.dat    
  10,Sales,Virginia,01-april-2001,"This   is   the   Sales    
  Office   in   Virginia"    
  20,Accounting,Virginia,13/04/2001,"This   is   the   Accounting    
  Office   in   Virginia"    
  30,Consulting,Virginia,14/04/2001   12:02:02,"This   is   the   Consulting    
  Office   in   Virginia"    
  40,Finance,Virginia,987268297,"This   is   the   Finance    
  Office   in   Virginia"    
   
     使用var属性    
  LOAD   DATA    
  INFILE   demo19.dat   "var   3"    
  //   3   告诉每个记录的前3个字节表示记录的长度   如第一个记录的   071   表示此记录有   71   个字节    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )",    
  COMMENTS    
  )    
  demo19.dat    
  07110,Sales,Virginia,01-april-2001,This   is   the   Sales    
  Office   in   Virginia    
  07820,Accounting,Virginia,13/04/2001,This   is   the   Accounting    
  Office   in   Virginia    
  08730,Consulting,Virginia,14/04/2001   12:02:02,This   is   the   Consulting    
  Office   in   Virginia    
  07140,Finance,Virginia,987268297,This   is   the   Finance    
  Office   in   Virginia    
   
     使用str属性    
  //   最灵活的一中   可定义一个新的行结尾符   win   回车换行   :   chr(13)||chr(10)    
   
  此列中记录是以   a|\r\n   结束的    
  select   utl_raw.cast_to_raw('|'||chr(13)||chr(10))   from   dual;    
  结果   7C0D0A    
   
  LOAD   DATA    
  INFILE   demo20.dat   "str   X'7C0D0A'"    
  INTO   TABLE   DEPT    
  REPLACE    
  FIELDS   TERMINATED   BY   ','    
  TRAILING   NULLCOLS    
  (DEPTNO,    
  DNAME   "upper(:dname)",    
  LOC   "upper(:loc)",    
  LAST_UPDATED   "my_to_date(   :last_updated   )",    
  COMMENTS    
  )    
  demo20.dat    
  10,Sales,Virginia,01-april-2001,This   is   the   Sales    
  Office   in   Virginia|    
  20,Accounting,Virginia,13/04/2001,This   is   the   Accounting    
  Office   in   Virginia|    
  30,Consulting,Virginia,14/04/2001   12:02:02,This   is   the   Consulting    
  Office   in   Virginia|    
  40,Finance,Virginia,987268297,This   is   the   Finance    
  Office   in   Virginia|    
   
  ==============================================================================    
  象这样的数据   用   nullif   子句    
   
  10-jan-200002350Flipper   seemed   unusually   hungry   today.    
  10510-jan-200009945Spread   over   three   meals.    
   
  id   position(1:3)   nullif   id=blanks   //   这里可以是blanks   或者别的表达式    
  //   下面是另一个列子   第一行的   1   在数据库中将成为   null    
  LOAD   DATA    
  INFILE   *    
  INTO   TABLE   T    
  REPLACE    
  (n   position(1:2)   integer   external   nullif   n='1',    
  v   position(3    
  )    
  BEGINDATA    
  1   10    
  20lg    
  ------------------------------------------------------------    
   
  如果是英文的日志   格式，可能需要修改环境变量   nls_lang   or   nls_date_format   

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23590362/viewspace-630446/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23590362/viewspace-630446/