sqlldr中怎么装载数据

56次阅读

共计 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 行业资讯频道了解更多相关知识。

正文完