oracle sqlldr控制文件模板
1
Sqlldr userid=lgone/tiger control=a.ctl
2LOAD DATA
3INFILE 't.dat' // 要导入的文件
4// INFILE 'tt.date' // 导入多个文件
5// INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容, *和't.dat'不能同时存在
6
7INTO TABLE table_name // 指定装入的表
8BADFILE 'c:bad.txt' // 指定坏文件地址
9
10************* 以下是4种装入表的方式
11APPEND // 原先的表有数据 就加在后面
12// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
13// REPLACE // 原先的表有数据 原先的数据会全部删除
14// TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据
15
16************* 指定的TERMINATED可以在表的开头 也可在表的内部字段部分
17FIELDS 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
23TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空
24
25************* 下面是表的字段
26(
27col_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
45BEGINDATA // 对应开始的 INFILE * 要导入的内容就在control文件里
4610,Sql,what
4720,lg,show
48
49=====================================================================================
50
//////////// 注意begindata后的数值前面不能有空格
51
521 ***** 普通装载
53LOAD DATA
54INFILE *
55INTO TABLE DEPT
56REPLACE
57FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
58(DEPTNO,
59DNAME,
60LOC
61)
62BEGINDATA
6310,Sales,"""USA"""
6420,Accounting,"Virginia,USA"
6530,Consulting,Virginia
6640,Finance,Virginia
6750,"Finance","",Virginia // loc 列将为空
6860,"Finance",,Virginia // loc 列将为空
69
702 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况
71LOAD DATA
72INFILE *
73INTO TABLE DEPT
74REPLACE
75FIELDS TERMINATED BY WHITESPACE
76-- FIELDS TERMINATED BY x'09'
77(DEPTNO,
78DNAME,
79LOC
80)
81BEGINDATA
8210 Sales Virginia
83
843 ***** 指定不装载那一列
85LOAD DATA
86INFILE *
87INTO TABLE DEPT
88REPLACE
89FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
90( DEPTNO,
91FILLER_1 FILLER, // 下面的 "Something Not To Be Loaded" 将不会被装载
92DNAME,
93LOC
94)
95BEGINDATA
9620,Something Not To Be Loaded,Accounting,"Virginia,USA"
97
984 ***** position的列子
99LOAD DATA
100INFILE *
101INTO TABLE DEPT
102REPLACE
103( DEPTNO position(1:2),
104DNAME position(*:16), // 这个字段的开始位置在前一字段的结束位置
105LOC position(*:29),
106ENTIRE_LINE position(1:29)
107)
108BEGINDATA
10910Accounting Virginia,USA
110
1115 ***** 使用函数 日期的一种表达 TRAILING NULLCOLS的使用
112LOAD DATA
113INFILE *
114INTO TABLE DEPT
115REPLACE
116FIELDS TERMINATED BY ','
117TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
118// 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了
119(DEPTNO,
120DNAME "upper(:dname)", // 使用函数
121LOC "upper(:loc)",
122LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等
123ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
124)
125BEGINDATA
12610,Sales,Virginia,1/5/2000
12720,Accounting,Virginia,21/6/1999
12830,Consulting,Virginia,5/1/2000
12940,Finance,Virginia,15/3/2001
130
1316 ***** 使用自定义的函数 // 解决的时间问题
132create or replace
133my_to_date( p_string in varchar2 ) return date
134as
135type fmtArray is table of varchar2(25);
136
137l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',
138'dd/mm/yyyy',
139'dd/mm/yyyy hh24:mi:ss' );
140l_return date;
141begin
142for i in 1 .. l_fmts.count
143loop
144begin
145l_return := to_date( p_string, l_fmts(i) );
146exception
147when others then null;
148end;
149EXIT when l_return is not null;
150end loop;
151
152if ( l_return is null )
153then
154l_return :=
155new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
156p_string, 'GMT', 'EST' );
157end if;
158
159return l_return;
160end;
161/
162
163LOAD DATA
164INFILE *
165INTO TABLE DEPT
166REPLACE
167FIELDS TERMINATED BY ','
168TRAILING NULLCOLS
169(DEPTNO,
170DNAME "upper(:dname)",
171LOC "upper(:loc)",
172LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数
173)
174BEGINDATA
17510,Sales,Virginia,01-april-2001
17620,Accounting,Virginia,13/04/2001
17730,Consulting,Virginia,14/04/2001 12:02:02
17840,Finance,Virginia,987268297
17950,Finance,Virginia,02-apr-2001
18060,Finance,Virginia,Not a date
181
1827 ***** 合并多行记录为一行记录
183LOAD DATA
184INFILE *
185concatenate 3 // 通过关键字concatenate 把几行的记录看成一行记录
186INTO TABLE DEPT
187replace
188FIELDS TERMINATED BY ','
189(DEPTNO,
190DNAME "upper(:dname)",
191LOC "upper(:loc)",
192LAST_UPDATED date 'dd/mm/yyyy'
193)
194BEGINDATA
19510,Sales, // 其实这3行看成一行 10,Sales,Virginia,1/5/2000
196Virginia,
1971/5/2000
198// 这列子用 continueif list="," 也可以
199告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行
200
201LOAD DATA
202INFILE *
203continueif this(1:1) = '-' // 找每行的开始是否有连接字符 - 有就把下一行连接为一行
204// 如 -10,Sales,Virginia,
205// 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
206// 其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想
207INTO TABLE DEPT
208replace
209FIELDS TERMINATED BY ','
210(DEPTNO,
211DNAME "upper(:dname)",
212LOC "upper(:loc)",
213LAST_UPDATED date 'dd/mm/yyyy'
214)
215BEGINDATA // 但是好象不能象右面的那样使用
216-10,Sales,Virginia, -10,Sales,Virginia,
2171/5/2000 1/5/2000
218-40, 40,Finance,Virginia,13/04/2001
219Finance,Virginia,13/04/2001
220
2218 ***** 载入每行的行号
222
223load data
224infile *
225into table t
226replace
227( seqno RECNUM //载入每行的行号
228text Position(1:1024))
229BEGINDATA
230fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1
231fasdjfasdfl // 此行为 2 
232
2339 ***** 载入有换行符的数据
234注意: unix 和 windows 不同 & /n
235< 1 > 使用一个非换行符的字符
236LOAD DATA
237INFILE *
238INTO TABLE DEPT
239REPLACE
240FIELDS TERMINATED BY ','
241TRAILING NULLCOLS
242(DEPTNO,
243DNAME "upper(:dname)",
244LOC "upper(:loc)",
245LAST_UPDATED "my_to_date( :last_updated )",
246COMMENTS "replace(:comments,'n',chr(10))" // replace 的使用帮助转换换行符
247)
248BEGINDATA
24910,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia
25020,Accounting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia
25130,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia
25240,Finance,Virginia,987268297,This is the FinancenOffice in Virginia
253
254< 2 > 使用fix属性
255LOAD DATA
256INFILE demo17.dat "fix 101"
257INTO TABLE DEPT
258REPLACE
259FIELDS TERMINATED BY ','
260TRAILING NULLCOLS
261(DEPTNO,
262DNAME "upper(:dname)",
263LOC "upper(:loc)",
264LAST_UPDATED "my_to_date( :last_updated )",
265COMMENTS
266)
267demo17.dat
26810,Sales,Virginia,01-april-2001,This is the Sales
269Office in Virginia
27020,Accounting,Virginia,13/04/2001,This is the Accounting
271Office in Virginia
27230,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
273Office in Virginia
27440,Finance,Virginia,987268297,This is the Finance
275Office in Virginia
276
277// 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同
278
279LOAD DATA
280INFILE demo18.dat "fix 101"
281INTO TABLE DEPT
282REPLACE
283FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
284TRAILING NULLCOLS
285(DEPTNO,
286DNAME "upper(:dname)",
287LOC "upper(:loc)",
288LAST_UPDATED "my_to_date( :last_updated )",
289COMMENTS
290)
291demo18.dat
29210,Sales,Virginia,01-april-2001,"This is the Sales
293Office in Virginia"
29420,Accounting,Virginia,13/04/2001,"This is the Accounting
295Office in Virginia"
29630,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting
297Office in Virginia"
29840,Finance,Virginia,987268297,"This is the Finance
299Office in Virginia"
300
301< 3 > 使用var属性
302LOAD DATA
303INFILE demo19.dat "var 3"
304// 3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节
305INTO TABLE DEPT
306REPLACE
307FIELDS TERMINATED BY ','
308TRAILING NULLCOLS
309(DEPTNO,
310DNAME "upper(:dname)",
311LOC "upper(:loc)",
312LAST_UPDATED "my_to_date( :last_updated )",
313COMMENTS
314)
315demo19.dat
31607110,Sales,Virginia,01-april-2001,This is the Sales
317Office in Virginia
31807820,Accounting,Virginia,13/04/2001,This is the Accounting
319Office in Virginia
32008730,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
321Office in Virginia
32207140,Finance,Virginia,987268297,This is the Finance
323Office in Virginia
324
325< 4 > 使用str属性
326// 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)||chr(10)
327
328此列中记录是以 a|rn 结束的
329select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) from dual;
330结果 7C0D0A
331
332LOAD DATA
333INFILE demo20.dat "str X'7C0D0A'"
334INTO TABLE DEPT
335REPLACE
336FIELDS TERMINATED BY ','
337TRAILING NULLCOLS
338(DEPTNO,
339DNAME "upper(:dname)",
340LOC "upper(:loc)",
341LAST_UPDATED "my_to_date( :last_updated )",
342COMMENTS
343)
344demo20.dat
34510,Sales,Virginia,01-april-2001,This is the Sales
346Office in Virginia|
34720,Accounting,Virginia,13/04/2001,This is the Accounting
348Office in Virginia|
34930,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting
350Office in Virginia|
35140,Finance,Virginia,987268297,This is the Finance
352Office in Virginia|
353
354==============================================================================
355象这样的数据 用 nullif 子句
356
35710-jan-200002350Flipper seemed unusually hungry today.
35810510-jan-200009945Spread over three meals.
359
360id position(1:3) nullif id=blanks // 这里可以是blanks 或者别的表达式
361// 下面是另一个列子 第一行的 1 在数据库中将成为 null
362LOAD DATA
363INFILE *
364INTO TABLE T
365REPLACE
366(n position(1:2) integer external nullif n='1',
367v position(3:8)
368)
369BEGINDATA
3701 10
37120lg
372------------------------------------------------------------
373
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
