Oracle SQL*Loader 使用指南（第一部分）
保存这篇文章有段时间了，感觉很实用，这里感谢原作者：Angel.John
SQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载.
如何使用 SQL*Loader 工具
我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件（含分隔符). loader.ctl如下:
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一个指定记录长度的示例控制文件。"*" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件：
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
另外，也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
加载可变长度或指定长度的记录
如：
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( data1,
data2
)
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是导入固定位置（固定长度）数据示例：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
固定程度数据load 可以通过position 来指定
例子：
LOAD DATA
INFILE 'zipcodes.dat'
REPLACE INTO TABLE zipcodes (
city_name POSITION(1:10) CHAR(10),
zip_code POSITION(*:15) CHAR,
state_abbr POSITION(17-18) CHAR
)
city_name POSITION(1:10) CHAR(10),
The city name begins at position 1 and goes through position 10. In this case,
the length was specified redundantly in the datatype specification. A colon
was used to separate the beginning and ending values
zip_code POSITION(*:15) CHAR,
The * indicates that the ZIP Code begins with the first byte following the city
name. The ending position has been hardcoded as the 15th byte. A colon has
been used to separate the two values. No length has been specified with the
datatype, so SQL*Loader will compute the length as ending – beginning + 1.
state_abbr POSITION(17–18) CHAR
The state abbreviation has been specified in absolute terms. This time, a
hyphen has been used to separate the two values. Again, no length has been
specified with the datatype, so SQL*Loader will derive the length based on the
beginning and ending values.
跳过数据行：
可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP 5
( data1 POSITION(1:5),
data2 POSITION(6:15)
)
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
导入数据时修改数据：
在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 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
)
将数据导入多个表：
如:
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
)
导入选定的记录：
如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
导入时跳过某些字段：
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)
导入多行记录：
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
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.
增加些新内容：
1： 当出现物理折行的时候可以使用 CONTINUEIF LAST 来“合并物理行”
LOAD DATA
INFILE 'data15.dat'
REPLACE CONTINUEIF LAST = ','
INTO TABLE michigan_features
(
feature_name CHAR TERMINATED BY ',' ENCLOSED BY '"',
feature_type CHAR TERMINATED BY ',' ENCLOSED BY '"',
county CHAR TERMINATED BY ',' ENCLOSED BY '"',
latitude CHAR TERMINATED BY ',' ENCLOSED BY '"',
longitude CHAR TERMINATED BY ',' ENCLOSED BY '"',
elevation INTEGER EXTERNAL TERMINATED BY ',' ENCLOSED BY '"'
)
Begindata
"Grace Harbor","bay","Keweenaw","475215N",
"0891330W","601"
"Minong Ridge","ridge","Keweenaw","480115N","0885348W","800"
"Siskiwit Lake",
"lake",
"Keweenaw",
"480002N",
"0884745W",
"659"
例子：
SQL> conn hr/hr;
已连接。
SQL> create table michigan_features(feature_name varchar2(100),
2 feature_type varchar2(100),
3 county varchar2(100),
4 latitude varchar2(100),
5 longitude varchar2(100),
6 elevation number);
表已创建。
SQL> host sqlldr hr/hr control=c:\data\loader.ctl log=c:\data\load.log;
SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 8月 12 16:18:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 5
SQL> select * from michigan_features;
FEATURE_NAME
---------------------------------------------------------------------------
Grace Harbor
Minong Ridge
Siskiwit Lake
2： 对记录字段记录进行拆分：
LOAD DATA
INFILE *
REPLACE INTO TABLE michigan_features
(
feature_name CHAR TERMINATED BY ',',
feature_type CHAR TERMINATED BY ',',
county CHAR TERMINATED BY ',"',
lat_degrees INTEGER EXTERNAL(2),
lat_minutes INTEGER EXTERNAL(2),
lat_seconds INTEGER EXTERNAL(2),
lat_direction CHAR TERMINATED BY '","',
long_degrees INTEGER EXTERNAL(3),
long_minutes INTEGER EXTERNAL(2),
long_seconds INTEGER EXTERNAL(2),
long_direction CHAR TERMINATED BY '"'
)
Begindata
"Wagner Falls","falls","Alger","462316N","0863846W"
"Tannery Falls","falls","Alger","462456N","0863737W"
例子：
SQL> conn hr/hr
已连接。
SQL> set wrap off
SQL> create table michigan_features(
2 feature_name varchar2(100),
3 feature_type varchar2(100),
4 county varchar2(100),
5 lat_degrees number,
6 lat_minutes number,
7 lat_seconds number,
8 lat_direction varchar2(100),
9 long_degrees number,
10 long_minutes number,
11 long_seconds number,
12 long_direction varchar2(100));
表已创建。
SQL> host sqlldr hr/hr control=c:\data\loader.ctl log=c:\data\load.log;
SQL*Loader: Release 10.2.0.1.0 - Production on 星期二 8月 12 16:42:57 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 2
SQL> select * from michigan_features;
行将被截断
FEATURE_NAME
--------------------------------------------------------------------------------
"Wagner Falls"
"Tannery Falls"
3，使用sql 表达式， 在这个例子中使用了to_number 函数， 同样可以使用自定义的函数
LOAD DATA
INFILE *
REPLACE INTO TABLE book
fields terminated by "," optionally enclosed by '"'
(
book_title ,
book_price
"GREATEST(TO_NUMBER(:book_price)/100 * TO_NUMBER(:book_pages*0.10))",
book_pages
)
Begindata
Oracle Essentials ,3495,355
SQL*Plus: The Definitive Guide ,3995,502
Oracle PL/SQL Programming ,4495,87
Oracle8 Design Tips ,1495,115
例子：
SQL> create table BOOK
2 (
3 BOOK_ID NUMBER,
4 BOOK_TITLE VARCHAR2(35),
5 BOOK_PRICE NUMBER,
6 BOOK_PAGES NUMBER
7 );
表已创建。
SQL> sqlldr hr/hr control =c:\data\loader.ctl log=c:\data\log.log
SP2-0734: 未知的命令开头 "sqlldr hr/..." - 忽略了剩余的行。
SQL> host sqlldr hr/hr control =c:\data\loader.ctl log=c:\data\log.log;
SQL*Loader: Release 10.2.0.1.0 - Production on 星期四 8月 14 09:19:03 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
达到提交点 - 逻辑记录计数 3
达到提交点 - 逻辑记录计数 4
SQL> select * from book;
BOOK_ID BOOK_TITLE BOOK_PRICE BOOK_PAGES
---------- ----------------------------------- ---------- ----------
Oracle Essentials 1240.725 355
SQL*Plus: The Definitive Guide 2005.49 502
Oracle PL/SQL Programming 391.065 87
Oracle8 Design Tips 171.925 115
SQL>
4，字符串转换：
The following LOAD statement provides an example of how to specify a character
set. In this case, the character set is an EBCDIC character set named
WE8EBCDIC37C. Look for the CHARACTERSET clause in the second line of the
LOAD command:
LOAD DATA
CHARACTERSET 'WE8EBCDIC37C'
INFILE 'book_prices.dat'
REPLACE INTO TABLE book
(
book_title POSITION(1) CHAR(35),
book_price POSITION(37)
"GREATEST(TO_NUMBER(:book_price)/100,
TO_NUMBER(:book_pages*0.10))",
book_pages POSITION(42) INTEGER EXTERNAL(3),
book_
由于权限不够，这里将本文分两部分发出，后续请看--Oracle SQL*Loader 使用指南（第二部分）