Oracle —sqlldr导入数据

以你之姓@ 2021-11-23 07:26 556阅读 0赞

Oracle 的SQL\*LOADER可以将外部数据加载到数据库表中。

sqlldr  userid = USERNAME / PASSWORD @ IP/数据库名  control = D:/data.ctl  bad = D:/ bad.bad  log =D:/log.log

## 1.1 SQL\*LOADER的基本特点： ##

*  1）能装入不同数据类型文件及多个数据文件的数据
 *  2）可装入固定格式，自由定界以及可度长格式的数据
 *  3）可以装入二进制，压缩十进制数据
 *  4）一次可对多个表装入数据
 *  5）连接多个物理记录装到一个记录中
 *  6）对一单记录分解再装入到表中
 *  7）可以用 数对制定列生成唯一的KEY
 *  8）可对磁盘或 磁带数据文件装入制表中
 *  9）提供装入错误报告
 *  10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中。
 *  11）可以用filler关键字，不装载某列：field2 FILLER,
 *  12）可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据
 *  13）可以将多行当着一行记录

## 1.2控制文件(\*.ctl) ##

控制文件是用一种语言写的文本文件，这个文本文件能被SQL\*LOADER识别。SQL\*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成：

*  全局选件，行，跳过的记录数等；
 *   INFILE子句指定的输入数据；
 *  数据特性说明。

## 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 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。
4.  FIXED length 要读的记录长度为length字节，
5.  VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。缺伤为8k字节。
6.   BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
7.  DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
8.  DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。

**2）加载的方法：**

1.  APPEND 给表添加行。
2.  INSERT 给空表增加行（如果表中有记录则退出）。
3.  REPLACE 先清空表再加载数据。
4.   RECLEN 用于两种情况，1）SQLLDR不能自动计算记录长度，2）或用户想看坏文件的完整记录时。对于后一种，Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录，则可以将整条记录写到坏文件中。

**3）指定最大的记录长度：**

1.  CONCATENATE 允许用户设定一个整数，表示要组合逻辑记录的数目。

**4）建立逻辑记录：**

1.  THIS 检查当前记录条件，如果为真则连接下一个记录。
2.  NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。
3.   Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'

**5）指定要加载的表：**

1.  INTO 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 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ，则它们的顺序决定计算的顺序。

**7）定义列：**  
column 是表列名。列的取值可以是：

1.  BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
2.  CONSTANT 表示赋予常数。
3.  SEQUENCE 表示序列可以从任意序号开始，格式为：
4.  SEQUENCE （ \{ integer | MAX |COUNT\} \[,increment\]
5.  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 \[,divcision\]）  
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.  其它

数据文件的要求：

*  数据类型的指定

1.  CHAR 字符型
2.  INTEGER EXTERNAL 整型
3.  DECIMAL EXTERNAL 浮点型

3.1数据文件的内容  
可以在OS下的一个文件；或跟在控制文件下的具体数据。数据文件可以是：  
1、 二进制与字符格式：LOADER可以把二进制文件读（当成字符读）列表中  
2、 固定格式：记录中的数据、数据类型、 数据长度固定。  
3、 可变格式：每个记录至少有一个可变长数据字段，一个记录可以是一个连续的字符串。  
数据段的分界（如姓名、年龄）如用“，”作字段的 分 ；用，"’作数据括号等  
4、 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录，记录文件运行情况文件：包括以下内容：  
1、 运行日期：软件版本号  
2、 全部输入，输出文件名；对命令行的展示信息，补充信息，  
3、 对每个装入信息报告：如表名，装入情况；对初始装入， 加截入或更新装  
入的选择情况，栏信息  
4、 数据错误报告：错误码；放弃记录报告  
5、 每个装X报告：装入行；装入行数，可能跳过行数；可能拒绝行数；可能放  
弃行数等  
6、 统计概要：使用空间（包大小，长度）；读入记录数，装入记录数，跳过记  
录数；拒绝记录数，放弃记录数；运行时间等。

