( 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_di |