下面的是关于sql*loader 的使用的一点总结 有些是来自itpub上的一些网友的总结

大部分是Oracle专家高级编程上的实例 只是我实践以后写的结果



;;;;sqlldr userid=lgone/tiger control=a.ctl


LOAD DATA
INFILE 't.dat';;;;// 要导入的文件
// INFILE 'tt.dat'// 导入多个文件
// INFILE *; // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

INTO TABLE table_name;// 指定装入的表
// into table t_name partition (p_1); 分区的载入

BADFILE 'c:bad.txt';;// 指定坏文件地址

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

SKIP 5;可以用 "SKIP n" 要害字来指定导入时可以跳过多少行数据

*************; 指定的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;***** 指定不装载那一列还可用 POSTION(x:y) 来分隔数据
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;*****使用自定义的函数 // 解决的时间问题
使用函数这仅适合于常规导入，并不适合 direct导入方式 9i可能可以
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/20001/5/2000
-40,;;;;40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001

================================ 用别的语言帮助解决的方法
txt文件中的每2行作为一个记录插入到数据库中的一条记录,文件是定长的
1; 2; 3; 4; 5
6; 7
插入数据记录是
1 2 3 4 5 6 7
-------------
可以把换行符作为一个分隔符来处理

-------------
1、到www.activeperl.com去下载一个activeperl5.6 MSI
2、安装 PERL

3、你的文本文件示例：test.old
1 2 3 4 5
6 7
a b c d e
f g

4、我的PERL程序：test.pl
$mycount=2;
open(FILE_OLD","TEST.OLD");
open(FILE_NEW",">TEST.NEW");
while(<FILE_OLD>)
{
chomp;
if ($mycount%2 == 0)
{print FILE_NEW $_." ";}
else
{print FILE_NEW $_."n";}
$mycount++;
}

5、在命令窗口下执行 perl test.pl
6、得到一个新的文本文件：test.new,内容如下：

1 2 3 4 5 6 7
a b c d e f g

---------------
load data
infile 'test.txt'
concatenate(2)
into table aa
fields terminated by whitespace
(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7)
==============================================================

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
还可以用 dbms_lob; 和 bfile 看一个文件的回车 换行 等其他非凡字符

< 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)

此列中记录是以 arn 结束的
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


10*****将数据导入多个表

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
)

11*****转载 RAW 数据 或 转载长字段

options(bindsize=1075700,rows=1)
load data
infile my.data "fix 53760"// 53760 * 20=1075700;1075700是小于64K 的最大因子
concatenate 20
preserve blanks
into table foo
append
(id constant 1,bigdata raw(1075700))

12*****转载 LOB 数据

用 dbms_lobdbms_lob 转载的数据要在服务器上 通过网络的不行

drop table demo;


create or replace Directory dir1as 'c:temp';

create or replace directory "dir2" as 'c:temp';

create table demo
( id;;;;;int primary key,
theClobclob
)
/

host echo 'Hello World!' > c:temptest.txt

declare
l_clob;clob;
l_bfilebfile;
begin
insert into demo values ( 1, empty_clob() )
returning theclob into l_clob;

l_bfile := bfilename( 'DIR1', 'test.txt' )-- DIR1 要大写
dbms_lob.fileopen( l_bfile );

dbms_lob.loadfromfile( l_clob, l_bfile,
dbms_lob.getlength( l_bfile ) );

dbms_lob.fileclose( l_bfile );
end;
/

select dbms_lob.getlength(theClob), theClob from demo
/

----------------------------------
用 sqlldr; 在同一行的LOB; lob数据在同一个数据文件中

LOAD DATA
INFILE demo21.dat "str X'7C0D0A'"
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;;char(1000000)
)

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, it has embedded commas and is
mUCh longer then the other comments field.; If you
feel the need to add double quoted text in here like
this: ""You will need to double up those quotes!
"" to
preserve them in the string.; This field keeps going for upto
1,000,000 bytes or until we hit the magic end of record marker,
the followed by a end of line -- it is right here ->"

------------------------------------------------------
用 sqlldr; 不在同一行的LOB; 就是lob数据在单独的文件中

create table lob_demo
( owner;;varchar2(255),
timestamp date,
filename; varchar2(255),
text;;;clob
)
/

LOAD DATA///////////window 的
INFILE *
REPLACE
INTO TABLE LOB_DEMO
( owner;;;position(40:61),
timestamp; position(1:18) "to_date(:timestamp'm','mm/dd/yyyy; hh:miam')",
filenameposition(63:80),;-- 下面的LOB的filename是从这里来的
text LOBFILE(filename) TERMINATED BY EOF
)
BEGINDATA
04/14/2001; 12:36p;;;;1,697 BUILTINAdministrators demo10.log
// 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况; *******

///// unix 下的情况
用 ls -l 得到上面数据的情况
控制文件就改下时间的格式

------------------------------
lob 到对象列

create table image_load( id number, name varchar2(255),
image ordsys.ordimage )
/

desc ordsys.ordimage

desc ordsys.ordsource

LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
id,
name,
fiel_name filler,
image column object
(
source column object
(
localdatalobfile(file_name) terminated by bof
nullif file_name='NONE'
)
)
)
begindata
1,icons,icons.gif


13*****转载varrays /嵌套表

create type myArrayType
as varray(10) of number(12,2)
/

create table t
( x int primary key, y myArrayType )
/

LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
x,
y_cnt;;;;FILLER,
yvarray count (y_cnt)
(
y

)
)

BEGINDATA
1,2,3,4
2,10,1,2,3,4,5,6,7,8,9,10
3,5,5,4,3,2,1


------------------------------------

create or replace type myTableType
as table of number(12,2)
/

create table t
( x int primary key, y myTableType )
nested table y store as y_tab
/


LOAD DATA
INFILE *
INTO TABLE T
replace
fields terminated by ","
(
x,
ynested table count (CONSTANT 5)
(
y
)
)

BEGINDATA
1,100,200,300,400,500
2,123,243,542,123,432

==============================================================================
象这样的数据 用 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