==========================================================================================================  
sql load的一点小总结

跳过首行

OPTIONS (skip=1) — sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
    LOAD DATA
    append — 这里用 了 append 来操作，在表 users 中附加记录
    INTO TABLE users
    Fields terminated by “,”
    trailing nullcols
    (
    column1,
    column2,
    column3,
    ….
    );

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\_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('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 Sales\\nOffice in Virginia  
20,Accounting,Virginia,13/04/2001,This is the Accounting\\nOffice in Virginia  
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\\nOffice in Virginia  
40,Finance,Virginia,987268297,This is the Finance\\nOffice 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|\\r\\n 结束的  
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-200009945Sdivad 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  
==========================================================================================================  
SQL\*Loader 使用指南（转载）  
SQL\*Loader是Oracle数据库导入外部数据的一个工具.它和的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  
跳过数据行：  
可以用 "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.  
SQL\*Loader 数据的提交：  
一般情况下是在导入数据文件数据后提交的。  
也可以通过指定 ROWS= 参数来指定每次提交记录数。  
提高 SQL\*Loader 的性能：  
1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。  
2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。  
3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。  
4) 可以同时运行多个导入任务.  
常规导入与direct导入方式的区别：  
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。

在 Oracle 数据库中，我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法：  
  
1. A 表的记录导出为一条条分号隔开的 insert 语句，然后执行插入到 B 表中  
2. 建立数据库间的 dblink，然后用 create table B as select \* from [A@dblink][A_dblink] where ...，或 insert into B select \* from [A@dblink][A_dblink] where ...  
3. exp A 表，再 imp 到 B 表，exp 时可加查询条件  
4. 程序实现 select from A ..，然后 insert into B ...，也要分批提交  
5. 再就是本篇要说到的 Sql Loader(sqlldr) 来导入数据，效果比起逐条 insert 来很明显  
  
第 1 种方法在记录多时是个噩梦，需三五百条的分批提交，否则客户端会死掉，而且导入过程很慢。如果要不产生 REDO 来提高 insert into 的性能，就要下面那样做：  
  
alter table B nologging;  
insert /\* +APPEND \*/ into B(c1,c2) values(x,xx);  
insert /\* +APPEND \*/ into B select \* from A@dblink where .....;

好啦，前面简述了 Oracle 中数据导入导出的各种方法，我想一定还有更高明的。下面重点讲讲 Oracle  的 Sql Loader (sqlldr) 的用法。  
  
在命令行下执行 Oracle  的 sqlldr 命令，可以看到它的详细参数说明，要着重关注以下几个参数：  
  
userid -- Oracle 的 username/password\[@servicename\]  
control -- 控制文件，可能包含表的数据  
\-------------------------------------------------------------------------------------------------------  
log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log  
bad -- 坏数据文件，默认为 控制文件(去除扩展名).bad  
data -- 数据文件，一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作  
errors -- 允许的错误记录数，可以用他来控制一条记录都不能错  
rows -- 多少条记录提交一次，默认为 64  
skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述  
  
还有更多的 sqlldr 的参数说明请参考：[sql loader的用法][sql loader]。  
  
用例子来演示 sqlldr 的使用，有两种使用方法：  
  
1. 只使用一个控制文件，在这个控制文件中包含数据  
2. 使用一个控制文件(作为模板) 和一个数据文件  
  
一般为了利于模板和数据的分离，以及程序的不同分工会使用第二种方式，所以先来看这种用法。数据文件可以是 CSV 文件或者以其他分割符分隔的，数据文件可以用 PL/SQL Developer 或者 Toad 导出，也可以[用 SQL \*Plus 的  spool 格式化产出，或是 UTL\_FILE 包生成][SQL _Plus _ spool _ UTL_FILE]。另外，用 Toad 还能直接生成包含数据的控制文件。  
  
