當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle sqlldr详解,sqlldr详解

發(fā)布時間：2025/3/15 编程问答 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle sqlldr详解,sqlldr详解小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

Oracle 的SQL*LOADER可以將外部數(shù)據(jù)加載到數(shù)據(jù)庫表中。下面是SQL*LOADER的基本特點：

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給出記錄中字段的分隔符，FIELDS格式為：

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．雙精度符點二進(jìn)制

DOUBLE

6．普通符點二進(jìn)制

FLOAT

7．字符格式符點數(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．數(shù)據(jù)定義；

6．其它

數(shù)據(jù)文件的要求：

數(shù)據(jù)類型的指定

CHAR 字符型

INTEGER EXTERNAL 整型

DECIMAL EXTERNAL 浮點型

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的一點小總結(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 // 其實下面的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

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, // 其實這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 ...

總結(jié)

以上是生活随笔為你收集整理的oracle sqlldr详解,sqlldr详解的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。