oracle 导入

sqlldr详解

 

Oracle SQL*LOADER可以将外部数据加载到数据库表中。下面是SQL*LOADER的基本特点:
1
)能装入不同数据类型文件及多个数据文件的数据
2
)可装入固定格式,自由定界以及可度长格式的数据
3
)可以装入二进制,压缩十进制数据
4
)一次可对多个表装入数据
5
)连接多个物理记录装到一个记录中
6
)对一单记录分解再装入到表中
7
)可以用 数对制定列生成唯一的KEY
8
)可对磁盘或 磁带数据文件装入制表中
9
)提供装入错误报告
10
)可以将文件中的整型字符串,自动转成压缩十进制并装入列表中。

1.2控制文件

控制文件是用一种语言写的文本文件,这个文本文件能被SQL*LOADER识别。SQL*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成:
l
全局选件,行,跳过的记录数等;
l INFILE
子句指定的输入数据;
l
数据特性说明。

1.3输入文件

对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。如果 数据是在控制文件中指定,就要在控制文件中写成 INFILE * 格式。当数据固定的格式(长度一样)时且是在文件中得到时,要用INFILE "fix n"
load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,
当数据是可变格式(长度不一样)时且是在文件中得到时,要用INFILE "var n"。如:
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,

1.4坏文件

坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。
坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。
1.5
日志文件及日志信息
SQL*Loader 开始执行后,它就自动建立 日志文件。日志文件包含有加载的总结,加载中的错误信息等。
控制文件语法
控制文件的格式如下:
OPTIONS
{ [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]
1
)要加载的数据文件:
1
INFILE INDDN是同义词,它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件。
2
STRAM 表示一次读一个字节的数据。新行代表新物理记录(逻辑记录可由几个物理记录组成)。
3
RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
3
FIXED length 要读的记录长度为length字节,
4
VARIABLE 被读的记录中前两个字节包含的长度,length 记录可能的长度。缺伤为8k字节。
5
BADFILEBADDN同义。Oracle 不能加载数据到数据库的那些记录。
6
DISCARDFILEDISCARDDN是同义词。记录没有通过的数据。
7
DISCARDSDISCARDMAX是同义词。Integer 为最大放弃的文件个数。
2
)加载的方法:
1
APPEND 给表添加行。
2
INSERT 给空表增加行(如果表中有记录则退出)。
3
REPLACE 先清空表在加载数据。
4
RECLEN 用于两种情况,1SQLLDR不能自动计算记录长度,2)或用户想看坏文件的完整记录时。对于后一种,Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录,则可以将整条记录写到坏文件中。
3
)指定最大的记录长度:
1
CONCATENATE 允许用户设定一个整数,表示要组合逻辑记录的数目。
4
)建立逻辑记录:
1
THIS 检查当前记录条件,如果为真则连接下一个记录。
2
NEXT 检查下一个记录条件。如果为真,则连接下一个记录到当前记录来。
2
Start: end 表示要检查在THISNEXT字串是否存在继续串的列,以确定是否进行连接。如:continueif next(1-3)='WAG' continueif next(1-3)=X'0d03if'
5
)指定要加载的表:

