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 one Eg by having a # character in column
SQL*Loader 数据的提交
一般情况下是在导入数据文件数据后提交的
也可以通过指定 ROWS= 参数来指定每次提交记录数
提高 SQL*Loader的性能
) 一个简单而容易忽略的问题是没有对导入的表使用任何索引和/或约束(主键)如果这样做甚至在使用ROWS=参数时会很明显降低数据库导入性能
) 可以添加 DIRECT=TRUE来提高导入数据的性能当然在很多情况下不能使用此参数
) 通过指定UNRECOVERABLE选项可以关闭数据库的日志这个选项只能和 direct 一起使用
) 可以同时运行多个导入任务
常规导入与direct导入方式的区别
常规导入可以通过使用 INSERT语句来导入数据Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)而直接将数据导入到数据文件中
