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 recordCONTINUEIF 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)而直接将数据导入到数据文件中
