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