sqlldr的用法總結

sqlldr userid=lgone/tiger control=a.ctl
其中，a.ctl為控制檔案，內容：
　　LOAD DATA －－控制檔案標識
　　INFILE 't.dat' －－ 要匯入的檔案
　　INFILE 'tt.dat' －－ 匯入多個檔案
　　INFILE * －－ "*" 代表要匯入的內容就在control檔案裡，下面的BEGINDATA後面就是匯入的內容
　　INSERT －－資料載入方式，有四種
INTO TABLE table_name －－ 指定裝入的表
　　BADFILE 'c:bad.txt' －－指定壞檔案地址
LOGFILE 'c:log.txt' －－log檔案
DISCARD 'c:discard.txt' －－包含丟棄資料的檔案
ERRORS=1000 －－指定錯誤的數目

可以使用sqlldr檢視幫助。

[@more@]

以下是4種裝入表的方式：
　　 APPEND 原先的表有資料，就加在後面
　　 INSERT 裝載空表 如果原先的表有資料 sqlloader會停止，預設值
　　 REPLACE 原先的表有資料，原先的資料會全部刪除
　　 TRUNCATE 指定的內容和replace的相同 會用truncate語句刪除現存資料
　　
指定的TERMINATED可以在表的開頭，也可在表的內部欄位部分：
　　FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' －－OPTIONALLY ENCLOSED BY是指欄位值用什麼括起來，平時我們都是用單引號，如'value'
　　 裝載這種資料: 10,lg,"""lg""","lg,lg"
　　 在表中結果: 10 lg "lg" lg,lg
　　 TERMINATED BY X'09' －－ 表示欄位之間透過製表符tab鍵（以十六進位制格式 '09' 表示 ）分割，也可以把FIELDS TERMINATED BY ';'分號表示成ascii編碼
　　 TERMINATED BY WRITESPACE －－ 以空白分割，裝載這種資料: 10 lg lg

　　 (col_1 [interger external] TERMINATED BY ',' ,
　　 col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
　　 col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' )

當某列為空，不存在對應值：　　
　　 TRAILING NULLCOLS －－如果某個欄位沒有對應的值時（空格、空白、或null），被當作null列，向表中插入null
　　
不匯入某列：
　　( col_1 , col_2 ,col_filler FILLER －－FILLER 關鍵字（不是filter）指定此列（第三列）的數值不會被裝載，如: lg,lg,not，結果 lg lg)
也可以寫成這樣：( col_1 , col_2 ) －－資料檔案中存在超過兩列資料，但是隻匯入前兩列
　　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 列將為空

跳過資料行，可以用 "SKIP n" 關鍵字來指定匯入時可以跳過多少行資料。如：
INTO TABLE load_positional_data
SKIP 5
　　　
當沒宣告FIELDS TERMINATED BY ','用位置告訴欄位裝載資料：
　　 (
　　 col_1 position(1:2), －－1到2，(01) 代表第一個字元
　　 col_2 position(3:10), －－3到10
　　 col_3 position(*:16), －－ 這個欄位的開始位置在前一欄位的結束位置
　　 col_4 position(1:16),
　　 col_5 position(3:10) char(8) －－ 指定欄位的型別
　　 )
　　
　　BEGINDATA －－ 對應開始的 INFILE * 要匯入的內容就在control檔案裡，注意begindata後的數值前面不能有空格
　　10,Sql,what
　　20,lg,show
　　
使用函式（包括自定義函式）、日期的某種表達方式：
　　FIELDS TERMINATED BY ','
　　(DEPTNO,
　　DNAME "upper(:dname)", －－ 使用函式
　　LOC "upper(:loc)",
　　LAST_UPDATED date 'dd/mm/yyyy', －－或'dd-month-yyyy'等，也可以使用自定義函式LAST_UPDATED "my_to_date( :last_updated )" ， my_to_date()為自定義函式
　　ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
　　)

載入有換行符的資料：
　　注意: unix 和 windows換行符不同 & /n
(......
　　COMMENTS "replace(:comments,'n',chr(10))" --replace 的使用幫助轉換換行符
......
)

載入每行的行號:
　　......
　　( seqno RECNUM －－載入每行的行號
　　text Position(1:20))
　　BEGINDATA
　　fsdfasj －－自動分配一行號給載入 表t 的seqno欄位, 此行行號為1
　　fasdjfasdfl －－此行行號為2
　　
合併多行記錄為一行記錄:
　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.

LOAD DATA
　　INFILE *
　　concatenate 3 －－透過關鍵字concatenate 把幾行的記錄看成一行記錄
　　INTO TABLE DEPT
......
　　(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最理想

將資料匯入多個表：
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
)

匯入資料時修改資料：
在匯入資料到資料庫時，可以修改資料。注意，這僅適合於常規匯入，並不適合 direct匯入方式.
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
)

SQL*Loader 資料的提交：

一般情況下是在匯入資料檔案資料後提交的。也可以透過指定 ROWS= 引數來指定每次提交記錄數。

提高 SQL*Loader　的效能：
1) 一個簡單而容易忽略的問題是，沒有對匯入的表使用任何索引和/或約束(主鍵)。如果這樣做，甚至在使用ROWS=引數時，會很明顯降低資料庫匯入效能。
2) 可以新增 DIRECT=TRUE來提高匯入資料的效能。當然，在很多情況下，不能使用此引數。
3) 透過指定　UNRECOVERABLE選項，可以關閉資料庫的日誌。這個選項只能和 direct 一起使用。
4) 可以同時執行多個匯入任務.

常規匯入與direct匯入方式的區別：
常規匯入可以透過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中。