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