下面的是關于sql*loader 的使用的一點總結 有些是來自itpub上的一些網友的總結大部分是Oracle專家高級編程上的實例 只是我實踐以后寫的結果 ;;;;sqlldr userid=lgone/tiger control=a.ctlLOAD DATA INFILE 't.dat';;;;// 要導入的文件 // INFILE 'tt.dat'// 導入多個文件 // INFILE *; // 要導入的內容就在control文件里 下面的BEGINDATA后面就是導入的內容 INTO TABLE table_name;// 指定裝入的表 // into table t_name partition (p_1); 分區的載入 BADFILE 'c:bad.txt';;// 指定壞文件地址 *************以下是4種裝入表的方式 APPEND;;;;// 原先的表有數據 就加在后面 // INSERT;// 裝載空表 假如原先的表有數據 sqlloader會停止默認值 // REPLACE// 原先的表有數據 原先的數據會全部刪除 // TRUNCATE; // 指定的內容和replace的相同 會用truncate語句刪除現存數據 SKIP 5;可以用 'SKIP n' 要害字來指定導入時可以跳過多少行數據 *************; 指定的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;;Virginia3;***** 指定不裝載那一列還可用 POSTION(x:y) 來分隔數據 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,USA5;*****使用函數; 日期的一種表達; 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/20016;*****使用自定義的函數 // 解決的時間問題 使用函數這僅適合于常規導入，并不適合 direct導入方式 9i可能可以 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 date7;*****合并多行記錄為一行記錄 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/20001/5/2000 -40,;;;;40,Finance,Virginia,13/04/2001 Finance,Virginia,13/04/2001 ================================ 用別的語言幫助解決的方法 txt文件中的每2行作為一個記錄插入到數據庫中的一條記錄,文件是定長的 1; 2; 3; 4; 5 6; 7 插入數據記錄是 1 2 3 4 5 6 7 ------------- 可以把換行符作為一個分隔符來處理 ------------- 1、到www.activeperl.com去下載一個activeperl5.6 MSI 2、安裝 PERL 3、你的文本文件示例：test.old 1 2 3 4 5 6 7 a b c d e f g 4、我的PERL程序：test.pl $mycount=2; open(FILE_OLD','TEST.OLD'); open(FILE_NEW','>TEST.NEW'); while(<FILE_OLD>) { chomp; if ($mycount%2 == 0) {print FILE_NEW $_.' ';} else {print FILE_NEW $_.'n';} $mycount++; } 5、在命令窗口下執行 perl test.pl 6、得到一個新的文本文件：test.new,內容如下： 1 2 3 4 5 6 7 a b c d e f g --------------- load data infile 'test.txt' concatenate(2) into table aa fields terminated by whitespace (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7) ==============================================================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 還可以用 dbms_lob; 和 bfile 看一個文件的回車 換行 等其他非凡字符 < 1 >使用一個非換行符的字符 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 SalesnOffice in Virginia 20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia 40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia < 2 >使用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'; < 3 >使用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 < 4 >使用str屬性 // 最靈活的一中 可定義一個新的行結尾符; win 回車換行 : chr(13)chr(10) 此列中記錄是以 arn 結束的 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 Virginia10*****將數據導入多個表 LOAD DATA INFILE * 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 ) 11*****轉載 RAW 數據 或 轉載長字段 options(bindsize=1075700,rows=1) load data infile my.data 'fix 53760'// 53760 * 20=1075700;1075700是小于64K 的最大因子 concatenate 20 preserve blanks into table foo append (id constant 1,bigdata raw(1075700))12*****轉載 LOB 數據 用 dbms_lobdbms_lob 轉載的數據要在服務器上 通過網絡的不行drop table demo;create or replace Directory dir1as 'c:temp';create or replace directory 'dir2' as 'c:temp';create table demo( id;;;;;int primary key, theClobclob)/host echo 'Hello World!' > c:temptest.txtdeclare l_clob;clob; l_bfilebfile;begin insert into demo values ( 1, empty_clob() ) returning theclob into l_clob; l_bfile := bfilename( 'DIR1', 'test.txt' )-- DIR1 要大寫 dbms_lob.fileopen( l_bfile ); dbms_lob.loadfromfile( l_clob, l_bfile, dbms_lob.getlength( l_bfile ) ); dbms_lob.fileclose( l_bfile );end;/select dbms_lob.getlength(theClob), theClob from demo/----------------------------------用 sqlldr; 在同一行的LOB; lob數據在同一個數據文件中LOAD DATAINFILE demo21.dat 'str X'7C0D0A''INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '''TRAILING NULLCOLS(DEPTNO, DNAME;;;;;'upper(:dname)', LOC; 'upper(:loc)', LAST_UPDATED 'my_to_date( :last_updated )', COMMENTS;;char(1000000))10,Sales,Virginia,01-april-2001,This is the SalesOffice in Virginia20,Accounting,Virginia,13/04/2001,This is the AccountingOffice in Virginia30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingOffice in Virginia40,Finance,Virginia,987268297,'This is the FinanceOffice in Virginia, it has embedded commas and ismUCh longer then the other comments field.; If you feel the need to add double quoted text in here likethis: ''You will need to double up those quotes! '' to preserve them in the string.; This field keeps going for upto1,000,000 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;;varchar2(255), timestamp date, filename; varchar2(255), text;;;clob)/LOAD DATA///////////window 的INFILE *REPLACEINTO TABLE LOB_DEMO( owner;;;position(40:61), timestamp; position(1:18) 'to_date(:timestamp'm','mm/dd/yyyy; hh:miam')', filenameposition(63:80),;-- 下面的LOB的filename是從這里來的 text LOBFILE(filename) TERMINATED BY EOF)BEGINDATA04/14/2001; 12:36p;;;;1,697 BUILTINAdministrators demo10.log // 這是 windows 下面的情況 上面的數據是用 dir /q/n 看見的情況; *******///// unix 下的情況 用 ls -l 得到上面數據的情況 控制文件就改下時間的格式------------------------------lob 到對象列create table image_load( id number, name varchar2(255), image ordsys.ordimage )/desc ordsys.ordimagedesc ordsys.ordsourceLOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( id, name, fiel_name filler, image column object ( source column object ( localdatalobfile(file_name) terminated by bof nullif file_name='NONE' ) ))begindata1,icons,icons.gif13*****轉載varrays /嵌套表create type myArrayTypeas varray(10) of number(12,2)/create table t( x int primary key, y myArrayType )/LOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( x, y_cnt;;;;FILLER, yvarray count (y_cnt) ( y ))BEGINDATA1,2,3,42,10,1,2,3,4,5,6,7,8,9,103,5,5,4,3,2,1------------------------------------create or replace type myTableTypeas table of number(12,2)/create table t( x int primary key, y myTableType )nested table y store as y_tab/LOAD DATAINFILE *INTO TABLE Treplacefields terminated by ','( x, ynested table count (CONSTANT 5) ( y ))BEGINDATA1,100,200,300,400,5002,123,243,542,123,432==============================================================================象這樣的數據 用 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:8) );;;;; ;; BEGINDATA 1; 10 20lg------------------------------------------------------------假如是英文的日志 格式，可能需要修改環境變量 nls_lang or nls_date_format