SQLLoader的使用方法

本篇內(nèi)容介紹了“SQL Loader的使用方法”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

成都創(chuàng)新互聯(lián)專注于札達(dá)企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站,商城網(wǎng)站建設(shè)。札達(dá)網(wǎng)站建設(shè)公司,為札達(dá)等地區(qū)提供建站服務(wù)。全流程按需求定制制作，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

Oracle 的SQL*LOADER可以將外部數(shù)據(jù)加載到數(shù)據(jù)庫(kù)表中。下面是SQL*LOADER的基本特點(diǎn)：
1）能裝入不同數(shù)據(jù)類型文件及多個(gè)數(shù)據(jù)文件的數(shù)據(jù)
2）可裝入固定格式，自由定界以及可度長(zhǎng)格式的數(shù)據(jù)
3）可以裝入二進(jìn)制，壓縮十進(jìn)制數(shù)據(jù)
4）一次可對(duì)多個(gè)表裝入數(shù)據(jù)
5）連接多個(gè)物理記錄裝到一個(gè)記錄中
6）對(duì)一單記錄分解再裝入到表中
7）可以用 數(shù)對(duì)制定列生成唯一的KEY
8）可對(duì)磁盤或 磁帶數(shù)據(jù)文件裝入制表中
9）提供裝入錯(cuò)誤報(bào)告
10）可以將文件中的整型字符串，自動(dòng)轉(zhuǎn)成壓縮十進(jìn)制并裝入列表中。
1.2控制文件
控制文件是用一種語(yǔ)言寫的文本文件，這個(gè)文本文件能被SQL*LOADER識(shí)別。SQL*LOADER根據(jù)控制文件可以找到需要加載的數(shù)據(jù)。并且分析和解釋這些數(shù)據(jù)。控制文件由三個(gè)部分組成：
l 全局選件，行，跳過(guò)的記錄數(shù)等；
l INFILE子句指定的輸入數(shù)據(jù)；
l 數(shù)據(jù)特性說(shuō)明。
1.3輸入文件
對(duì)于 SQL*Loader, 除控制文件外就是輸入數(shù)據(jù)。SQL*Loader可從一個(gè)或多個(gè)指定的文件中讀出數(shù)據(jù)。如果數(shù)據(jù)是在控制文件中指定，就要在控制文件中寫成 INFILE * 格式。當(dāng)數(shù)據(jù)固定的格式（長(zhǎng)度一樣）時(shí)且是在文件中得到時(shí)，要用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ù)是可變格式（長(zhǎng)度不一樣）時(shí)且是在文件中得到時(shí)，要用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ù)來(lái)給定。
1.5日志文件及日志信息
當(dāng)SQL*Loader 開(kāi)始執(zhí)行后，它就自動(dòng)建立 日志文件。日志文件包含有加載的總結(jié)，加載中的錯(cuò)誤信息等。
控制文件語(yǔ)法
控制文件的格式如下：
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 后可以跟幾個(gè)文件。
2．STRAM 表示一次讀一個(gè)字節(jié)的數(shù)據(jù)。新行代表新物理記錄（邏輯記錄可由幾個(gè)物理記錄組成）。
3．RECORD 使用宿主操作系統(tǒng)文件及記錄管理系統(tǒng)。如果數(shù)據(jù)在控制文件中則使用這種方法。
3． FIXED length 要讀的記錄長(zhǎng)度為length字節(jié)，
4． VARIABLE 被讀的記錄中前兩個(gè)字節(jié)包含的長(zhǎng)度，length 記錄可能的長(zhǎng)度。缺傷為8k字節(jié)。
5． BADFILE和BADDN同義。Oracle 不能加載數(shù)據(jù)到數(shù)據(jù)庫(kù)的那些記錄。
6． DISCARDFILE和DISCARDDN是同義詞。記錄沒(méi)有通過(guò)的數(shù)據(jù)。
7． DISCARDS和DISCARDMAX是同義詞。Integer 為最大放棄的文件個(gè)數(shù)。
2）加載的方法：
1．APPEND 給表添加行。
2．INSERT 給空表增加行（如果表中有記錄則退出）。
3．REPLACE 先清空表在加載數(shù)據(jù)。
4． RECLEN 用于兩種情況，1）SQLLDR不能自動(dòng)計(jì)算記錄長(zhǎng)度，2）或用戶想看壞文件的完整記錄時(shí)。對(duì)于后一種，Oracle只能按常規(guī)把壞記錄部分寫到錯(cuò)誤的地方。如果看整條記錄，則可以將整條記錄寫到壞文件中。
3）指定最大的記錄長(zhǎng)度：
1． CONCATENATE 允許用戶設(shè)定一個(gè)整數(shù)，表示要組合邏輯記錄的數(shù)目。
4）建立邏輯記錄：
1．THIS 檢查當(dāng)前記錄條件，如果為真則連接下一個(gè)記錄。
2．NEXT 檢查下一個(gè)記錄條件。如果為真，則連接下一個(gè)記錄到當(dāng)前記錄來(lái)。
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類似。用來(lái)檢查記錄的情況，如：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 讀完前一個(gè)字段即開(kāi)始讀下一個(gè)字段直到介紹。
WHITESPACE 是指結(jié)束符是空格的意思。包括空格、Tab、換行符、換頁(yè)符及回車符。如果是要判斷但字符，可以用單引號(hào)括起，如X'1B'等。
OPTIONALLY ENCLOSED 表示數(shù)據(jù)應(yīng)由特殊字符括起來(lái)。也可以括在TERMINATED字符內(nèi)。使用OPTIONALLY要同時(shí)用TERMINLATED。
ENCLOSED 指兩個(gè)分界符內(nèi)的數(shù)據(jù)。如果同時(shí)用 ENCLOSED和TERMINAED ，則它們的順序決定計(jì)算的順序。
7）定義列：
column 是表列名。列的取值可以是：
BECHUM 表示邏輯記錄數(shù)。第一個(gè)記錄為1,第2個(gè)記錄為2。
CONSTANT 表示賦予常數(shù)。
SEQUENCE 表示序列可以從任意序號(hào)開(kāi)始，格式為：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 給出列在邏輯記錄中的位置?？梢允墙^對(duì)的，或相對(duì)前一列的值。格式為：
POSITION ( {start[end] | * [+integer] } )
Start 開(kāi)始位置
* 表示前字段之后立刻開(kāi)始。
+ 從前列開(kāi)始向后條的位置數(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ù)來(lái)限制。
3．字符格式中的十進(jìn)制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常規(guī)格式的十進(jìn)制數(shù)（不是二進(jìn)制=> 一個(gè)位等于一個(gè)bit）。
4．壓縮十進(jìn)制格式數(shù)據(jù)
DECIMAL （digtial [,divcision]）
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．可變長(zhǎng)度字符串
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下的一個(gè)文件；或跟在控制文件下的具體數(shù)據(jù)。數(shù)據(jù)文件可以是：
1、 二進(jìn)制與字符格式：LOADER可以把二進(jìn)制文件讀（當(dāng)成字符讀）列表中
2、 固定格式：記錄中的數(shù)據(jù)、數(shù)據(jù)類型、 數(shù)據(jù)長(zhǎng)度固定。
3、 可變格式：每個(gè)記錄至少有一個(gè)可變長(zhǎng)數(shù)據(jù)字段，一個(gè)記錄可以是一個(gè)連續(xù)的字符串。
數(shù)據(jù)段的分界（如姓名、年齡）如用“，”作字段的 分 ；用，"’作數(shù)據(jù)
括號(hào)等
4、 LOADER可以使用多個(gè)連續(xù)字段的物理記錄組成一個(gè)邏輯記錄，記錄文件運(yùn)行情況文件：包括以下內(nèi)容：
1、 運(yùn)行日期：軟件版本號(hào)
2、 全部輸入，輸出文件名；對(duì)命令行的展示信息，補(bǔ)充信息，
3、 對(duì)每個(gè)裝入信息報(bào)告：如表名，裝入情況；對(duì)初始裝入， 加截入或更新裝
入的選擇情況，欄信息
4、 數(shù)據(jù)錯(cuò)誤報(bào)告：錯(cuò)誤碼；放棄記錄報(bào)告
5、 每個(gè)裝X報(bào)告：裝入行；裝入行數(shù)，可能跳過(guò)行數(shù)；可能拒絕行數(shù)；可能放
棄行數(shù)等
6、 統(tǒng)計(jì)概要：使用空間（包大小，長(zhǎng)度）；讀入記錄數(shù)，裝入記錄數(shù)，跳過(guò)記
錄數(shù)；拒絕記錄數(shù)，放棄記錄數(shù)；運(yùn)行時(shí)間等。
==========================================================================================================
sql load的一點(diǎn)小總結(jié)
 
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' // 要導(dǎo)入的文件
// INFILE 'tt.date' // 導(dǎo)入多個(gè)文件
// 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會(huì)停止 默認(rèn)值
// REPLACE // 原先的表有數(shù)據(jù) 原先的數(shù)據(jù)會(huì)全部刪除
// TRUNCATE // 指定的內(nèi)容和replace的相同 會(huì)用truncate語(yǔ)句刪除現(xiàn)存數(shù)據(jù)
 