1INTO TABLE 要加的表名。
2
WHEN select WHERE类似。用来检查记录的情况,如:when(3-5)='SSM' and (22)='*"
6
)介绍并括起记录中的字段:
1
FIELDS给出记录中字段的分隔符,FIELDS格式为:
FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]
[ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]
TERMINATED
读完前一个字段即开始读下一个字段直到介绍。
WHITESPACE
是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符,可以用单引号括起,如X'1B'等。
OPTIONALLY ENCLOSED
表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED
ENCLOSED
指两个分界符内的数据。如果同时用 ENCLOSEDTERMINAED ,则它们的顺序决定计算的顺序。
7
)定义列:
column
是表列名。列的取值可以是:
BECHUM
表示逻辑记录数。第一个记录为1,2个记录为2
CONSTANT
表示赋予常数。
SEQUENCE
表示序列可以从任意序号开始,格式为:
SEQUENCE
{ integer | MAX |COUNT} [,increment]
POSITION
给出列在逻辑记录中的位置。可以是绝对的,或相对前一列的值。格式为:
POSITION ( {start[end] | * [+integer] } )
Start
开始位置
*
表示前字段之后立刻开始。
+
从前列开始向后条的位置数。
8
)定义数据类型:
可以定义14种数据类型:
CHAR
DATE
DECIMAL EXTERNAL
DECIMAL
DOUBLE
FLOAT
FLOAT EXTERNAL
GRAPHIC EXTERNAL
INTEGER
INTEGER EXTERNAL
SMALLINT
VARCHAR
VARGRAPHIC
1
.字符类型数据
CHAR[ (length)] [delimiter]
length
缺省为 1.
2.
日期类型数据
DATE [ ( length)]['date_format' [delimiter]
使用to_date函数来限制。
3
.字符格式中的十进制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常规格式的十进制数(不是二进制=> 一个位等于一个bit)。
4
.压缩十进制格式数据
DECIMAL
digtial [,precision]
5
.双精度符点二进制
DOUBLE
6
.普通符点二进制
FLOAT
7
.字符格式符点数
FLOAT EXTERNAL [ (length) ] [delimiter]
8
.双字节字符串数据
GRAPHIC [ (legth)]
9
.双字节字符串数据
GRAPHIC EXTERNAL[ (legth)]
10
.常规全字二进制整数
INTEGER
11
.字符格式整数
INTEGER EXTERNAL
12
.常规全字二进制数据
SMALLINT
13
.可变长度字符串
VARCHAR
14
.可变双字节字符串数据
VARGRAPHIC

2.2写控制文件CTL
1
各数据文件的文件名;
2
.各数据文件格式;
3
.各数据文件里各数据记录字段的属性;
4
.接受数据的ORACLE表列的属性;
5
.数据定义;
6
.其它
数据文件的要求:
数据类型的指定
CHAR
字符型
INTEGER EXTERNAL
整型
DECIMAL EXTERNAL
浮点型
3.1
数据文件的内容
可以在OS下的一个文件;或跟在控制文件下的具体数据。数据文件可以是:
1
二进制与字符格式:LOADER可以把二进制文件读(当成字符读)列表中
2
固定格式:记录中的数据、数据类型、 数据长度固定。
3
可变格式:每个记录至少有一个可变长数据字段,一个记录可以是一个连续的字符串。
数据段的分界(如姓名、年龄)如用作字段的 ;用,"’作数据
括号等
4
LOADER可以使用多个连续字段的物理记录组成一个逻辑记录,记录文件运行情况文件:包括以下内容:
1
运行日期:软件版本号
2
全部输入,输出文件名;对命令行的展示信息,补充信息,
3
对每个装入信息报告:如表名,装入情况;对初始装入, 加截入或更新装
入的选择情况,栏信息
4
数据错误报告:错误码;放弃记录报告
5
每个装X报告:装入行;装入行数,可能跳过行数;可能拒绝行数;可能放
弃行数等
6
统计概要:使用空间(包大小,长度);读入记录数,装入记录数,跳过记
录数;拒绝记录数,放弃记录数;运行时间等。
==========================================================================================================
sql load
的一点小总结
sqlldr userid=lgone/tiger control=a.ctl
LOAD DATA
INFILE 't.dat' //
要导入的文件
// INFILE 'tt.date' //
导入多个文件
// INFILE * //
要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容
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_LINEBEGINDATA后面的数据中是没有直接对应
//
的列的值的 如果第一行改为 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('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
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 & /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,987268297,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,987268297,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,987268297,"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,987268297,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,987268297,This is the Finance
Office in Virginia|
==============================================================================
象这样的数据 nullif 子句
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread 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
------------------------------------------------------------
如果是英文的日志 格式,可能需要修改环境变量 nls_lang or nls_date_format
==========================================================================================================
Oracle SQL*Loader
使用指南(转载)
SQL*Loader
Oracle数据库导入外部数据的一个工具.它和DB2Load工具相似,但有更多的选择,它支持变化的加载模式,可选的加载及多表加载.
如何使用 SQL*Loader 工具
我们可以用Oraclesqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl) 将加载一个外部数据文件(含分隔符). loader.ctl如下:
load data
infile 'c:datamydata.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*Plusselect 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, %sn', '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
跳过数据行:
可以用 "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) 代表3037之间的字符:
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.
SQL*Loader
数据的提交:
一般情况下是在导入数据文件数据后提交的。
也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQL*Loader 的性能:
1)
一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。
2)
可以添加 DIRECT=TRUE来提高导入数据的性能。当然,在很多情况下,不能使用此参数。
3)
通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和 direct 一起使用。
4)
可以同时运行多个导入任务.
常规导入与direct导入方式的区别:
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中。

 

 

