下面的是关于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/2000 1/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(
1<file_old>)
2{
3chomp;
4if ($mycount%2 == 0)
5{print FILE_NEW $_." ";}
6else
7{print FILE_NEW $_."\n";}
8$mycount++;
9}
10
115、在命令窗口下执行 perl test.pl
126、得到一个新的文本文件：test.new,内容如下：
13
141 2 3 4 5 6 7
15a b c d e f g
16
17\---------------
18load data
19infile 'test.txt'
20concatenate(2)
21into table aa
22fields terminated by whitespace
23(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7)
24==============================================================
25
268 ***** 载入每行的行号
27
28load data
29infile *
30into table t
31replace
32( seqno RECNUM //载入每行的行号
33text Position(1:1024))
34BEGINDATA
35fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1
36fasdjfasdfl // 此行为 2 ...
37
389 ***** 载入有换行符的数据
39注意: unix 和 windows 不同 \n & /n
40还可以用 dbms_lob 和 bfile 看一个文件的回车 换行 等其他特殊字符
41
42< 1 > 使用一个非换行符的字符
43LOAD DATA
44INFILE *
45INTO TABLE DEPT
46REPLACE
47FIELDS TERMINATED BY ','
48TRAILING NULLCOLS
49(DEPTNO,
50DNAME "upper(:dname)",
51LOC "upper(:loc)",
52LAST_UPDATED "my_to_date( :last_updated )",
53COMMENTS "replace(:comments,'\n',chr(10))" // replace 的使用帮助转换换行符
54)
55BEGINDATA
5610,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia
5720,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia
5830,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia
5940,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia
60
61< 2 > 使用fix属性
62LOAD DATA
63INFILE demo17.dat "fix 101"
64INTO TABLE DEPT
65REPLACE
66FIELDS TERMINATED BY ','
67TRAILING NULLCOLS
68(DEPTNO,
69DNAME "upper(:dname)",
70LOC "upper(:loc)",
71LAST_UPDATED "my_to_date( :last_updated )",
72COMMENTS
73)
74demo17.dat
7510,Sales,Virginia,01-april-2001,This is the Sales
76Office in Virginia
7720,Accounting,Virginia,13/04/2001,This is the Accounting
78Office in Virginia
7930,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
80Office in Virginia
8140,Finance,Virginia,987268297,This is the Finance
82Office in Virginia
83
84// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同
85
86LOAD DATA
87INFILE demo18.dat "fix 101"
88INTO TABLE DEPT
89REPLACE
90FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
91TRAILING NULLCOLS
92(DEPTNO,
93DNAME "upper(:dname)",
94LOC "upper(:loc)",
95LAST_UPDATED "my_to_date( :last_updated )",
96COMMENTS
97)
98demo18.dat
9910,Sales,Virginia,01-april-2001,"This is the Sales
100Office in Virginia"
10120,Accounting,Virginia,13/04/2001,"This is the Accounting
102Office in Virginia"
10330,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
104Office in Virginia"
10540,Finance,Virginia,987268297,"This is the Finance
106Office in Virginia"
107
108< 3 > 使用var属性
109LOAD DATA
110INFILE demo19.dat "var 3"
111// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
112INTO TABLE DEPT
113REPLACE
114FIELDS TERMINATED BY ','
115TRAILING NULLCOLS
116(DEPTNO,
117DNAME "upper(:dname)",
118LOC "upper(:loc)",
119LAST_UPDATED "my_to_date( :last_updated )",
120COMMENTS
121)
122demo19.dat
12307110,Sales,Virginia,01-april-2001,This is the Sales
124Office in Virginia
12507820,Accounting,Virginia,13/04/2001,This is the Accounting
126Office in Virginia
12708730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
128Office in Virginia
12907140,Finance,Virginia,987268297,This is the Finance
130Office in Virginia
131
132< 4 > 使用str属性
133// 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
134
135此列中记录是以 a|\r\n 结束的
136select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
137结果 7C0D0A
138
139LOAD DATA
140INFILE demo20.dat "str X'7C0D0A'"
141INTO TABLE DEPT
142REPLACE
143FIELDS TERMINATED BY ','
144TRAILING NULLCOLS
145(DEPTNO,
146DNAME "upper(:dname)",
147LOC "upper(:loc)",
148LAST_UPDATED "my_to_date( :last_updated )",
149COMMENTS
150)
151demo20.dat
15210,Sales,Virginia,01-april-2001,This is the Sales
153Office in Virginia|
15420,Accounting,Virginia,13/04/2001,This is the Accounting
155Office in Virginia|
15630,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
157Office in Virginia|
15840,Finance,Virginia,987268297,This is the Finance
159Office in Virginia|
160
161
16210 ***** 将数据导入多个表
163
164LOAD DATA
165INFILE *
166REPLACE
167INTO TABLE emp WHEN empno != ' '
168(
169empno POSITION(1:4) INTEGER EXTERNAL,
170ename POSITION(6:15) CHAR,
171deptno POSITION(17:18) CHAR,
172mgr POSITION(20:23) INTEGER EXTERNAL
173)
174INTO TABLE proj WHEN projno != ' '
175(
176projno POSITION(25:27) INTEGER EXTERNAL,
177empno POSITION(1:4) INTEGER EXTERNAL
178)
179
18011 ***** 转载 RAW 数据 或 转载长字段
181
182options(bindsize=1075700,rows=1)
183load data
184infile my.data "fix 53760" // 53760 * 20=1075700 1075700是小于64K 的最大因子
185concatenate 20
186preserve blanks
187into table foo
188append
189(id constant 1,bigdata raw(1075700))
190
19112 ***** 转载 LOB 数据
192
193用 dbms_lob dbms_lob 转载的数据要在服务器上 通过网络的不行
194
195drop table demo;
196
197
198create or replace directory dir1 as 'c:\temp\';
199
200create or replace directory "dir2" as 'c:\temp\';
201
202create table demo
203( id int primary key,
204theClob clob
205)
206/
207
208host echo 'Hello World\\!' > c:\temp\test.txt
209
210declare
211l_clob clob;
212l_bfile bfile;
213begin
214insert into demo values ( 1, empty_clob() )
215returning theclob into l_clob;
216
217l_bfile := bfilename( 'DIR1', 'test.txt' ); -- DIR1 要大写
218dbms_lob.fileopen( l_bfile );
219
220dbms_lob.loadfromfile( l_clob, l_bfile,
221dbms_lob.getlength( l_bfile ) );
222
223dbms_lob.fileclose( l_bfile );
224end;
225/
226
227select dbms_lob.getlength(theClob), theClob from demo
228/
229
230\----------------------------------
231用 sqlldr 在同一行的LOB lob数据在同一个数据文件中
232
233LOAD DATA
234INFILE demo21.dat "str X'7C0D0A'"
235INTO TABLE DEPT
236REPLACE
237FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
238TRAILING NULLCOLS
239(DEPTNO,
240DNAME "upper(:dname)",
241LOC "upper(:loc)",
242LAST_UPDATED "my_to_date( :last_updated )",
243COMMENTS char(1000000)
244)
245
24610,Sales,Virginia,01-april-2001,This is the Sales
247Office in Virginia|
24820,Accounting,Virginia,13/04/2001,This is the Accounting
249Office in Virginia|
25030,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
251Office in Virginia|
25240,Finance,Virginia,987268297,"This is the Finance
253Office in Virginia, it has embedded commas and is
254much longer then the other comments field. If you
255feel the need to add double quoted text in here like
256this: ""You will need to double up those quotes!"" to
257preserve them in the string. This field keeps going for upto
2581,000,000 bytes or until we hit the magic end of record marker,
259the | followed by a end of line -- it is right here ->"|
260
261\------------------------------------------------------
262用 sqlldr 不在同一行的LOB 就是lob数据在单独的文件中
263
264create table lob_demo
265( owner varchar2(255),
266timestamp date,
267filename varchar2(255),
268text clob
269)
270/
271
272LOAD DATA /////////// window 的
273INFILE *
274REPLACE
275INTO TABLE LOB_DEMO
276( owner position(40:61),
277timestamp position(1:18) "to_date(:timestamp||'m','mm/dd/yyyy hh:miam')",
278filename position(63:80), -- 下面的LOB的filename是从这里来的
279text LOBFILE(filename) TERMINATED BY EOF
280)
281BEGINDATA
28204/14/2001 12:36p 1,697 BUILTIN\Administrators demo10.log
283// 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况 *******
284
285///// unix 下的情况
286用 ls -l 得到上面数据的情况
287控制文件就改下时间的格式
288
289\------------------------------
290lob 到对象列
291
292create table image_load( id number, name varchar2(255),
293image ordsys.ordimage )
294/
295
296desc ordsys.ordimage
297
298desc ordsys.ordsource
299
300LOAD DATA
301INFILE *
302INTO TABLE T
303replace
304fields terminated by ","
305(
306id,
307name,
308fiel_name filler,
309image column object
310(
311source column object
312(
313localdatalobfile(file_name) terminated by bof
314nullif file_name='NONE'
315)
316)
317)
318begindata
3191,icons,icons.gif
320
321
32213 ***** 转载varrays /嵌套表
323
324create type myArrayType
325as varray(10) of number(12,2)
326/
327
328create table t
329( x int primary key, y myArrayType )
330/
331
332LOAD DATA
333INFILE *
334INTO TABLE T
335replace
336fields terminated by ","
337(
338x,
339y_cnt FILLER,
340y varray count (y_cnt)
341(
342y
343)
344)
345
346BEGINDATA
3471,2,3,4
3482,10,1,2,3,4,5,6,7,8,9,10
3493,5,5,4,3,2,1
350
351
352\------------------------------------
353
354create or replace type myTableType
355as table of number(12,2)
356/
357
358create table t
359( x int primary key, y myTableType )
360nested table y store as y_tab
361/
362
363
364LOAD DATA
365INFILE *
366INTO TABLE T
367replace
368fields terminated by ","
369(
370x,
371y nested table count (CONSTANT 5)
372(
373y
374)
375)
376
377BEGINDATA
3781,100,200,300,400,500
3792,123,243,542,123,432
380
381==============================================================================
382象这样的数据 用 nullif 子句
383
38410-jan-200002350Flipper seemed unusually hungry today.
38510510-jan-200009945Spread over three meals.
386
387id position(1:3) nullif id=blanks // 这里可以是blanks 或者别的表达式
388// 下面是另一个列子 第一行的 1 在数据库中将成为 null
389LOAD DATA
390INFILE *
391INTO TABLE T
392REPLACE
393(n position(1:2) integer external nullif n='1',
394v position(3:8)
395)
396BEGINDATA
3971 10
39820lg
399\------------------------------------------------------------
400
401如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format</file_old>