************* 指定的TERMINATED可以在表的開(kāi)頭 也可在表的內(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 ************* 表的字段沒(méi)有對(duì)應(yīng)的值時(shí)允許為空
 
************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 關(guān)鍵字 此列的數(shù)值不會(huì)被裝載
// 如: lg,lg,not 結(jié)果 lg lg
)
// 當(dāng)沒(méi)聲明FIELDS TERMINATED BY ',' 時(shí)
// (
// 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)沒(méi)聲明FIELDS TERMINATED BY ','用位置告訴字段裝載數(shù)據(jù)
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 這個(gè)字段的開(kāi)始位置在前一字段的結(jié)束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的類型
// )
 
BEGINDATA // 對(duì)應(yīng)開(kāi)始的 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" 將不會(huì)被裝載
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), // 這個(gè)字段的開(kāi)始位置在前一字段的結(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ù)中是沒(méi)有直接對(duì)應(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ù) // 解決的時(shí)間問(wèn)題
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 hh34: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ò)關(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在每行的末尾找逗號(hào) 找到逗號(hào)就把下一行附加到上一行
 
LOAD DATA
INFILE *
continueif this(1:1) = '-' // 找每行的開(kāi)始是否有連接字符 - 有就把下一行連接為一行
// 如 -10,Sales,Virginia,
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
// 其中1:1 表示從第一行開(kāi)始 并在第一行結(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 ***** 載入每行的行號(hào)
 
load data
infile *
into table t
replace
( seqno RECNUM //載入每行的行號(hào)
text Position(1:1024))
BEGINDATA
fsdfasj //自動(dòng)分配一行號(hào)給載入 表t 的seqno字段 此行為 1
fasdjfasdfl // 此行為 2 ...
 
9 ***** 載入有換行符的數(shù)據(jù)
注意: unix 和 windows 不同& /n
< 1 > 使用一個(gè)非換行符的字符
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 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
 
// 這樣裝載會(huì)把換行符裝入 下面的方法就不會(huì) 但要求數(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 告訴每個(gè)記錄的前3個(gè)字節(jié)表示記錄的長(zhǎng)度 如第一個(gè)記錄的 071 表示此記錄有 71 個(gè)字節(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屬性
// 最靈活的一中 可定義一個(gè)新的行結(jié)尾符 win 回車換行 : chr(13)||chr(10)
 
此列中記錄是以 a|\r\n 結(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-200009945Sdivad over three meals.
 
id position(1:3) nullif id=blanks // 這里可以是blanks 或者別的表達(dá)式
// 下面是另一個(gè)列子 第一行的 1 在數(shù)據(jù)庫(kù)中將成為 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
==========================================================================================================
SQL*Loader 使用指南（轉(zhuǎn)載）
SQL*Loader是Oracle數(shù)據(jù)庫(kù)導(dǎo)入外部數(shù)據(jù)的一個(gè)工具.它和的Load工具相似,但有更多的選擇，它支持變化的加載模式，可選的加載及多表加載.
如何使用 SQL*Loader 工具
我們可以用Oracle的sqlldr工具來(lái)導(dǎo)入數(shù)據(jù)。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 將加載一個(gè)外部數(shù)據(jù)文件（含分隔符). loader.ctl如下:
load data
infile 'c:\data\mydata.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
下面是一個(gè)指定記錄長(zhǎng)度的示例控制文件。"*" 代表數(shù)據(jù)文件與此文件同名，即在后面使用BEGINDATA段來(lái)標(biāo)識(shí)數(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 沒(méi)有提供將數(shù)據(jù)導(dǎo)出到一個(gè)文件的工具。但是，我們可以用SQL*Plus的select 及 format 數(shù)據(jù)來(lái)輸出到一個(gè)文件：
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, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
當(dāng)然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
加載可變長(zhǎng)度或指定長(zhǎng)度的記錄
如：
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)入固定位置（固定長(zhǎng)度）數(shù)據(jù)示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
跳過(guò)數(shù)據(jù)行：
可以用 "SKIP n" 關(guān)鍵字來(lái)指定導(dǎo)入時(shí)可以跳過(guò)多少行數(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í)修改數(shù)據(jù)：
在導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫(kù)時(shí)，可以修改數(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)入多個(gè)表：
如:
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) 代表第一個(gè)字符, (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)入時(shí)跳過(guò)某些字段：
可用 POSTION(x:y) 來(lái)分隔數(shù)據(jù). 在Oracle8i中可以通過(guò)指定 FILLER 字段實(shí)現(xiàn)。FILLER 字段用來(lái)跳過(guò)、忽略導(dǎo)入數(shù)據(jù)文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
導(dǎo)入多行記錄：
可以使用下面兩個(gè)選項(xiàng)之一來(lái)實(shí)現(xiàn)將多行數(shù)據(jù)導(dǎo)入為一個(gè)記錄:
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ù)后提交的。
也可以通過(guò)指定 ROWS= 參數(shù)來(lái)指定每次提交記錄數(shù)。
提高 SQL*Loader 的性能：
1) 一個(gè)簡(jiǎn)單而容易忽略的問(wèn)題是，沒(méi)有對(duì)導(dǎo)入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=參數(shù)時(shí)，會(huì)很明顯降低數(shù)據(jù)庫(kù)導(dǎo)入性能。
2) 可以添加 DIRECT=TRUE來(lái)提高導(dǎo)入數(shù)據(jù)的性能。當(dāng)然，在很多情況下，不能使用此參數(shù)。
3) 通過(guò)指定 UNRECOVERABLE選項(xiàng)，可以關(guān)閉數(shù)據(jù)庫(kù)的日志。這個(gè)選項(xiàng)只能和 direct 一起使用。
4) 可以同時(shí)運(yùn)行多個(gè)導(dǎo)入任務(wù).
常規(guī)導(dǎo)入與direct導(dǎo)入方式的區(qū)別：
常規(guī)導(dǎo)入可以通過(guò)使用 INSERT語(yǔ)句來(lái)導(dǎo)入數(shù)據(jù)。Direct導(dǎo)入可以跳過(guò)數(shù)據(jù)庫(kù)的相關(guān)邏輯(DIRECT=TRUE)，而直接將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)文件中。

“SQL Loader的使用方法”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！

網(wǎng)站名稱：SQLLoader的使用方法
文章起源：http://muchs.cn/article26/ihjjjg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站營(yíng)銷、搜索引擎優(yōu)化、服務(wù)器托管、網(wǎng)站設(shè)計(jì)公司、網(wǎng)站策劃、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)