熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle 使用雜記二

2013-11-13 16:02:46  來源: Oracle 

  下面的是關於sql*loader 的使用的一點總結 有些是來自itpub上的一些網友的總結
  
  大部分是oracle專家高級編程上的實例 只是我實踐以後寫的結果
  
  sqlldr userid=lgone/tiger control=actl
  
  LOAD DATA
  INFILE tdat    // 要導入的文件
  // INFILE ttdat // 導入多個文件
  // INFILE *     // 要導入的內容就在control文件裡 下面的BEGINDATA後面就是導入的內容
  
  INTO TABLE table_name  // 指定裝入的表
  // into table t_name partition (p_) 分區的載入
  
  BADFILE c:\badtxt   // 指定壞文件地址
  
  *************  以下是種裝入表的方式
  APPEND        // 原先的表有數據 就加在後面
  // INSERT      // 裝載空表 如果原先的表有數據 sqlloader會停止  默認值
  // REPLACE      // 原先的表有數據 原先的數據會全部刪除
  // TRUNCATE     // 指定的內容和replace的相同 會用truncate語句刪除現存數據
  
  SKIP   可以用 SKIP n 關鍵字來指定導入時可以跳過多少行數據
  
  ************* 指定的TERMINATED可以在表的開頭 也可在表的內部字段部分
  FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
  // 裝載這種數據: lglglglg
  // 在表中結果:   lg  lg  lglg
  // TERMINATED BY X     // 以十六進制格式 表示的
  // TERMINATED BY WRITESPACE  // 裝載這種數據: lg lg
  
  TRAILING NULLCOLS  *************  表的字段沒有對應的值時允許為空
  
  ************* 下面是表的字段
  (
  col_ col_ col_filler FILLER // FILLER 關鍵字 此列的數值不會被裝載
  // 如: lglgnot 結果 lg lg
  )
  // 當沒聲明FIELDS TERMINATED BY
  // (
  //  col_ [interger external] TERMINATED BY
  //  col_ [date ddmonyyy] TERMINATED BY
  //  col_ [char]       TERMINATED BY OPTIONALLY ENCLOSED BY lg
  // )
  // 當沒聲明FIELDS TERMINATED BY 用位置告訴字段裝載數據
  // (
  //  col_ position(:)
  //  col_ position(:)
  //  col_ position(*:) // 這個字段的開始位置在前一字段的結束位置
  //  col_ position(:)
  //  col_ position(:) char() // 指定字段的類型
  // )
  
  BEGINDATA  // 對應開始的 INFILE * 要導入的內容就在control文件裡
  Sqlwhat
  lgshow
  
  =====================================================================================
  //////////// 注意begindata後的數值前面不能有空格
  
         ***** 普通裝載
  LOAD DATA
  INFILE *
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
  (DEPTNO
  DNAME
  LOC
  )
  BEGINDATA
  SalesUSA
  AccountingVirginiaUSA
  ConsultingVirginia
  FinanceVirginia
  FinanceVirginia  // loc 列將為空
  FinanceVirginia   // loc 列將為空
  
         ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x 的情況
  LOAD DATA
  INFILE *
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY WHITESPACE
   FIELDS TERMINATED BY x
  (DEPTNO
  DNAME
  LOC
  )
  BEGINDATA
   Sales Virginia
  
        ***** 指定不裝載那一列  還可用 POSTION(x:y) 來分隔數據
  LOAD DATA
  INFILE *
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
  ( DEPTNO
  FILLER_ FILLER  // 下面的 Something Not To Be Loaded 將不會被裝載
  DNAME
  LOC
  )
  BEGINDATA
  Something Not To Be LoadedAccountingVirginiaUSA
  
        *****  position的列子
  LOAD DATA
  INFILE *
  INTO TABLE DEPT
  REPLACE
  ( DEPTNO   position(:)
  DNAME    position(*:) // 這個字段的開始位置在前一字段的結束位置
  LOC     position(*:)
  ENTIRE_LINE position(:)
  )
  BEGINDATA
  Accounting  VirginiaUSA
  
        *****  使用函數 日期的一種表達 TRAILING NULLCOLS的使用
  LOAD DATA
  INFILE *
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY
  TRAILING NULLCOLS  // 其實下面的ENTIRE_LINE在BEGINDATA後面的數據中是沒有直接對應
  // 的列的值的 如果第一行改為 SalesVirginia// 就不用TRAILING NULLCOLS了
  (DEPTNO
  DNAME    upper(:dname)  // 使用函數
  LOC     upper(:loc)
  LAST_UPDATED date dd/mm/yyyy // 日期的一種表達方式 還有ddmonyyyy
  ENTIRE_LINE :deptno||:dname||:loc||:last_updated
  )
  BEGINDATA
  SalesVirginia//
  AccountingVirginia//
  ConsultingVirginia//
  FinanceVirginia//
  
        *****  使用自定義的函數     // 解決的時間問題
  使用函數這僅適合於常規導入並不適合 direct導入方式 i可能可以
  create or replace
  function my_to_date( p_string in varchar ) return date
  as
  type fmtArray is table of varchar();
  
  l_fmts fmtArray := fmtArray( ddmonyyyy ddmonthyyyy
  dd/mm/yyyy
  dd/mm/yyyy hh:mi:ss );
  l_return date;
  begin
  for i in unt
  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(ddmmyyyy) + /// *
  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
  SalesVirginiaapril
  AccountingVirginia//
  ConsultingVirginia// ::
  FinanceVirginia
  FinanceVirginiaapr
  FinanceVirginiaNot a date
  
        *****  合並多行記錄為一行記錄
  LOAD DATA
  INFILE *
  concatenate   // 通過關鍵字concatenate 把幾行的記錄看成一行記錄
  INTO TABLE DEPT
  replace
  FIELDS TERMINATED BY
  (DEPTNO
  DNAME    upper(:dname)
  LOC     upper(:loc)
  LAST_UPDATED date dd/mm/yyyy
  )
  BEGINDATA
  Sales   // 其實這行看成一行 SalesVirginia//
  Virginia
  //
  // 這列子用 continueif list= 也可以
  告訴sqlldr在每行的末尾找逗號 找到逗號就把下一行附加到上一行
  
  LOAD DATA
  INFILE *
  continueif this(:) =  // 找每行的開始是否有連接字符  有就把下一行連接為一行
  // 如  SalesVirginia
  //   //       就是一行  SalesVirginia//
  // 其中: 表示從第一行開始 並在第一行結束  還有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            // 但是好象不能象右面的那樣使用
  SalesVirginia            SalesVirginia
  //                  //
                      FinanceVirginia//
  FinanceVirginia//
  
  ================================ 用別的語言幫助解決的方法
  txt文件中的每行作為一個記錄插入到數據庫中的一條記錄文件是定長的
      
   
  插入數據記錄是
  
  
  可以把換行符作為一個分隔符來處理
  
  
  到去下載一個activeperl MSI
  安裝 PERL
  
  你的文本文件示例testold
  
  
  a b c d e
  f g
  
  我的PERL程序testpl
  $mycount=;
  open(FILE_OLDTESTOLD);
  open(FILE_NEW>TESTNEW);
  while(<FILE_OLD>)
  {
  chomp;
  if ($mycount% == )
  {print FILE_NEW $_ ;}
  else
  {print FILE_NEW $_\n;}
  $mycount++;
  }
  
  在命令窗口下執行 perl testpl
  得到一個新的文本文件testnew內容如下
  
  
  a b c d e f g
  
  
  load data
  infile testtxt
  concatenate()
  into table aa
  fields terminated by whitespace
  (FIELDFIELDFIELDFIELDFIELDFIELDFIELD)
  ==============================================================
  
        *****  載入每行的行號
  
  load data
  infile *
  into table t
  replace
  ( seqno RECNUM   //載入每行的行號
  text Position(:))
  BEGINDATA
  fsdfasj       //自動分配一行號給載入 表t 的seqno字段 此行為
  fasdjfasdfl                       // 此行為  
  
        *****  載入有換行符的數據
  注意:  unix 和 windows 不同 \\n & /n
  還可以用 dbms_lob 和 bfile 看一個文件的回車 換行 等其他特殊字符
  
  < >  使用一個非換行符的字符
  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\nchr())  // replace 的使用幫助轉換換行符
  )
  BEGINDATA
  SalesVirginiaaprilThis is the Sales\nOffice in Virginia
  AccountingVirginia//This is the Accounting\nOffice in Virginia
  ConsultingVirginia// ::This is the Consulting\nOffice in Virginia
  FinanceVirginiaThis is the Finance\nOffice in Virginia
  
  < >  使用fix屬性
  LOAD DATA
  INFILE demodat fix
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY
  TRAILING NULLCOLS
  (DEPTNO
  DNAME    upper(:dname)
  LOC     upper(:loc)
  LAST_UPDATED my_to_date( :last_updated )
  COMMENTS
  )
  demodat
  SalesVirginiaaprilThis is the Sales
  Office in Virginia
  AccountingVirginia//This is the Accounting
  Office in Virginia
  ConsultingVirginia// ::This is the Consulting
  Office in Virginia
  FinanceVirginiaThis is the Finance
  Office in Virginia
  
  // 這樣裝載會把換行符裝入數據庫  下面的方法就不會 但要求數據的格式不同
  
  LOAD DATA
  INFILE demodat fix
  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
  )
  demodat
  SalesVirginiaaprilThis is the Sales
  Office in Virginia
  AccountingVirginia//This is the Accounting
  Office in Virginia
  ConsultingVirginia// ::This is the Consulting
  Office in Virginia
  FinanceVirginiaThis is the Finance
  Office in Virginia
  
  < >  使用var屬性
  LOAD DATA
  INFILE demodat var
  // 告訴每個記錄的前個字節表示記錄的長度 如第一個記錄的 表示此記錄有 個字節
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY
  TRAILING NULLCOLS
  (DEPTNO
  DNAME    upper(:dname)
  LOC     upper(:loc)
  LAST_UPDATED my_to_date( :last_updated )
  COMMENTS
  )
  demodat
  SalesVirginiaaprilThis is the Sales
  Office in Virginia
  AccountingVirginia//This is the Accounting
  Office in Virginia
  ConsultingVirginia// ::This is the Consulting
  Office in Virginia
  FinanceVirginiaThis is the Finance
  Office in Virginia
  
  < >  使用str屬性
  // 最靈活的一中 可定義一個新的行結尾符 win 回車換行 : chr()||chr()
  
  此列中記錄是以 a|\r\n 結束的
  select utl_rawcast_to_raw(|||chr()||chr()) from dual;
  結果  CDA
  
  LOAD DATA
  INFILE demodat str XCDA
  INTO TABLE DEPT
  REPLACE
  FIELDS TERMINATED BY
  TRAILING NULLCOLS
  (DEPTNO
  DNAME    upper(:dname)
  LOC     upper(:loc)
  LAST_UPDATED my_to_date( :last_updated )
  COMMENTS
  )
  demodat
  SalesVirginiaaprilThis is the Sales
  Office in Virginia|
  AccountingVirginia//This is the Accounting
  Office in Virginia|
  ConsultingVirginia// ::This is the Consulting
  Office in Virginia|
  FinanceVirginiaThis is the Finance
  Office in Virginia|
  
        *****  將數據導入多個表
  
  LOAD DATA
  INFILE *
  REPLACE
  INTO TABLE emp WHEN empno !=
  (
  empno POSITION(:) INTEGER EXTERNAL
  ename POSITION(:) CHAR
  deptno POSITION(:) CHAR
  mgr POSITION(:) INTEGER EXTERNAL
  )
  INTO TABLE proj WHEN projno !=
  (
  projno POSITION(:) INTEGER EXTERNAL
  empno POSITION(:) INTEGER EXTERNAL
  )
  
        *****  轉載 RAW 數據 或 轉載長字段
  
  options(bindsize=rows=)
  load data
  infile mydata fix   // * =  是小於K 的最大因子
  concatenate
  preserve blanks
  into table foo
  append
  (id constant bigdata raw())
  
        *****  轉載 LOB 數據
  
  用 dbms_lob  dbms的數據要在服務器上 通過網絡的不行
  
  drop table demo;
  
  create or replace directory dir  as c:\temp\;
  
  create or replace directory dir as c:\temp\;
  
  create table demo
  ( id    int primary key
  theClob  clob
  )
  /
  
  host echo Hello World\! > c:\temp\testtxt
  
  declare
  l_clob  clob;
  l_bfile  bfile;
  begin
  insert into demo values ( empty_clob() )
  returning theclob into l_clob;
  
  l_bfile := bfilename( DIR testtxt );  DIR 要大寫
  dbms_lobfileopen( l_bfile );
  
  dbms_lobloadfromfile( l_clob l_bfile
  dbms_lobgetlength( l_bfile ) );
  
  dbms_lobfileclose( l_bfile );
  end;
  /
  
  select dbms_lobgetlength(theClob) theClob from demo
  /
  
  
  用 sqlldr 在同一行的LOB lob數據在同一個數據文件中
  
  LOAD DATA
  INFILE demodat str XCDA
  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   char()
  )
  
  SalesVirginiaaprilThis is the Sales
  Office in Virginia|
  AccountingVirginia//This is the Accounting
  Office in Virginia|
  ConsultingVirginia// ::This is the Consulting
  Office in Virginia|
  FinanceVirginiaThis is the Finance
  Office in Virginia it has embedded commas and is
  much longer then the other comments field If you
  feel the need to add double quoted text in here like
  this: You will need to double up those quotes! to
  preserve them in the string This field keeps going for upto
   bytes or until we hit the magic end of record marker
  the | followed by a end of line it is right here >|
  
  
  用 sqlldr 不在同一行的LOB 就是lob數據在單獨的文件中
  
  create table lob_demo
  ( owner   varchar()
  timestamp date
  filename varchar()
  text   clob
  )
  /
  
  LOAD DATA  ///////////  window 的
  INFILE *
  REPLACE
  INTO TABLE LOB_DEMO
  ( owner   position(:)
  timestamp position(:) to_date(:timestamp||mmm/dd/yyyy hh:miam)
  filename  position(:)   下面的LOB的filename是從這裡來的
  text LOBFILE(filename) TERMINATED BY EOF
  )
  BEGINDATA
  // :p        BUILTIN\Administrators demolog
  // 這是 windows 下面的情況 上面的數據是用 dir /q/n 看見的情況 *******
  
  ///// unix 下的情況
  用 ls l 得到上面數據的情況
  控制文件就改下時間的格式
  
  
  lob 到對象列
  
  create table image_load( id number name varchar()
  image ordsysordimage )
  /
  
  desc ordsysordimage
  
  desc ordsysordsource
  
  LOAD DATA
  INFILE *
  INTO TABLE T
  replace
  fields terminated by
  (
  id
  name
  fiel_name filler
  image column object
  (
  source column object
  (
  localdatalobfile(file_name) terminated by bof
  nullif file_name=NONE
  )
  )
  )
  begindata
  iconsiconsgif
  
        *****  轉載varrays /嵌套表
  
  create type myArrayType
  as varray() of number()
  /
  
  create table t
  ( x int primary key y myArrayType )
  /
  
  LOAD DATA
  INFILE *
  INTO TABLE T
  replace
  fields terminated by
  (
  x
  y_cnt        FILLER
  y          varray count (y_cnt)
  (
  y
  )
  )
  
  BEGINDATA
  
  
  
  
  
  
  create or replace type myTableType
  as table of number()
  /
  
  create table t
  ( x int primary key y myTableType )
  nested table y store as y_tab
  /
  
  LOAD DATA
  INFILE *
  INTO TABLE T
  replace
  fields terminated by
  (
  x
  y          nested table count (CONSTANT )
  (
  y
  )
  )
  
  BEGINDATA
  
  
  
  ==============================================================================
  象這樣的數據 用 nullif 子句
  
  janFlipper seemed unusually hungry today
  janSpread over three meals
  
  id position(:) nullif id=blanks // 這裡可以是blanks 或者別的表達式
  // 下面是另一個列子 第一行的 在數據庫中將成為 null
  LOAD DATA
  INFILE *
  INTO TABLE T
  REPLACE
  (n position(:) integer external nullif n=
  v position(:)
  )
  BEGINDATA
   
  lg
  
  
  如果是英文的日志 格式可能需要修改環境變量 nls_lang or nls_date_format
From:http://tw.wingwit.com/Article/program/Oracle/201311/17716.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.