oracle sqlldr统制文件模板

oracle sqlldr控制文件模板

?

?

?

?

?

文章来源：http://www.cnblogs.com/twyth/archive/2009/08/16/1547141.html
? 1Sqlldr 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?
110padding: 0px; margin: 0