sqlload的居多用法--688IT编程网

load data

infile ''

append /truncate/insert/replace

into table abc

fields terminated by ','

trailing nullcols

(a,b,c constant 100)

命令提示符下执行一下命令

sqlldr userid=test/test l

注：

infile '1.txt' //需要装载的数据文件的路径，如果数据在本文件中，则用infile *

append into table test //数据载入的表:

(1)append 表示表中有数据，加在后面

(2)INSERT 表示装入空表，有数据则停止。默认值

(3)REPLACE 原先表中如果有数据，会被删除

(4)TRUNCATE 如果要载入的数据与现在的数据相同，载入的数据替换现存的数据。

fields terminated by ','

//数据用是','；分隔，也可用其他字符，如'|'

trailing nullcols //表示如果有字段为空也要导入

c constant 100 // 如果表中字段比文本文件字段还多，则可在控制文件中加上constant关键字，并给出某个固定值

日期时间的处理： colname date "yyyy-mm-dd hh24:mi:ss"

字符串超长（超过255字符）的处理： colname char(1000) 其中1000可自行确定

导入数据时修改数据：

在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：

LOAD DATA

INFILE *

INTO TABLE modified_data

( rec_no "my_val",

region CONSTANT '31',

time_loaded "to_char(SYSDATE, 'HH24:MI')",

data1 POSITION(1:5) ":data1/100",

data2 POSITION(6:15) "upper(:data2)",

data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"

)

BEGINDATA

11111AAAAAAAAAA991201

22222BBBBBBBBBB990112

LOAD DATA

INFILE ''

BADFILE ''

APPEND

INTO TABLE mailing_list

FIELDS TERMINATED BY ","

( addr,

city,

state,

zipcode,

mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",

mailing_city "decode(:mailing_city, null, :city, :mailing_city)",

mailing_state

)

将数据导入多个表：

如:

LOAD DATA

INFILE *

REPLACE

INTO TABLE emp

WHEN empno != ' '

( empno POSITION(1:4) INTEGER EXTERNAL,

ename POSITION(6:15) CHAR,

deptno POSITION(17:18) CHAR,

mgr POSITION(20:23) INTEGER EXTERNAL

)

INTO TABLE proj

WHEN projno != ' '

( projno POSITION(25:27) INTEGER EXTERNAL,

empno POSITION(1:4) INTEGER EXTERNAL

)

导入选定的记录：

如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:

LOAD DATA

INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'

APPEND

INTO TABLE my_selective_table

WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'

(

region CONSTANT '31',

service_key POSITION(01:11) INTEG

ER EXTERNAL,

call_b_no POSITION(12:29) CHAR

)

导入时跳过某些字段：

可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：

LOAD DATA

TRUNCATE INTO TABLE T1

FIELDS TERMINAT

ED BY ','

( field1,

field2 FILLER,

field3

)

导入多行记录：

可以使用下面两个选项之一来实现将多行数据导入为一个记录:

CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.

CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.

SQL*Loader 数据的提交：

一般情况下是在导入数据文件数据后提交的。

也可以通过指定 ROWS= 参数来指定每次提交记录数。

提高 SQL*Loader 的性能：

1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。

2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。

3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。

4) 可以同时运行多个导入任务.

常规导入与direct导入方式的区别：

常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中

=====================================================================================

//////////// 注意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

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;

begin

for i in 1 .. unt

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

INFI

LE 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

replace into

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(DEPTNO,

DNAME "upper(:dname)",

LOC "upper(:loc)",

LAST_UPDATED "my_to_date( :last_updat

ed )",

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 id=blanks // 这里可以是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

688IT编程网

sqlload的居多用法

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

688IT编程网

sqlload的居多用法

发表评论

推荐文章

negotiable certificates of deposits -回复

trandate翻译 -回复

tenoke翻译 -回复

避免合同倒签的方法

cut down the transaction cost

热门文章

Truncate Table

truncate table audit_result_temp; -回复

truncate 语法

truncated incorrect double value 21分钟

truncate()函数

统计学专业辞汇英语翻译

铁塔专业术语 英语

大数据英语翻译

A MEANS FOR SAWING ELONGATE UNITS FROM A TREE-TRUN

外文翻译--拥塞控制中的算法

TOC术语中英文对照

conditionalty贸易术语 -回复

termination_date翻译

determined 翻译

truncatedsvd函数作用

tangential翻译

表面处理方面的缺陷术语(Defect Terms)

exterminate翻译

delamination翻译

antiquated翻译

最新文章

negotiable certificates of deposits -回复

teams中presenting duplicate -回复

老同志独断专行处理流程详解

quota share reinsurance contract -回复

attempted to resolve relative to -回复

concurrent modification during iteration dart

标签列表

铁塔专业术语英语