我们可以用Oracle的sqlldr工具来导入数据：
Sqlldr userid=lgone/tiger control=a.ctl着重关注以下几个参数：
userid
control -- 控制文件，可能包含表的数据
log
bad
data
errors
rows
skip
有两种使用方法：
（1）使用一个控制文件(作为模板) 和一个数据文件
一般为了利于模板和数据的分离，以及程序的不同分工会使用第一种方式,下面是一个比较完整的例子：
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE "users_data.csv" --指定外部数据文件，可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
// INFILE 'tt.date' // 导入多个文件
--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，
truncate --操作类型，用 truncate table 来清除表中原有记录
INTO TABLE users -- 要插入记录的表
Fields terminated by "," -- 数据中每行记录用 "," 分隔
Optionally enclosed by '"' -- 数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时
trailing nullcols --表的字段没有对应的值时允许为空
(
virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号
user_id number, --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示
user_name,
login_times,
last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换
)
其中append为数据装载方式，还有其他选项：
a、insert，为缺省方式，在数据装载开始时要求表为空
b、append，在表中追加新记录
c、replace，删除旧记录(用 delete from table 语句)，替换成新装载的记录
d、truncate，删除旧记录(用 truncate table 语句)，替换成新装载的记录
（2）只使用一个控制文件，在这个控制文件中包含数据
把 users_data.cvs 中的内容补到 users.ctl 中，并以 BEGINDATA 连接，还要把 INFILE "users_data.csv" 改为 INFILE *。同时为了更大化的说明问题，把数据处理了一下。此时，完整的 users.ctl 文件内容是：
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE * -- 因为数据同控制文件在一起，所以用 * 表示
append -- 这里用了 append 来操作，在表 users 中附加记录
INTO TABLE users
when LOGIN_TIMES<>'8' -- 还可以用 when 子句选择导入符合条件的记录
Fields terminated by ","
trailing nullcols
(
virtual_column FILLER, --跳过由 PL/SQL Developer 生成的第一列序号
user_id "user_seq.nextval", --这一列直接取序列的下一值，而不用数据中提供的值
user_name "'Hi '||upper(:user_name)",--,还能用SQL函数或运算对数据进行加工处理
login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符
last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这里开始
,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄莺,8,2009-1-2
4,4,Kypfos,NULL,NULL
5,5,不知秋,1,2008-12-23
逐一举例说明：
1、普通装载
注：BEGINDATA后的数值前面不能有空格
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia --loc 列将为空
60,"Finance",,Virginia --loc 列将为空
2、TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
注：x'09'表示字符ASCII码的16进制数值，为tab分隔符
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ：装载这种数据: 10,lg,"""lg""","lg,lg"
TERMINATED BY WRITESPACE：装载这种数据: 10 lg lg
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
--FIELDS TERMINATED BY x'20'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3、指定不装载那一列
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
FILLER_1 FILLER, --下面的 "Something Not To Be Loaded" 将不会被装载
DNAME,
LOC
)
BEGINDATA
20,Something Not To Be Loaded,Accounting,"Virginia,USA"
4、position：当没声明FIELDS TERMINATED BY ',' 时 ，用位置告诉字段装载数据
结果：10 | Accounting Vir | ginia,USA | 10Accounting Virginia,USA
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position(1:2),
DNAME position(*:16), --这个字段的开始位置在前一字段的结束位置
LOC position(*:29),
ENTIRE_LINE position(1:29)
)
BEGINDATA
10Accounting Virginia,USA
5、使用函数日期的一种表达TRAILING NULLCOLS的使用
注：可以通过:dname类型调用函数，特别注意date函数的使用。
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
--这句的意思是将没有对应值的列都置为null
--如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
(DEPTNO,
DNAME "upper(:dname)", --使用函数
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', --日期的一种表达方式。还有'dd-mon-yyyy'等
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2000
20,Accounting,Virginia,21/6/1999
30,Consulting,Virginia,5/1/2000
40,Finance,Virginia,15/3/2001
6、合并多行记录为一行记录
注：例如有些文本文件以N行为一循环记录数据，则可以这样导入。
LOAD DATA
INFILE *
concatenate 3 --通过关键字concatenate 把几行的记录看成一行记录
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,
Virginia,
1/5/2000
7、使用continueif来合并记录行
上例可直接使用continueif last= ','来告诉Oracle如果前一个数据以','结尾，则这个附加到上一行
注：ContinueIf还可以使用this或next选项
LOAD DATA
INFILE *
continueif last= ','
INTO TABLE DEPT
replace
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,
Virginia,
1/5/2000
8、载入每行的行号，关键字：RECNUM
LOAD DATA
INFILE *
INTO TABLE DEPT
replace
(DEPTNO RECNUM //载入每行的行号
ENTIRE_LINE Position(1:1024)
)
BEGINDATA
fsdfasj --自动分配行号到DEPTNO字段，此行为1
fasdjfasdfl --自动递增，此行为2
9、载入有换行符的数据
①使用一个非换行符的字符
注：换行的特殊字符如果使用'\n'，则会在Windows编译过程中直接换成换行符，导致无法转换
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )",
COMMENTS "replace(:comments,'%%',chr(10))" --用replace函数转换成换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This is the Sales%%Office in Virginia
20,Accounting,Virginia,13/04/2001,This is the Accounting%%Office in Virginia
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting%%Office in Virginia
40,Finance,Virginia,987268297,This is the Finance%%Office in Virginia
②使用fix属性： fix告诉每次取一行的范围， 然后，通过看这一行相应的分隔符去分开对应字段中的数据
注：fix只能加在外部文件数据导入时，另外需要每行数据长度都相等。
Load DATA
INFILE demo1.dat "fix 68"
INTO TABLE t1_a
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED Date 'dd/mm/yyyy',
ENTIRE_LINE
)
-------------------------------------------------
demo1.dat
----------
10,aaaab,Virginia,01/05/2001,This is the aaaab
Office in Virginia
20,aaaac,Virginia,13/04/2001,This is the aaaac
Office in Virginia
30,aaaad,Virginia,14/04/2001,This is the aaaad
Office in Virginia
40,aaaae,Virginia,16/02/2001,This is the aaaae
Office in Virginia
③ 使用var属性：其实就是每次开头告诉sqlldr每行变化取多少位，相当于每次都指定个fix多少（由前面的数据决定位数）的值。这也就是变长的做法。
Load DATA
INFILE demo17.dat "var 3"
INTO TABLE t1_a
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED Date 'dd/mm/yyyy',
ENTIRE_LINE
)
----------------------------------------------
demo17.dat
----------
03510,Sales,Virginia,01/01/2001,This
03920,Accounting,Virginia,13/04/2001,Thi
04530,Consulting,Virginia,14/04/2001,This is t
07140,Finance,Virginia,14/04/2001,This is the Finance Office
in Virginia
注：var 3 表示前三位用于说明该条记录的长度 (但是谁告诉我长度怎么数的？-_-|||)
④使用str属性
可使用str来定义一个行结尾符
计算以|\r\n 结束的值：
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
结果：7C0D0A
注意：同样需要在外部文件数据导入中使用，且最后一个不用加；另外注意不要有空格
LOAD DATA
INFILE demo17.dat "str X'7C0D0A'"
INTO TABLE t1_a
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED Date 'dd/mm/yyyy',
ENTIRE_LINE
)
-----------------------------------------------
demo17.dat
----------
10,Sales,Virginia,01/01/2001,This is the Sales
Office in Virginia|
20,Accounting,Virginia,13/04/2001,This is the Accounting
Office in Virginia|
30,Consulting,Virginia,14/04/2001,This is the Consulting
Office in Virginia|
40,Finance,Virginia,14/04/2002,This is the Finance
Office in Virginia
10、nullif导入
注：需要注意的是在前面指定的数据类型以及后面的引号！
LOAD DATA
INFILE *
INTO TABLE t1_a
REPLACE
(DEPTNO position(1:2) integer external nullif DEPTNO='1',
-- 当导入deotno的值为'1'时，则该条记录不导入
DNAME position(3:8)
)
BEGINDATA
1 10
20lg
11、使用自定义的函数。以为是解决的时间问题的例子：
create or replace
my_to_date( p_string in varchar2 ) return date
as
type fmtArray is table of varchar2(25);
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyy hh24:mi:ss' );
l_return date;
begin
for i in 1 .. l_fmts.count
loop
begin
l_return := to_date( p_string, l_fmts(i) );
exception
when others then null;
end;
EXIT when l_return is not null;
end loop;
if ( l_return is null )
then
l_return :=
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
p_string, 'GMT', 'EST' );
end if;
return l_return;
end;
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
)
BEGINDATA
10,Sales,Virginia,01-april-2001
20,Accounting,Virginia,13/04/2001
30,Consulting,Virginia,14/04/2001 12:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not a date
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