浅谈ORACLESQLLDR

 

一:sql loader 的特点
oracle
自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。
比如说expimp可以对数据库中的数据进行导出和导出的工作,是一种很好的数据库备份和恢复的工具,因此主要用在数据库的热备份和恢复方面。有着速度快,使用简单,快捷的优点;同时也有一些缺点,比如在不同版本数据库之间的导出、导入的过程之中,总会出现这样或者那样的问题,这个也许是oracle公司自己产品的兼容性的问题吧。
sql loader
工具却没有这方面的问题,它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。

二:sql loader 的帮助

 

三:sql loader使用例子
a
SQLLoader Excel 数据导出到 Oracle
1.
创建SQL*Loader输入数据所需要的文件,均保存到C:/,用记事本编辑:

控制文件:input.ctl,内容如下:

   load data           --1、控制文件标识
   infile 'test.txt'       --2、要输入的数据文件名为test.txt
   append into table test    --3、向表test中追加记录

   fields terminated by X'09'  --4、字段终止于X'09',是一个制表符(TAB
   (id,username,password,sj)   -----定义列对应顺序

ainsert,为缺省方式,在数据装载开始时要求表为空
b
append,在表中追加新记录
c
replace,删除旧记录,替换成新装载的记录
d
truncate,同上

DOS窗口下使用SQL*Loader命令实现数据的输入

C:/>sqlldr userid=system/manager@orcl control=input.ctl
默认日志文件名为:input.log
默认坏记录文件为:input.bad

2.还有一种方法
可以把EXCEL文件另存为CSV(逗号分隔)(*.csv),控制文件就改为用逗号分隔
LOAD DATA
INFILE 'd:/car.csv'
APPEND INTO TABLE t_car_temp
FIELDS TERMINATED BY ","
(phoneno,vip_car)

b)在控制文件中直接导入数据

