sql load的一点小总结.doc

1、sql load的一点小总结sql load的一点小总结 sqlldr userid=lgone/tiger control=a.ctl LOAD DATA INFILE t.dat / 要导入的文件 / INFILE tt.date / 导入多个文件 / INFILE * / 要导入的内容就在 control文件里 下面的BEGINDATA后面就是导入的内容 INTO TABLE table_name / 指定装入的表 BADFILE c:bad.txt / 指定坏文件地址 * 以下是 4种装入表的方式 APPEND / 原先的表有数据 就加在后面 / INSERT / 装载空表 如果原先的

2、表有数据 sqlloader 会停止 默认值 / REPLACE / 原先的表有数据 原先的数据会全部删除 / TRUNCATE / 指定的内容和 replace的相同 会用 truncate语句删除现存数据 * 指定的 TERMINATED可以在表的开头 也可在表的内部字段部分 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY “ / 装载这种数据: 10,lg,“lg“,“lg,lg“ / 在表中结果: 10 lg “lg“ lg,lg / TERMINATED BY X 09 / 以十六进制格式 09 表示的 / TERMINATED BY WR

3、ITESPACE / 装载这种数据: 10 lg lg TRAILING NULLCOLS * 表的字段没有对应的值时允许为空 * 下面是表的字段 ( col_1 , col_2 ,col_filler FILLER / FILLER 关键字 此列的数值不会被装载 / 如: lg,lg,not 结果 lg lg ) / 当没声明 FIELDS TERMINATED BY , 时 / ( / col_1 interger external TERMINATED BY , , / col_2 date “d-mon-yyy“ TERMINATED BY , , / col_3 char TERMI

4、NATED BY , OPTIONALLY ENCLOSED BY lg / ) / 当没声明 FIELDS TERMINATED BY ,用位置告诉字段装载数据 / ( / col_1 position(1:2), / col_2 position(3:10), / col_3 position(*:16), / 这个字段的开始位置在前一字段的结束位置 / col_4 position(1:16), / col_5 position(3:10) char(8) / 指定字段的类型 / ) BEGINDATA / 对应开始的 INFILE * 要导入的内容就在 control文件里 10,Sq

