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 
< 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 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 
 
< 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) 
 
此列中記錄是以 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:8) 
) 
begindata 
1 10 
20lg 
------------------------------------------------------------ 
 
如果是英文的日志 格式，可能需要修改環境變量 nls_lang or nls_date_format