1、控制文件test.ctl的内容
-- The format for executing this file with SQL Loader is:
-- SQLLDR control=<filename> Be sure to substitute your
-- version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
BADFILE 'C:/Documents and Settings/Jackey/
桌面/WMCOUNTRY.BAD'
DISCARDFILE 'C:/Documents and Settings/Jackey/
桌面
/WMCOUNTRY.DSC'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
COUNTRYID NULLIF (COUNTRYID="NULL"),
COUNTRYCODE,
COUNTRYNAME,
CONTINENTID NULLIF (CONTINENTID="NULL"),
MAPID NULLIF (MAPID="NULL"),
CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL
3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL
4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL
5;"CA";"Canada";4;13;"09/16/2004 16:31:32";NULL
6;"US";"United States";4;14;"09/16/2004 16:31:32";NULL
7;"MX";"Mexico";4;15;"09/16/2004 16:31:32";NULL
8;"GB";"United Kingdom";3;16;"09/16/2004 16:31:32";NULL
9;"DE";"Germany";3;17;"09/16/2004 16:31:32";NULL
10;"FR";"France";3;18;"09/16/2004 16:31:32";NULL
11;"IT";"Italy";3;19;"09/16/2004 16:31:32";NULL
12;"ES";"Spain";3;20;"09/16/2004 16:31:32";NULL
13;"FI";"Finland";3;21;"09/16/2004 16:31:32";NULL
14;"SE";"Sweden";3;22;"09/16/2004 16:31:32";NULL
15;"IE";"Ireland";3;23;"09/16/2004 16:31:32";NULL
16;"NL";"Netherlands";3;24;"09/16/2004 16:31:32";NULL
17;"DK";"Denmark";3;25;"09/16/2004 16:31:32";NULL
18;"BR";"Brazil";5;85;"09/30/2004 11:25:43";NULL
19;"KR";"Korea, Republic of";1;88;"09/30/2004 11:25:43";NULL
20;"NZ";"New Zealand";6;89;"09/30/2004 11:25:43";NULL
21;"BE";"Belgium";3;79;"09/30/2004 11:25:43";NULL
22;"AT";"Austria";3;78;"09/30/2004 11:25:43";NULL
23;"NO";"Norway";3;82;"09/30/2004 11:25:43";NULL
24;"LU";"Luxembourg";3;81;"09/30/2004 11:25:43";NULL
25;"PT";"Portugal";3;83;"09/30/2004 11:25:43";NULL
26;"GR";"Greece";3;80;"09/30/2004 11:25:43";NULL
27;"IL";"Israel";1;86;"09/30/2004 11:25:43";NULL
28;"CH";"Switzerland";3;84;"09/30/2004 11:25:43";NULL
29;"A1";"Anonymous Proxy";0;0;"09/30/2004 11:25:43";NULL
30;"A2";"Satellite Provider";0;0;"09/30/2004 11:25:43";NULL
31;"AD";"Andorra";3;0;"09/30/2004 11:25:43";NULL
32;"AE";"United Arab Emirates";1;0;"09/30/2004 11:25:43";NULL
33;"AF";"Afghanistan";1;0;"09/30/2004 11:25:43";NULL
34;"AG";"Antigua and Barbuda";7;0;"09/30/2004 11:25:43";NULL
35;"AI";"Anguilla";7;0;"09/30/2004 11:25:43";NULL
36;"AL";"Albania";3;0;"09/30/2004 11:25:43";NULL
37;"AM";"Armenia";3;0;"09/30/2004 11:25:43";NULL
38;"AN";"Netherlands Antilles";3;0;"09/30/2004 11:25:43";NULL
39;"AO";"Angola";2;0;"09/30/2004 11:25:43";NULL
40;"AP";"Asia/Pacific Region";2;0;"09/30/2004 11:25:43";NULL
41;"AQ";"Antarctica";8;0;"09/30/2004 11:25:43";NULL
42;"AR";"Argentina";5;0;"09/30/2004 11:25:43";NULL
43;"AS";"American Samoa";6;0;"09/30/2004 11:25:43";NULL
44;"AW";"Aruba";5;0;"09/30/2004 11:25:43";NULL
45;"AZ";"Azerbaijan";1;0;"09/30/2004 11:25:43";NULL
46;"BA";"Bosnia and Herzegovina";3;0;"09/30/2004 11:25:43";NULL
47;"BB";"Barbados";5;0;"09/30/2004 11:25:43";NULL
48;"BD";"Bangladesh";1;0;"09/30/2004 11:25:43";NULL
49;"BF";"Burkina Faso";2;0;"09/30/2004 11:25:43";NULL
50;"BG";"Bulgaria";3;0;"09/30/2004 11:25:43";NULL
51;"BH";"Bahrain";1;0;"09/30/2004 11:25:43";NULL
52;"BI";"Burundi";2;0;"09/30/2004 11:25:43";NULL
53;"BJ";"Benin";2;0;"09/30/2004 11:25:43";NULL
54;"BM";"Bermuda";4;0;"09/30/2004 11:25:43";NULL
55;"BN";"Brunei Darussalam";1;0;"09/30/2004 11:25:43";NULL
56;"BO";"Bolivia";5;0;"09/30/2004 11:25:43";NULL
57;"BS";"Bahamas";7;0;"09/30/2004 11:25:43";NULL
58;"BT";"Bhutan";1;0;"09/30/2004 11:25:43";NULL
59;"BV";"Bouvet Island";5;0;"09/30/2004 11:25:43";NULL
60;"BW";"Botswana";2;0;"09/30/2004 11:25:43";NULL
61;"BY";"Belarus";3;0;"09/30/2004 11:25:43";NULL
2
、执行导入命令

C:/>sqlldr userid=system/manager control=test.ctl

