Oracle 的SQL*LOADER可以將外部數(shù)據(jù)加載到數(shù)據(jù)庫表中。下面是SQL*LOADER的基本特點(diǎn)：
1）能裝入不同數(shù)據(jù)類型文件及多個數(shù)據(jù)文件的數(shù)據(jù)
2）可裝入固定格式，自由定界以及可度長格式的數(shù)據(jù)
3）可以裝入二進(jìn)制，壓縮十進(jìn)制數(shù)據(jù)
4）一次可對多個表裝入數(shù)據(jù)
5）連接多個物理記錄裝到一個記錄中
6）對一單記錄分解再裝入到表中
7）可以用 數(shù)對制定列生成唯一的KEY
8）可對磁盤或 磁帶數(shù)據(jù)文件裝入制表中
9）提供裝入錯誤報告
10）可以將文件中的整型字符串，自動轉(zhuǎn)成壓縮十進(jìn)制并裝入列表中。
1.2控制文件
控制文件是用一種語言寫的文本文件，這個文本文件能被SQL*LOADER識別。SQL*LOADER根據(jù)控制文件可以找到需要加載的數(shù)據(jù)。并且分析和解釋這些數(shù)據(jù)。控制文件由三個部分組成：
l 全局選件，行，跳過的記錄數(shù)等；
l INFILE子句指定的輸入數(shù)據(jù)；
l 數(shù)據(jù)特性說明。
1.3輸入文件
對于 SQL*Loader, 除控制文件外就是輸入數(shù)據(jù)。SQL*Loader可從一個或多個指定的文件中讀出數(shù)據(jù)。如果 數(shù)據(jù)是在控制文件中指定，就要在控制文件中寫成 INFILE * 格式。當(dāng)數(shù)據(jù)固定的格式（長度一樣）時且是在文件中得到時，要用INFILE "fix n"
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
當(dāng)數(shù)據(jù)是可變格式（長度不一樣）時且是在文件中得到時，要用INFILE "var n"。如：
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,
1.4壞文件
壞文件包含那些被SQL*Loader拒絕的記錄。被拒絕的記錄可能是不符合要求的記錄。
壞文件的名字由 SQL*Loader命令的BADFILE 參數(shù)來給定。
1.5日志文件及日志信息
當(dāng)SQL*Loader 開始執(zhí)行后，它就自動建立 日志文件。日志文件包含有加載的總結(jié)，加載中的錯誤信息等。
控制文件語法
控制文件的格式如下：
OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
1）要加載的數(shù)據(jù)文件：
1．INFILE 和INDDN是同義詞，它們后面都是要加載的數(shù)據(jù)文件。如果用 * 則表示數(shù)據(jù)就在控制文件內(nèi)。在INFILE 后可以跟幾個文件。
2．STRAM 表示一次讀一個字節(jié)的數(shù)據(jù)。新行代表新物理記錄（邏輯記錄可由幾個物理記錄組成）。
3．RECORD 使用宿主操作系統(tǒng)文件及記錄管理系統(tǒng)。如果數(shù)據(jù)在控制文件中則使用這種方法。
3． FIXED length 要讀的記錄長度為length字節(jié)，
4． VARIABLE 被讀的記錄中前兩個字節(jié)包含的長度，length 記錄可能的長度。缺傷為8k字節(jié)。
5． BADFILE和BADDN同義。Oracle 不能加載數(shù)據(jù)到數(shù)據(jù)庫的那些記錄。
6． DISCARDFILE和DISCARDDN是同義詞。記錄沒有通過的數(shù)據(jù)。
7． DISCARDS和DISCARDMAX是同義詞。Integer 為最大放棄的文件個數(shù)。
2）加載的方法：
1．APPEND 給表添加行。
2．INSERT 給空表增加行（如果表中有記錄則退出）。
3．REPLACE 先清空表在加載數(shù)據(jù)。
4． RECLEN 用于兩種情況，1）SQLLDR不能自動計算記錄長度，2）或用戶想看壞文件的完整記錄時。對于后一種，Oracle只能按常規(guī)把壞記錄部分寫到錯誤的地方。如果看整條記錄，則可以將整條記錄寫到壞文件中。
3）指定最大的記錄長度：
1． CONCATENATE 允許用戶設(shè)定一個整數(shù)，表示要組合邏輯記錄的數(shù)目。
4）建立邏輯記錄：
1．THIS 檢查當(dāng)前記錄條件，如果為真則連接下一個記錄。
2．NEXT 檢查下一個記錄條件。如果為真，則連接下一個記錄到當(dāng)前記錄來。
2． Start: end 表示要檢查在THIS或NEXT字串是否存在繼續(xù)串的列，以確定是否進(jìn)行連接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
5）指定要加載的表：
1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE類似。用來檢查記錄的情況，如：when(3-5)='SSM' and (22)='*"
6）介紹并括起記錄中的字段：
1． FIELDS給出記錄中字段的分隔符，F(xiàn)IELDS格式為：
FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
TERMINATED 讀完前一個字段即開始讀下一個字段直到介紹。
WHITESPACE 是指結(jié)束符是空格的意思。包括空格、Tab、換行符、換頁符及回車符。如果是要判斷但字符，可以用單引號括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示數(shù)據(jù)應(yīng)由特殊字符括起來。也可以括在TERMINATED字符內(nèi)。使用OPTIONALLY要同時用TERMINLATED。
ENCLOSED 指兩個分界符內(nèi)的數(shù)據(jù)。如果同時用 ENCLOSED和TERMINAED ，則它們的順序決定計算的順序。
7）定義列：
column 是表列名。列的取值可以是：
BECHUM 表示邏輯記錄數(shù)。第一個記錄為1,第2個記錄為2。
CONSTANT 表示賦予常數(shù)。
SEQUENCE 表示序列可以從任意序號開始，格式為：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 給出列在邏輯記錄中的位置。可以是絕對的，或相對前一列的值。格式為：
POSITION ( {start[end] | * [+integer] } )
Start 開始位置
* 表示前字段之后立刻開始。
+ 從前列開始向后條的位置數(shù)。
8）定義數(shù)據(jù)類型：
可以定義14種數(shù)據(jù)類型：
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC
1．字符類型數(shù)據(jù)
CHAR[ (length)] [delimiter]
length缺省為 1.
2.日期類型數(shù)據(jù)
DATE [ ( length)]['date_format' [delimiter]
使用to_date函數(shù)來限制。
3．字符格式中的十進(jìn)制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常規(guī)格式的十進(jìn)制數(shù)（不是二進(jìn)制=> 一個位等于一個bit）。
4．壓縮十進(jìn)制格式數(shù)據(jù)
DECIMAL （digtial [,precision]）
5．雙精度符點(diǎn)二進(jìn)制
DOUBLE
6．普通符點(diǎn)二進(jìn)制
FLOAT
7．字符格式符點(diǎn)數(shù)
FLOAT EXTERNAL [ (length) ] [delimiter]
8．雙字節(jié)字符串?dāng)?shù)據(jù)
GRAPHIC [ (legth)]
9．雙字節(jié)字符串?dāng)?shù)據(jù)
GRAPHIC EXTERNAL[ (legth)]
10．常規(guī)全字二進(jìn)制整數(shù)
INTEGER
11．字符格式整數(shù)
INTEGER EXTERNAL
12．常規(guī)全字二進(jìn)制數(shù)據(jù)
SMALLINT
13．可變長度字符串
VARCHAR
14．可變雙字節(jié)字符串?dāng)?shù)據(jù)
VARGRAPHIC
2.2寫控制文件CTL
1． 各數(shù)據(jù)文件的文件名;
2．各數(shù)據(jù)文件格式;
3．各數(shù)據(jù)文件里各數(shù)據(jù)記錄字段的屬性；
4．接受數(shù)據(jù)的ORACLE表列的屬性；
5．?dāng)?shù)據(jù)定義；
6．其它
數(shù)據(jù)文件的要求：
數(shù)據(jù)類型的指定
CHAR 字符型
INTEGER EXTERNAL 整型
DECIMAL EXTERNAL 浮點(diǎn)型
3.1數(shù)據(jù)文件的內(nèi)容
可以在OS下的一個文件；或跟在控制文件下的具體數(shù)據(jù)。數(shù)據(jù)文件可以是：
1、 二進(jìn)制與字符格式：LOADER可以把二進(jìn)制文件讀（當(dāng)成字符讀）列表中
2、 固定格式：記錄中的數(shù)據(jù)、數(shù)據(jù)類型、 數(shù)據(jù)長度固定。
3、 可變格式：每個記錄至少有一個可變長數(shù)據(jù)字段，一個記錄可以是一個連續(xù)的字符串。
數(shù)據(jù)段的分界（如姓名、年齡）如用“，”作字段的 分 ；用，"’作數(shù)據(jù)
括號等
4、 LOADER可以使用多個連續(xù)字段的物理記錄組成一個邏輯記錄，記錄文件運(yùn)行情況文件：包括以下內(nèi)容：
1、 運(yùn)行日期：軟件版本號
2、 全部輸入，輸出文件名；對命令行的展示信息，補(bǔ)充信息，
3、 對每個裝入信息報告：如表名，裝入情況；對初始裝入， 加截入或更新裝
入的選擇情況，欄信息
4、 數(shù)據(jù)錯誤報告：錯誤碼；放棄記錄報告
5、 每個裝X報告：裝入行；裝入行數(shù)，可能跳過行數(shù)；可能拒絕行數(shù)；可能放
棄行數(shù)等
6、 統(tǒng)計概要：使用空間（包大小，長度）；讀入記錄數(shù)，裝入記錄數(shù)，跳過記
錄數(shù)；拒絕記錄數(shù)，放棄記錄數(shù)；運(yùn)行時間等。
==========================================================================================================
sql load的一點(diǎn)小總結(jié)
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要導(dǎo)入的文件
// INFILE 'tt.date' // 導(dǎo)入多個文件
// INFILE * // 要導(dǎo)入的內(nèi)容就在control文件里 下面的BEGINDATA后面就是導(dǎo)入的內(nèi)容
INTO TABLE table_name // 指定裝入的表
BADFILE 'c:bad.txt' // 指定壞文件地址
************* 以下是4種裝入表的方式
APPEND // 原先的表有數(shù)據(jù) 就加在后面
// INSERT // 裝載空表 如果原先的表有數(shù)據(jù) sqlloader會停止 默認(rèn)值
// REPLACE // 原先的表有數(shù)據(jù) 原先的數(shù)據(jù)會全部刪除
// TRUNCATE // 指定的內(nèi)容和replace的相同 會用truncate語句刪除現(xiàn)存數(shù)據(jù)
************* 指定的TERMINATED可以在表的開頭 也可在表的內(nèi)部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 裝載這種數(shù)據(jù): 10,lg,"""lg""","lg,lg"
// 在表中結(jié)果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六進(jìn)制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 裝載這種數(shù)據(jù): 10 lg lg
TRAILING NULLCOLS ************* 表的字段沒有對應(yīng)的值時允許為空
************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 關(guān)鍵字 此列的數(shù)值不會被裝載
// 如: lg,lg,not 結(jié)果 lg lg
)
// 當(dāng)沒聲明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'
// )
// 當(dāng)沒聲明FIELDS TERMINATED BY ','用位置告訴字段裝載數(shù)據(jù)
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個字段的開始位置在前一字段的結(jié)束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的類型
// )
BEGINDATA // 對應(yīng)開始的 INFILE * 要導(dǎo)入的內(nèi)容就在control文件里
10,Sql,what
20,lg,show
=====================================================================================
//////////// 注意begindata后的數(shù)值前面不能有空格
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), // 這個字段的開始位置在前一字段的結(jié)束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5 ***** 使用函數(shù) 日期的一種表達(dá) TRAILING NULLCOLS的使用
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS // 其實(shí)下面的ENTIRE_LINE在BEGINDATA后面的數(shù)據(jù)中是沒有直接對應(yīng)
// 的列的值的 如果第一行改為 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", // 使用函數(shù)
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一種表達(dá)方式 還有'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 ***** 使用自定義的函數(shù) // 解決的時間問題
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 )" // 使用自定義的函數(shù)
)
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 // 通過關(guān)鍵字concatenate 把幾行的記錄看成一行記錄
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, // 其實(shí)這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 表示從第一行開始 并在第一行結(jié)束 還有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 ***** 載入有換行符的數(shù)據(jù)
注意: 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 的使用幫助轉(zhuǎn)換換行符
)
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
// 這樣裝載會把換行符裝入數(shù)據(jù)庫 下面的方法就不會 但要求數(shù)據(jù)的格式不同
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個字節(jié)表示記錄的長度 如第一個記錄的 071 表示此記錄有 71 個字節(jié)
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屬性
// 最靈活的一中 可定義一個新的行結(jié)尾符 win 回車換行 : chr(13)||chr(10)
此列中記錄是以 a|rn 結(jié)束的
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
結(jié)果 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|
==============================================================================
象這樣的數(shù)據(jù) 用 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
id position(1:3) nullif id=blanks // 這里可以是blanks 或者別的表達(dá)式
// 下面是另一個列子 第一行的 1 在數(shù)據(jù)庫中將成為 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
------------------------------------------------------------
如果是英文的日志 格式，可能需要修改環(huán)境變量 nls_lang or nls_date_format
==========================================================================================================
Oracle SQL*Loader 使用指南（轉(zhuǎn)載）
SQL*Loader是Oracle數(shù)據(jù)庫導(dǎo)入外部數(shù)據(jù)的一個工具.它和DB2的Load工具相似,但有更多的選擇，它支持變化的加載模式，可選的加載及多表加載.
如何使用 SQL*Loader 工具
我們可以用Oracle的sqlldr工具來導(dǎo)入數(shù)據(jù)。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 將加載一個外部數(shù)據(jù)文件（含分隔符). loader.ctl如下:
load data
infile 'c:datamydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一個指定記錄長度的示例控制文件。"*" 代表數(shù)據(jù)文件與此文件同名，即在后面使用BEGINDATA段來標(biāo)識數(shù)據(jù)。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader這樣的工具
Oracle 沒有提供將數(shù)據(jù)導(dǎo)出到一個文件的工具。但是，我們可以用SQL*Plus的select 及 format 數(shù)據(jù)來輸出到一個文件：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
另外，也可以使用使用 UTL_FILE PL/SQL 包處理:
rem Remember to update initSID.ora, utl_file_dir='c:oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
當(dāng)然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
加載可變長度或指定長度的記錄
如：
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是導(dǎo)入固定位置（固定長度）數(shù)據(jù)示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳過數(shù)據(jù)行：
可以用 "SKIP n" 關(guān)鍵字來指定導(dǎo)入時可以跳過多少行數(shù)據(jù)。如：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
導(dǎo)入數(shù)據(jù)時修改數(shù)據(jù)：
在導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫時，可以修改數(shù)據(jù)。注意，這僅適合于常規(guī)導(dǎo)入，并不適合 direct導(dǎo)入方式.如：
LOAD DATA
INFILE *
INTO TABLE modified_data
( 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
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
)
將數(shù)據(jù)導(dǎo)入多個表：
如:
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
)
導(dǎo)入選定的記錄：
如下例： (01) 代表第一個字符, (30:37) 代表30到37之間的字符:
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) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
導(dǎo)入時跳過某些字段：
可用 POSTION(x:y) 來分隔數(shù)據(jù). 在Oracle8i中可以通過指定 FILLER 字段實(shí)現(xiàn)。FILLER 字段用來跳過、忽略導(dǎo)入數(shù)據(jù)文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
導(dǎo)入多行記錄：
可以使用下面兩個選項(xiàng)之一來實(shí)現(xiàn)將多行數(shù)據(jù)導(dǎo)入為一個記錄:
CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.
SQL*Loader 數(shù)據(jù)的提交：
一般情況下是在導(dǎo)入數(shù)據(jù)文件數(shù)據(jù)后提交的。
也可以通過指定 ROWS= 參數(shù)來指定每次提交記錄數(shù)。
提高 SQL*Loader 的性能：
1) 一個簡單而容易忽略的問題是，沒有對導(dǎo)入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=參數(shù)時，會很明顯降低數(shù)據(jù)庫導(dǎo)入性能。
2) 可以添加 DIRECT=TRUE來提高導(dǎo)入數(shù)據(jù)的性能。當(dāng)然，在很多情況下，不能使用此參數(shù)。
3) 通過指定 UNRECOVERABLE選項(xiàng)，可以關(guān)閉數(shù)據(jù)庫的日志。這個選項(xiàng)只能和 direct 一起使用。
4) 可以同時運(yùn)行多個導(dǎo)入任務(wù).
常規(guī)導(dǎo)入與direct導(dǎo)入方式的區(qū)別：
常規(guī)導(dǎo)入可以通過使用 INSERT語句來導(dǎo)入數(shù)據(jù)。Direct導(dǎo)入可以跳過數(shù)據(jù)庫的相關(guān)邏輯(DIRECT=TRUE)，而直接將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)文件中。
posted on 2010-03-24 21:04
xzc 閱讀(17822)
評論(2) 編輯 收藏 所屬分類:
Oracle