首先，假定有这么一个表 users，并插入五条记录：  
  
create table users(  
    user\_id number,           --用户 ID  
    user\_name varchar2(50),   --用户名  
    login\_times number,       --登陆次数  
    last\_login date           --最后登录日期  
)

insert into users values(1,'Unmi',3,sysdate);  
insert into users values(2,NULL,5,to\_date('2008-10-15','YYYY-MM-DD'));  
insert into users values(3,'隔叶黄莺',8,to\_date('2009-01-02','YYYY-MM-DD'));  
insert into users values(4,'Kypfos',NULL,NULL);  
insert into users values(5,'不知秋',1,to\_date('2008-12-23','YYYY-MM-DD'));

第二种方式： 使用一个控制文件(作为模板) 和一个数据文件  
  
1) 建立数据文件，我们这里用 PL/SQL Developer 导出表 users 的记录为 users\_data.csv 文件，内容如下：  
"   ","USER\_ID","USER\_NAME","LOGIN\_TIMES","LAST\_LOGIN"  
"1","1","Unmi","3","2009-1-5 20:34:44"  
"2","2","","5","2008-10-15"  
"3","3","隔叶黄莺","8","2009-1-2"  
"4","4","Kypfos","",""  
"5","5","不知秋","1","2008-12-23"

2) 建立一个控制文件 users.ctl，内容如下：  
  
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
LOAD DATA  
INFILE "users\_data.csv" --指定外部数据文件，可以写多个 INFILE "another\_data\_file.csv" 指定多个数据文件  
\--这里还可以使用 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() 函数转换  
)

说明：在操作类型 truncate 位置可用以下中的一值：  
  
1) insert     --为缺省方式，在数据装载开始时要求表为空  
2) append  --在表中追加新记录  
3) replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录  
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录  
  
3) 执行命令：  
  
sqlldr [dbuser/dbpass@dbservice][dbuser_dbpass_dbservice] control=users.ctl  
  
在 dbservice 指示的数据库的表 users 中记录就和数据文件中的一样了。  
  
执行完 sqlldr 后希望能留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。  
  
第一种方式，只使用一个控制文件在这个控制文件中包含数据  
  
1) 把 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

2) 执行一样的命令：  
  
sqlldr [dbuser/dbpass@dbservice][dbuser_dbpass_dbservice] control=users.ctl  
  
比如，在控制台会显示这样的信息：  
  
C:\\>sqlldr [dbuser/dbpass@dbservice][dbuser_dbpass_dbservice] control=users.ctl

SQL\*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009

达到提交点，逻辑记录计数4  
达到提交点，逻辑记录计数5  
  
上面的控制文件包含的内容比较复杂(演示目的)，请根据注释理解每个参数的意义。还能由此发掘更多用法。  
  
最后说下有关 SQL \*Loader 的性能与并发操作

1) ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。(体验过在 PL/SQL Developer 中一次执行几条条以上的 insert 语句的情形吗？)

2）常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性能。当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志(是否要 alter table table1 nologging 呢?)。这个选项只能和 direct 一起使用。

4) 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.

sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true     
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true     
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true

当加载大量数据时（大约超过10GB），最好抑制日志的产生：     
    
  SQL>ALTER   TABLE   RESULTXT   nologging;   
    
  这样不产生REDO LOG，可以提高效率。然后在 CONTROL 文件中 load data 上面加一行：unrecoverable，  此选项必须要与DIRECT共同应用。     
    
  在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到 1－10G 就算不错了，开始可用结构 相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

[A_dblink]: mailto:A@dblink
[sql loader]: http://www.myfaq.com.cn/2005September/2005-09-13/206612.html
[SQL _Plus _ spool _ UTL_FILE]: http://soft.ccw.com.cn/programing/database/htm2008/20080925_509432_2.shtml
[dbuser_dbpass_dbservice]: mailto:dbuser/dbpass@dbservice