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

oracle sqlldr控制文件模板

公告
