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 //指定装入的表8BADFILE '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,lg20//TERMINATED BY X '09'//以十六进制格式 '09' 表示的21//TERMINATED BY WRITESPACE//装载这种数据: 10 lg lg22

23

TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空 

24

25

************* 下面是表的字段 

26

( 

27

col_1 , col_2 ,col_filler FILLER //FILLER 关键字 此列的数值不会被装载28//如: lg,lg,not 结果 lg lg29) 

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文件里4610,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 列将为空6860,"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" 将不会被装载92DNAME, 

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), //这个字段的开始位置在前一字段的结束位置105LOC 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)", //使用函数121LOC "upper(:loc)", 

122

LAST_UPDATED date 'dd/mm/yyyy', //日期的一种表达方式 还有'dd-mon-yyyy' 等123ENTIRE_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 ***** 使用自定义的函数 //解决的时间问题132create 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 把几行的记录看成一行记录186INTO 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/2000196Virginia, 

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/2000206//其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想207INTO 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 //载入每行的行号228text Position(1:1024)) 

229

BEGINDATA 

230

fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1231fasdjfasdfl //此行为 2 

232

233

9 ***** 载入有换行符的数据 

234

注意: unix 和 windows 不同 & /n 

235

 使用一个非换行符的字符 

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

 使用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 Sales293Office in Virginia"29420,Accounting,Virginia,13/04/2001,"This is the Accounting295Office in Virginia"29630,Consulting,Virginia,14/04/2001 12:02:02,"This is the Consulting297Office in Virginia"29840,Finance,Virginia,987268297,"This is the Finance299Office in Virginia"300

301

 使用var属性 

302

LOAD DATA 

303

INFILE demo19.dat "var 3" 

304

//3 告诉每个记录的前3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节305INTO 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

 使用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 在数据库中将成为 null362LOAD 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