目标:
1.整理控制文件的常用规则
2.导入测试文件
控制文件
[oracle@localhost
sqlldr]$ cat url.ctl
load data
infile ‘url.tsv’
into table url
Fields terminated by
“\t”
trailing nullcols
(path,sort,url
)
表：
SQL> desc url
Name
Null? Type
—————————————–
——– —————————-
PATH
VARCHAR2(256)
SORT
VARCHAR2(256)
URL
VARCHAR2(256)
导入命令
[oracle@localhost
sqlldr]$ sqlldr userid=temp/temp control=url.ctl log=url.log
日志：
[oracle@localhost
sqlldr]$ cat url.log
SQL*Loader: Release
11.2.0.1.0 – Production on 星期五 7月 15 10:19:51 2011
Copyright (c) 1982,
2009, Oracle and/or its affiliates. All
rights reserved.
Control File: url.ctl
Data File: url.tsv
Bad File:
url.bad
Discard File:
none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of bytes
Continuation: none specified
Path used: Conventional
Table URL, loaded
from every logical record.
Insert option in
effect for this table: INSERT
TRAILING NULLCOLS
option in effect
Column Name Position Len
Term Encl Datatype
——————————
———- —– —- —- ———————
PATH FIRST *
WHT CHARACTER
SORT NEXT *
WHT CHARACTER
URL NEXT *
WHT CHARACTER
Table URL:
Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses
were failed.
0 Rows not loaded because all fields were
null.
Space allocated for
bind array: 49536
bytes(64 rows)
Read buffer bytes:
Total logical
records skipped: 0
Total logical
records read:
Total logical
records rejected: 0
Total logical
records discarded: 0
Run began on 星期五
7月 15 10:19:51 2011
Run ended on 星期五
7月 15 10:20:12 2011
Elapsed time
was: 00:00:20.84
CPU time was: 00:00:03.81
3.其它思考
类型
时间字符串导入日期类型
[oracle@localhost
date]$ cat date.ctl
load
data
infile
‘date.tsv’
into
table i_date append
Fields
terminated by “\t”
trailing
nullcols
(i_date
date ‘yy-mm-dd hh24:mi:ss’
)
序列
带序列的控制文件
[oracle@localhost
testseq]$ cat testseq.ctl
OPTIONS
(skip=1)
load
data
infile
‘testseq.tsv’
into
table testseq
Fields
terminated by “\t”
trailing
nullcols
(name,addr,id
“testseq$seq.nextval”
)
常用规则：
OPTIONS
(skip=1)忽略第一行
virtual_column
FILLER, –这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号
LOAD
DATA
INFILE ‘t.dat’ //要导入的文件
// INFILE ‘tt.date’ //导入多个文件
// INFILE * //要导入的内容就在control文件里下面的BEGINDATA后面就是导入的内容, *和’t.dat’不能同时存在
INTO TABLE table_name //指定装入的表
BADFILE ‘c:bad.txt’ //指定坏文件地址
*以下是4种装入表的方式
APPEND //原先的表有数据就加在后面
// INSERT //装载空表如果原先的表有数据sqlloader会停止默认值
// REPLACE //原先的表有数据原先的数据会全部删除
// TRUNCATE //指定的内容和replace的相同会用truncate语句删除现存数据
*指定的TERMINATED可以在表的开头也可在表的内部字段部分
FIELDS
TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
//装载这种数据:
10,lg,”””lg”””,”lg,lg”
//在表中结果: 10 lg “lg” lg,lg
// TERMINATED BY X ’09’ //以十六进制格式’09’表示的
// TERMINATED BY WRITESPACE //装载这种数据: 10 lg lg
TRAILING NULLCOLS *表的字段没有对应的值时允许为空
*下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER关键字此列的数值不会被装载
//如: lg,lg,not结果lg lg
)
//当没声明FIELDS TERMINATED BY ‘,’时
// (
// col_1
[interger external] TERMINATED BY ‘,’ ,
// col_2
[date “dd-mon-yyy”] TERMINATED BY ‘,’ ,
// col_3
[char] TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘lg’
// )
//当没声明FIELDS TERMINATED BY ‘,’用位置告诉字段装载数据
// (
// col_1
position(1:2),
// col_2
position(3:10),
// col_3 position(*:16), //这个字段的开始位置在前一字段的结束位置
// col_4
position(1:16),
// col_5 position(3:10) char(8) //指定字段的类型
// )
BEGINDATA //对应开始的INFILE *要导入的内容就在control文件里
10,Sql,what
20,lg,show
=====================================================================================
注意begindata后的数值前面不能有空格
1 *普通装载
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 * FIELDS TERMINATED BY WHITESPACE和FIELDS TERMINATED BY x’09’的情况
LOAD
DATA
INFILE *
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY WHITESPACE
—
FIELDS TERMINATED BY x’09’
(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的列子
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的使用
LOAD
DATA
INFILE *
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY ‘,’
TRAILING NULLCOLS //其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
//的列的值的如果第一行改为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 *使用自定义的函数//解决的时间问题
create
or replace
function
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(‘0′,’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,
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Not
a date
7 *合并多行记录为一行记录
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, //其实这3行看成一行10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
//这列子用continueif list=”,”也可以
告诉sqlldr在每行的末尾找逗号找到逗号就把下一行附加到上一行
LOAD
DATA
INFILE *
continueif this(1:1) = ‘-‘ //找每行的开始是否有连接字符-有就把下一行连接为一行
//如-10,Sales,Virginia,
// 1/5/2000就是一行10,Sales,Virginia,1/5/2000
//其中1:1表示从第一行开始并在第一行结束还有continueif next但continueif list最理想
INTO
TABLE DEPT
replace
FIELDS
TERMINATED BY ‘,’
(DEPTNO,
DNAME
“upper(:dname)”,
LOC
“upper(:loc)”,
LAST_UPDATED
date ‘dd/mm/yyyy’
)
BEGINDATA //但是好象不能象右面的那样使用
-10,Sales,Virginia,
-10,Sales,Virginia,
1/5/2000
1/5/2000
-40,
40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001
8 *载入每行的行号
load
data
infile *
into
table t
replace
( seqno RECNUM //载入每行的行号
text
Position(1:1024))
BEGINDATA
fsdfasj //自动分配一行号给载入表t的seqno字段此行为1
fasdjfasdfl //此行为2 …
9 *载入有换行符的数据
注意: unix和windows不同& /n
< 1 >使用一个非换行符的字符
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,’n’,chr(10))” // replace的使用帮助转换换行符
)
BEGINDATA
10,Sales,Virginia,01-april-2001,This
is the SalesnOffice in Virginia
20,Accounting,Virginia,13/04/2001,This
is the AccountingnOffice in Virginia
30,Consulting,Virginia,14/04/2001
12:02:02,This is the ConsultingnOffice in Virginia
40,Finance,Virginia,,This
is the FinancenOffice in Virginia
< 2 >使用fix属性
LOAD
DATA
INFILE
demo17.dat “fix 101”
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY ‘,’
TRAILING
NULLCOLS
(DEPTNO,
DNAME
“upper(:dname)”,
LOC
“upper(:loc)”,
LAST_UPDATED
“my_to_date( :last_updated )”,
COMMENTS
)
demo17.dat
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,,This
is the Finance
Office
in Virginia
//这样装载会把换行符装入数据库下面的方法就不会但要求数据的格式不同
LOAD
DATA
INFILE
demo18.dat “fix 101”
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING
NULLCOLS
(DEPTNO,
DNAME
“upper(:dname)”,
LOC
“upper(:loc)”,
LAST_UPDATED
“my_to_date( :last_updated )”,
COMMENTS
)
demo18.dat
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,,”This
is the Finance
Office
in Virginia”
< 3 >使用var属性
LOAD
DATA
INFILE
demo19.dat “var 3”
// 3告诉每个记录的前3个字节表示记录的长度如第一个记录的071表示此记录有71个字节
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY ‘,’
TRAILING
NULLCOLS
(DEPTNO,
DNAME
“upper(:dname)”,
LOC
“upper(:loc)”,
LAST_UPDATED
“my_to_date( :last_updated )”,
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,This
is the Sales
Office
in Virginia
07820,Accounting,Virginia,13/04/2001,This
is the Accounting
Office
in Virginia
08730,Consulting,Virginia,14/04/2001
12:02:02,This is the Consulting
Office
in Virginia
07140,Finance,Virginia,,This
is the Finance
Office
in Virginia
< 4 >使用str属性
//最灵活的一中可定义一个新的行结尾符win回车换行: chr(13)||chr(10)
此列中记录是以a|rn结束的
select
utl_raw.cast_to_raw(‘|’||chr(13)||chr(10)) from dual;
结果7C0D0A
LOAD
DATA
INFILE
demo20.dat “str X’7C0D0A'”
INTO
TABLE DEPT
REPLACE
FIELDS
TERMINATED BY ‘,’
TRAILING
NULLCOLS
(DEPTNO,
DNAME
“upper(:dname)”,
LOC
“upper(:loc)”,
LAST_UPDATED
“my_to_date( :last_updated )”,
COMMENTS
)
demo20.dat
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,,This
is the Finance
Office
in Virginia|
==============================================================================
象这样的数据用nullif子句
10-jan-Flipper
seemed unusually hungry today.
10510-jan-Spread
over three meals.
id position(1:3) nullif id=blanks //这里可以是blanks或者别的表达式
//下面是另一个列子第一行的1在数据库中将成为null
LOAD
DATA
INFILE *
INTO
TABLE T
REPLACE
(n
position(1:2) integer external nullif n=’1′,
v
position(3:8)
)
BEGINDATA
1 10
20lg
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/227485.html原文链接：https://javaforall.net
