oracle sqlldr控制文件模板:)
oracle sqlldr控制文件模板1
Sqlldr userid=lgone/tiger control=a.ctl 2
LOAD DATA 3
INFILE 't.dat' // 要导入的文件 4
// INFILE 'tt.date' // 导入多个文件 5
// INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容, *和't.dat'不能同时存在6
7
INTO TABLE table_name // 指定装入的表 8
BADFILE 'c:bad.txt' // 指定坏文件地址 9
10
************* 以下是4种装入表的方式 11
APPEND // 原先的表有数据 就加在后面 12
// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值 13
// REPLACE // 原先的表有数据 原先的数据会全部删除 14
// TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据 15
16
************* 指定的TERMINATED可以在表的开头 也可在表的内部字段部分 17
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 18
// 装载这种数据: 10,lg,"""lg""","lg,lg" 19
// 在表中结果: 10 lg "lg" lg,lg 20
// TERMINATED BY X '09' // 以十六进制格式 '09' 表示的 21
// TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg 22
23
TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空 24
25
************* 下面是表的字段 26
( 27
col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载 28
// 如: lg,lg,not 结果 lg lg 29
) 30
// 当没声明FIELDS TERMINATED BY ',' 时 31
// ( 32
// col_1 [interger external] TERMINATED BY ',' , 33
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' , 34
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' 35
// ) 36
// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据 37
// ( 38
// col_1 position(1:2), 39
// col_2 position(3:10), 40
// col_3 position(*:16), // 这个字段的开始位置在前一字段的结束位置 41
// col_4 position(1:16), 42
// col_5 position(3:10) char(8) // 指定字段的类型 43
// ) 44
45
BEGINDATA // 对应开始的 INFILE * 要导入的内容就在control文件里 46
10,Sql,what 47
20,lg,show 48
49
===================================================================================== 50
/**///////////// 注意begindata后的数值前面不能有空格 51
52
1 ***** 普通装载 53
LOAD DATA 54
INFILE * 55
INTO TABLE DEPT 56
REPLACE 57
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 58
(DEPTNO, 59
DNAME, 60
LOC 61
) 62
BEGINDATA 63
10,Sales,"""USA""" 64
20,Accounting,"Virginia,USA" 65
30,Consulting,Virginia 66
40,Finance,Virginia 67
50,"Finance","",Virginia // loc 列将为空 68
60,"Finance",,Virginia // loc 列将为空 69
70
2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况 71
LOAD DATA 72
INFILE * 73
INTO TABLE DEPT 74
REPLACE 75
FIELDS TERMINATED BY WHITESPACE 76
-- FIELDS TERMINATED BY x'09' 77
(DEPTNO, 78
DNAME, 79
LOC 80
) 81
BEGINDATA 82
10 Sales Virginia 83
84
3 ***** 指定不装载那一列 85
LOAD DATA 86
INFILE * 87
INTO TABLE DEPT 88
REPLACE 89
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 90
( DEPTNO, 91
FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 将不会被装载 92
DNAME, 93
LOC 94
) 95
BEGINDATA 96
20,Something Not To Be Loaded,Accounting,"Virginia,USA" 97
98
4 ***** position的列子 99
LOAD DATA 100
INFILE * 101
INTO TABLE DEPT 102
REPLACE 103
( DEPTNO position(1:2), 104
DNAME position(*:16), // 这个字段的开始位置在前一字段的结束位置 105
LOC position(*:29), 106
ENTIRE_LINE position(1:29) 107
) 108
BEGINDATA 109
10Accounting Virginia,USA 110
111
5 ***** 使用函数 日期的一种表达 TRAILING NULLCOLS的使用 112
LOAD DATA 113
INFILE * 114
INTO TABLE DEPT 115
REPLACE 116
FIELDS TERMINATED BY ',' 117
TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应 118
// 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了 119
(DEPTNO, 120
DNAME "upper(:dname)", // 使用函数 121
LOC "upper(:loc)", 122
LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等 123
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated" 124
) 125
BEGINDATA 126
10,Sales,Virginia,1/5/2000 127
20,Accounting,Virginia,21/6/1999 128
30,Consulting,Virginia,5/1/2000 129
40,Finance,Virginia,15/3/2001 130
131
6 ***** 使用自定义的函数 // 解决的时间问题 132
create or replace 133
my_to_date( p_string in varchar2 ) return date 134
as 135
type fmtArray is table of varchar2(25); 136
137
l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy', 138
'dd/mm/yyyy', 139
'dd/mm/yyyy hh24:mi:ss' ); 140
l_return date; 141
begin 142
for i in 1 .. l_fmts.count 143
loop 144
begin 145
l_return := to_date( p_string, l_fmts(i) ); 146
exception 147
when others then null; 148
end; 149
EXIT when l_return is not null; 150
end loop; 151
152
if ( l_return is null ) 153
then 154
l_return := 155
new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * 156
p_string, 'GMT', 'EST' ); 157
end if; 158
159
return l_return; 160
end; 161
/ 162
163
LOAD DATA 164
INFILE * 165
INTO TABLE DEPT 166
REPLACE 167
FIELDS TERMINATED BY ',' 168
TRAILING NULLCOLS 169
(DEPTNO, 170
DNAME "upper(:dname)", 171
LOC "upper(:loc)", 172
LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数 173
) 174
BEGINDATA 175
10,Sales,Virginia,01-april-2001 176
20,Accounting,Virginia,13/04/2001 177
30,Consulting,Virginia,14/04/2001 12:02:02 178
40,Finance,Virginia,987268297 179
50,Finance,Virginia,02-apr-2001 180
60,Finance,Virginia,Not a date 181
182
7 ***** 合并多行记录为一行记录 183
LOAD DATA 184
INFILE * 185
concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录 186
INTO TABLE DEPT 187
replace 188
FIELDS TERMINATED BY ',' 189
(DEPTNO, 190
DNAME "upper(:dname)", 191
LOC "upper(:loc)", 192
LAST_UPDATED date 'dd/mm/yyyy' 193
) 194
BEGINDATA 195
10,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000 196
Virginia, 197
1/5/2000 198
// 这列子用 continueif list="," 也可以 199
告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行 200
201
LOAD DATA 202
INFILE * 203
continueif this(1:1) = '-' // 找每行的开始是否有连接字符 - 有就把下一行连接为一行 204
// 如 -10,Sales,Virginia, 205
// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000 206
// 其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想 207
INTO TABLE DEPT 208
replace 209
FIELDS TERMINATED BY ',' 210
(DEPTNO, 211
DNAME "upper(:dname)", 212
LOC "upper(:loc)", 213
LAST_UPDATED date 'dd/mm/yyyy' 214
) 215
BEGINDATA // 但是好象不能象右面的那样使用 216
-10,Sales,Virginia, -10,Sales,Virginia, 217
1/5/2000 1/5/2000 218
-40, 40,Finance,Virginia,13/04/2001 219
Finance,Virginia,13/04/2001 220
221
8 ***** 载入每行的行号 222
223
load data 224
infile * 225
into table t 226
replace 227
( seqno RECNUM //载入每行的行号 228
text Position(1:1024)) 229
BEGINDATA 230
fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1 231
fasdjfasdfl // 此行为 2 
232
233
9 ***** 载入有换行符的数据 234
注意: unix 和 windows 不同 & /n 235
< 1 > 使用一个非换行符的字符 236
LOAD DATA 237
INFILE * 238
INTO TABLE DEPT 239
REPLACE 240
FIELDS TERMINATED BY ',' 241
TRAILING NULLCOLS 242
(DEPTNO, 243
DNAME "upper(:dname)", 244
LOC "upper(:loc)", 245
LAST_UPDATED "my_to_date( :last_updated )", 246
COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用帮助转换换行符 247
) 248
BEGINDATA 249
10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia 250
20,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia 251
30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia 252
40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia 253
254
< 2 > 使用fix属性 255
LOAD DATA 256
INFILE demo17.dat "fix 101" 257
INTO TABLE DEPT 258
REPLACE 259
FIELDS TERMINATED BY ',' 260
TRAILING NULLCOLS 261
(DEPTNO, 262
DNAME "upper(:dname)", 263
LOC "upper(:loc)", 264
LAST_UPDATED "my_to_date( :last_updated )", 265
COMMENTS 266
) 267
demo17.dat 268
10,Sales,Virginia,01-april-2001,This is the Sales 269
Office in Virginia 270
20,Accounting,Virginia,13/04/2001,This is the Accounting 271
Office in Virginia 272
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 273
Office in Virginia 274
40,Finance,Virginia,987268297,This is the Finance 275
Office in Virginia 276
277
// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同 278
279
LOAD DATA 280
INFILE demo18.dat "fix 101" 281
INTO TABLE DEPT 282
REPLACE 283
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 284
TRAILING NULLCOLS 285
(DEPTNO, 286
DNAME "upper(:dname)", 287
LOC "upper(:loc)", 288
LAST_UPDATED "my_to_date( :last_updated )", 289
COMMENTS 290
) 291
demo18.dat 292
10,Sales,Virginia,01-april-2001,"This is the Sales 293
Office in Virginia" 294
20,Accounting,Virginia,13/04/2001,"This is the Accounting 295
Office in Virginia" 296
30,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting 297
Office in Virginia" 298
40,Finance,Virginia,987268297,"This is the Finance 299
Office in Virginia" 300
301
< 3 > 使用var属性 302
LOAD DATA 303
INFILE demo19.dat "var 3" 304
// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节 305
INTO TABLE DEPT 306
REPLACE 307
FIELDS TERMINATED BY ',' 308
TRAILING NULLCOLS 309
(DEPTNO, 310
DNAME "upper(:dname)", 311
LOC "upper(:loc)", 312
LAST_UPDATED "my_to_date( :last_updated )", 313
COMMENTS 314
) 315
demo19.dat 316
07110,Sales,Virginia,01-april-2001,This is the Sales 317
Office in Virginia 318
07820,Accounting,Virginia,13/04/2001,This is the Accounting 319
Office in Virginia 320
08730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 321
Office in Virginia 322
07140,Finance,Virginia,987268297,This is the Finance 323
Office in Virginia 324
325
< 4 > 使用str属性 326
// 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10) 327
328
此列中记录是以 a|rn 结束的 329
select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual; 330
结果 7C0D0A 331
332
LOAD DATA 333
INFILE demo20.dat "str X'7C0D0A'" 334
INTO TABLE DEPT 335
REPLACE 336
FIELDS TERMINATED BY ',' 337
TRAILING NULLCOLS 338
(DEPTNO, 339
DNAME "upper(:dname)", 340
LOC "upper(:loc)", 341
LAST_UPDATED "my_to_date( :last_updated )", 342
COMMENTS 343
) 344
demo20.dat 345
10,Sales,Virginia,01-april-2001,This is the Sales 346
Office in Virginia| 347
20,Accounting,Virginia,13/04/2001,This is the Accounting 348
Office in Virginia| 349
30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting 350
Office in Virginia| 351
40,Finance,Virginia,987268297,This is the Finance 352
Office in Virginia| 353
354
============================================================================== 355
象这样的数据 用 nullif 子句 356
357
10-jan-200002350Flipper seemed unusually hungry today. 358
10510-jan-200009945Spread over three meals. 359
360
id position(1:3) nullif id=blanks // 这里可以是blanks 或者别的表达式 361
// 下面是另一个列子 第一行的 1 在数据库中将成为 null 362
LOAD DATA 363
INFILE * 364
INTO TABLE T 365
REPLACE 366
(n position(1:2) integer external nullif n='1', 367
v position(3:8) 368
) 369
BEGINDATA 370
1 10 371
20lg 372
------------------------------------------------------------ 373
374
如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format 