SQL*Loader是Oracle數據庫導入外部數據的一個工具它和DB的Load工具相似但有更多的選擇它支持變化的加載模式可選的加載及多表加載
如何使用 SQL*Loader 工具
我們可以用Oracle的sqlldr工具來導入數據例如:
sqlldr scott/tiger control=loaderctl
控制文件(loaderctl) 將加載一個外部數據文件（含分隔符)loaderctl如下:
load data
infile c:\data\mydatacsv
into table emp
fields terminated by optionally enclosed by
( empno empname sal deptno )
mydatacsv 如下:
Scott Tiger
Frank Naude
下面是一個指定記錄長度的示例控制文件* 代表數據文件與此文件同名即在後面使用BEGINDATA段來標識數據
load data
infile *
replace
into table departments
( dept position (:) char()
deptname position (:) char()
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader這樣的工具
Oracle 沒有提供將數據導出到一個文件的工具但是我們可以用SQL*Plus的select 及 format 數據來輸出到一個文件
set echo off newpage space pagesize
feed off head off trimspool on
spool oradatatxt
select col || || col || || col
from tab
where col = XYZ;
spool off
另外也可以使用使用 UTL_FILE PL/SQL 包處理:
rem Remember to update initSIDora
utl_file_dir=c:\oradata parameter
declare
fp utl_filefile_type;
begin
fp := utl_filefopen(c:\oradatatabtxtw);
utl_fileputf(fp %s %s\n TextField );
utl_filefclose(fp);
end;
/
當然你也可以使用第三方工具如SQLWays TOAD for Quest等
加載可變長度或指定長度的記錄
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
TRAILING NULLCOLS
( data
data
)
BEGINDATA
AAAAAAAAAA
ABCD
下面是導入固定位置（固定長度）數據示例
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data POSITION(:)
data POSITION(:)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
跳過數據行可以用 SKIP n關鍵字來指定導入時可以跳過多少行數據如
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP
( data POSITION(:)
data POSITION(:)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
導入數據時修改數據
在導入數據到數據庫時可以修改數據注意這僅適合於常規導入並不適合 direct導入方式如
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no my_db_sequencenextval
region CONSTANT
time_loaded to_char(SYSDATE HH:MI)
data POSITION(:) :data/
data POSITION(:) upper(:data)
data POSITION(:)to_date(:data YYMMDD)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
LOAD DATA
INFILE mail_orderstxt
BADFILE bad_orderstxt
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
)
將數據導入多個表
如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno !=
( empno POSITION(:) INTEGER EXTERNAL
ename POSITION(:) CHAR
deptno POSITION(:) CHAR
mgr POSITION(:) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno !=
( projno POSITION(:) INTEGER EXTERNAL
empno POSITION(:) INTEGER EXTERNAL
)
導入選定的記錄
如下例 () 代表第一個字符 (:) 代表到之間的字符:
LOAD DATA
INFILE mydatadat BADFILE mydatabad
DISCARDFILE mydatadis
APPEND
INTO TABLE my_selective_table
WHEN () <>; H and () <>; T
and (:) =
(
region CONSTANT
service_key POSITION(:) INTEGER EXTERNAL
call_b_no POSITION(:) CHAR
)
導入時跳過某些字段
可用 POSTION(x:y) 來分隔數據在Oraclei中可以通過指定FILLER字段實現FILLER 字段用來跳過忽略導入數據文件中的字段如
LOAD DATA
TRUNCATE INTO TABLE T
FIELDS TERMINATED BY
( field
field FILLER
field
)
導入多行記錄
可以使用下面兩個選項之一來實現將多行數據導入為一個記錄:
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
oneEgby having a # character in column
SQL*Loader 數據的提交
一般情況下是在導入數據文件數據後提交的也可以通過指定 ROWS= 參數來指定每次提交記錄數
提高 SQL*Loader 的性能
() 一個簡單而容易忽略的問題是沒有對導入的表使用任何索引和/或約束(主鍵)如果這樣做甚至在使用ROWS=參數時會很明顯降低數據庫導入性能
() 可以添加 DIRECT=TRUE來提高導入數據的性能當然在很多情況下不能使用此參數
() 通過指定 UNRECOVERABLE選項可以關閉數據庫的日志這個選項只能和 direct 一起使用
() 可以同時運行多個導入任務
常規導入與direct導入方式的區別
常規導入可以通過使用 INSERT語句來導入數據Direct導入可以跳過數據庫的相關邏輯(DIRECT=TRUE)而直接將數據導入到數據文件中
From:http://tw.wingwit.com/Article/program/Oracle/201311/18244.html