c)复杂格式的导入

Sqlldr

sql loader可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,
是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。
缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。
用法: SQLLDR keyword=value [,keyword=value,...]

有效的关键字:
    userid -- ORACLE username/password
   control
– 控制文件

       log
– 记录的日志文件
       bad
– 坏数据文件
      data
– 数据文件
   discard
– 丢弃的数据文件
discardmax
– 允许丢弃数据的最大值        (全部默认)
      skip -- Number of logical records to skip (
默认
0)
      load -- Number of logical records to load (
全部默认
)
    errors
– 允许的错误记录数          (默认
50)
      rows -- Number of rows in conventional path bind array or between direct path data saves
               
(每次提交的记录数,默认: 常规路径 64, 所有直接路径)

bindsize -- Size of conventional path bind array in bytes(
默认256000)
               
每次提交记录的缓冲区的大小(字节为单位,默认
256000)
    silent --
禁止输出信息
(header,feedback,errors,discards,partitions)
    direct
– 使用直通路径方式导入                    (默认
FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
parallel --
并行导入                   (默认
FALSE)
       file -- File to allocate extents from
bindsize成对使用,其中较小者会自动调整到较大者

sqlldr
先计算单条记录长度,乘以rows,如小于bindsize,不会试图扩张rows以填充bindsize;如超出,则以bindsize为准。

external_table
           -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(
默认NOT_USED)
columnarrayrows
           -- Number of rows for direct path column array(
默认
5000)
streamsize -- Size of direct path stream buffer in bytes(
默认
256000)
multithreading
           -- use multithreading in direct path
resumable -- enable or disable resumable for current session(
默认
FALSE)
resumable_name
           -- text string to help identify resumable statement
resumable_timeout
           -- wait time (in seconds) for RESUMABLE(
默认
7200)
date_cache -- size (in entries) of date conversion cache(
默认1000)

注意:有两种方式可以指定命令行参数:通过位置或者通过关键字。前者的例子:'sqlldr scott/tiger foo'
后者的例子:'sqlldr control=foo userid=scott/tiger'
不能前面使用关键字指定后面通过位置制定的混合方式;
比如:'sqlldr scott/tiger control=foo logfile=log' 是允许的,
'sqlldr scott/tiger control=foo log'不允许。
为清楚起见最好所有命令行参数都用关键字指定。
控制文件:
一个控制命令的脚本文件,通常以ctl结尾,内容如下:
LOAD DATA
INFILE 't.dat'             
要导入的文件
// INFILE 'tt.date'
导入多个文件
// INFILE *              
表示要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

INTO TABLE table_name    指定装入的表
BADFILE 'c:/bad.txt'   
可选,指定坏文件地址,缺省在当前目录下生成与原文件名一致的.bad文件

************* 以下是4种装入表的方式
APPEND            
原先的表有数据就加在后面
INSERT            
装载空表如果原先的表有数据 sqlloader会停止 默认值
REPLACE          
原先的表有数据原先的数据会全部删除
TRUNCATE       
指定的内容和replace的相同 会用truncate语句删除现存数据

************* 指定分隔符
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// TERMINATED BY WRITESPACE
以空白分割

TRAILING NULLCOLS            表的字段没有对应的值时允许为空

************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER
关键字 此列的数值不会被装载
//
: lg,lg,not 结果 lg lg
)
如果没声明FIELDS TERMINATED BY ',' 时,可以用下面两种方式实现同样功能:

1
.为每一列指定分隔符
(
col_1 [interger external] TERMINATED BY ',' ,
col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
)
2
.用位置告诉字段装载数据
(
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

 

 

sqlldr的全面资料:

 

 

 

sqlldr userid=lgone/tiger control=a.ctl

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_LINEBEGINDATA后面的数据中是没有直接对应

// 的列的值的 如果第一行改为 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('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

 

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,987268297,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,987268297,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,987268297,"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,987268297,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,987268297,This is the Finance

Office in Virginia|

 

==============================================================================

象这样的数据 nullif 子句

 

10-jan-200002350Flipper seemed unusually hungry today.

10510-jan-200009945Spread 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

 

 

 

 

阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