共计 8204 个字符，预计需要花费 21 分钟才能阅读完成。
sqlldr 中怎么装载数据，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
1）把 excel 数据存为 t.txt 文件，注意文件后缀名为 .txt 1 jhchen 11/07/2005 20:04:00 2005-11-7 20:04 2 jhchen 11/07/2005 20:04:00 2005-11-7 20:04 3 jhchen 11/07/2005 20:04:00 2005-11-7 20:04
2）SQL create table t_load(id number, name varchar2(10), dat1 date, dat2 date, dat3 date ); Table created.
3）控制文件 t.ctl 如下 load data infile t.txt badfile t.bad append into table t_load fields terminated by X 09 trailing nullcols (id , name , dat1 date mm/dd/yyyy hh34:mi:ss , dat2 date yyyy-mm-dd hh34:mi:ss) 其中，X 09 是制表符，TAB 键，trailing nullcols 表示如表的字段没有对应的值时允许为空。
4）C:\Documents and Settings\cjh sqlldr userid=jhchen/oracle control=t.ctl SQL*Loader: Release 9.2.0.6.0 – Production on 星期一 11 月 7 20:20:00 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 达到提交点，逻辑记录计数 3
5）SQL select * from t_load; ID NAME DAT1 DAT2 DAT3 ———- ———- ——— ——— ——— 1 jhchen 07-NOV-05 07-NOV-05 2 jhchen 07-NOV-05 07-NOV-05 3 jhchen 07-NOV-05 07-NOV-05 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 // 装载空表 如果原先的表有数据 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 WRITESPACE // 装载这种数据: 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 dd-mon-yyy] TERMINATED BY , , // col_3 [char] TERMINATED 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,Sql,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 列将为空 60, Finance ,,Virginia // loc 列将为空 2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x 09 的情况 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY WHITESPACE — FIELDS TERMINATED BY x 09 (DEPTNO, DNAME, LOC) BEGINDATA 10 Sales Virginia 3 ***** 指定不装载那一列 LOAD DATA INFILE * INTO TABLE DEPT 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 position(*: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 后面的数据中是没有直接对应 // 的列的值的 如果第一行改为 10,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,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 hh34:mi:ss l_return date; begin 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; 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,14/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_UPDATED 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,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,Finance,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 /n
1 使用一个非换行符的字符 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) , COMMENTS replace(:comments, \n ,chr(10)) // replace 的使用帮助转换换行符 ) BEGINDATA 10,Sales,Virginia,01-april-2001,This is the Sales\nOffice in Virginia 20,Accounting,Virginia,13/04/2001,This is the Accounting\nOffice in Virginia 30,Consulting,Virginia,14/04/2001 12:02:02,This is the Consulting\nOffice in Virginia 40,Finance,Virginia,987268297,This is the Finance\nOffice in Virginia
2 使用 fix 属性 LOAD DATA INFILE demo17.dat fix 101 INTO TABLE DEPT REPLACE FIELDS 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 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 fix 101 INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY 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,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
3 使用 var 属性 LOAD DATA INFILE demo19.dat var 3 // 3 告诉每个记录的前 3 个字节表示记录的长度 如第一个记录的 071 表示此记录有 71 个字节 INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , TRAILING NULLCOLS (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,14/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|\r\n 结束的 select utl_raw.cast_to_raw(| ||chr(13)||chr(10)) from dual; 结果 7C0D0A LOAD DATA INFILE demo20.dat str X 7C0D0A 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/2001,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 // 这里可以是 blanks 或者别的表达式 // 下面是另一个列子 第一行的 1 在数据库中将成为 null LOAD DATA INFILE * INTO TABLE T REPLACE (n position(1:2) integer external nullif n= 1 , v position(3:8) ) BEGINDATA 1 10 20lg
关于 sqlldr 中怎么装载数据问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。