5、l,what 20,lg,show = / 注意 begindata后的数值前面不能有空格 1 * 普通装载 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY “ (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,“USA“ 20,Accounting,“Virginia,USA“ 30,Consulting,Virginia 40,Finance,Virginia 50,“Finance“,“,Virginia / loc 列将为空

6、60,“Finance“,Virginia / loc 列将为空 2 * FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x09 的情况 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE - FIELDS TERMINATED BY x09 (DEPTNO, DNAME, LOC ) BEGINDATA 10 Sales Virginia 3 * 指定不装载那一列 LOAD DATA INFILE * INTO TABLE DEPT

7、REPLACE FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY “ ( DEPTNO, FILLER_1 FILLER, / 下面的 “Something Not To Be Loaded“ 将不会被装载 DNAME, LOC ) BEGINDATA 20,Something Not To Be Loaded,Accounting,“Virginia,USA“ 4 * position的列子 LOAD DATA INFILE * INTO TABLE DEPT REPLACE ( DEPTNO position(1:2), DNAME positio

8、n(*:16), / 这个字段的开始位置在前一字段的结束位置 LOC position(*:29), ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting Virginia,USA 5 * 使用函数 日期的一种表达 TRAILING NULLCOLS 的使用 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULLCOLS / 其实下面的 ENTIRE_LINE在 BEGINDATA后面的数据中是没有直接对应 / 的列的值的 如果第一行改为 1

9、0,Sales,Virginia,1/5/2000, 就不用 TRAILING NULLCOLS了 (DEPTNO, DNAME “upper(:dname)“, / 使用函数 LOC “upper(:loc)“, LAST_UPDATED date dd/mm/yyyy, / 日期的一种表达方式 还有dd-mon-yyyy 等 ENTIRE_LINE “:deptno|:dname|:loc|:last_updated“ ) BEGINDATA 10,Sales,Virginia,1/5/2000 20,Accounting,Virginia,21/6/1999 30,Consulting,

10、Virginia,5/1/2000 40,Finance,Virginia,15/3/2001 6 * 使用自定义的函数 / 解决的时间问题 create or replace function my_to_date( p_string in varchar2 ) return date as type fmtArray is table of varchar2(25); l_fmts fmtArray := fmtArray( dd-mon-yyyy, dd-month-yyyy, dd/mm/yyyy, dd/mm/yyyy hh24:mi:ss ); l_return date; beg

11、in for i in 1 l_fmts.count loop begin l_return := to_date( p_string, l_fmts(i) ); exception when others then null; end; EXIT when l_return is not null; end loop; if ( l_return is null ) then l_return := new_time( to_date(01011970,ddmmyyyy) + 1/24/60/60 * p_string, GMT, EST ); end if; return l_return

12、; end; / LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULLCOLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“ / 使用自定义的函数 ) BEGINDATA 10,Sales,Virginia,01-april-2001 20,Accounting,Virginia,13/04/2001 30,Consulting,Virginia,1

13、4/04/2001 12:02:02 40,Finance,Virginia,987268297 50,Finance,Virginia,02-apr-2001 60,Finance,Virginia,Not a date 7 * 合并多行记录为一行记录 LOAD DATA INFILE * concatenate 3 / 通过关键字 concatenate 把几行的记录看成一行记录 INTO TABLE DEPT replace FIELDS TERMINATED BY , (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPD

14、ATED date dd/mm/yyyy ) BEGINDATA 10,Sales, / 其实这 3行看成一行 10,Sales,Virginia,1/5/2000 Virginia, 1/5/2000 / 这列子用 continueif list=“,“ 也可以 告诉 sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行 LOAD DATA INFILE * continueif this(1:1) = - / 找每行的开始是否有连接字符 - 有就把下一行连接为一行 / 如 -10,Sales,Virginia, / 1/5/2000 就是一行 10,Sales,Virginia,

15、1/5/2000 / 其中 1:1 表示从第一行开始 并在第一行结束 还有 continueif next 但 continueif list最理想 INTO TABLE DEPT replace FIELDS TERMINATED BY , (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED date dd/mm/yyyy ) BEGINDATA / 但是好象不能象右面的那样使用 -10,Sales,Virginia, -10,Sales,Virginia, 1/5/2000 1/5/2000 -40, 40,Fin

16、ance,Virginia,13/04/2001 Finance,Virginia,13/04/2001 8 * 载入每行的行号 load data infile * into table t replace ( seqno RECNUM /载入每行的行号 text Position(1:1024) BEGINDATA fsdfasj /自动分配一行号给载入 表 t 的 seqno字段 此行为 1 fasdjfasdfl / 此行为 2 . 9 * 载入有换行符的数据 注意: unix 和 windows 不同 n 1 使用一个非换行符的字符 LOAD DATA INFILE * INTO T

17、ABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULLCOLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“, COMMENTS “replace(:comments,n,chr(10)“ / replace 的使用帮助转换换行符 ) BEGINDATA 10,Sales,Virginia,01-april-2001,This is the SalesnOffice in Virginia 20,Ac

18、counting,Virginia,13/04/2001,This is the AccountingnOffice in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the ConsultingnOffice in Virginia 40,Finance,Virginia,987268297,This is the FinancenOffice in Virginia 2 使用 fix属性 LOAD DATA INFILE demo17.dat “ix 101“ INTO TABLE DEPT REPLACE FIE

19、LDS TERMINATED BY , TRAILING NULLCOLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“, COMMENTS ) demo17.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia

20、30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia 40,Finance,Virginia,987268297,This is the Finance Office in Virginia / 这样装载会把换行符装入数据库 下面的方法就不会 但要求数据的格式不同 LOAD DATA INFILE demo18.dat “ix 101“ INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY “

21、TRAILING NULLCOLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“, COMMENTS ) demo18.dat 10,Sales,Virginia,01-april-2001,“This is the Sales Office in Virginia“ 20,Accounting,Virginia,13/04/2001,“This is the Accounting Office in Virginia“ 30,Consulting,Vi

22、rginia,14/04/2001 12:02:02,“This is the Consulting Office in Virginia“ 40,Finance,Virginia,987268297,“This is the Finance Office in Virginia“ 3 使用 var属性 LOAD DATA INFILE demo19.dat “var 3“ / 3 告诉每个记录的前 3个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节 INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULL

23、COLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“, COMMENTS ) demo19.dat 07110,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia 07820,Accounting,Virginia,13/04/2001,This is the Accounting Office in Virginia 08730,Consulting,Virginia,1

24、4/04/2001 12:02:02,This is the Consulting Office in Virginia 07140,Finance,Virginia,987268297,This is the Finance Office in Virginia 4 使用 str属性 / 最灵活的一中 可定义一个新的行结尾符 win 回车换行 : chr(13)|chr(10) 此列中记录是以 a|rn 结束的 select utl_raw.cast_to_raw(|chr(13)|chr(10) from dual; 结果 7C0D0A LOAD DATA INFILE demo20.da

25、t “str X7C0D0A“ INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULLCOLS (DEPTNO, DNAME “upper(:dname)“, LOC “upper(:loc)“, LAST_UPDATED “my_to_date( :last_updated )“, COMMENTS ) demo20.dat 10,Sales,Virginia,01-april-2001,This is the Sales Office in Virginia| 20,Accounting,Virginia,13/04/200

26、1,This is the Accounting Office in Virginia| 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting Office in Virginia| 40,Finance,Virginia,987268297,This is the Finance Office in Virginia| = 象这样的数据 用 nullif 子句 10-jan-200002350Flipper seemed unusually hungry today. 10510-jan-200009945Spread over three meals. id position(1:3) nullif id=blanks / 这里可以是 blanks 或者别的表达式 / 下面是另一个列子 第一行的 1 在数据库中将成为 null LOAD DATA INFILE * INTO TABLE T REPLACE (n position(1:2) integer external nullif n=1, v position(3) ) BEGINDATA 1 10 20lg - 如果是英文的日志 格式，可能需要修改环境变量 nls_lang or nls_date_format