oracle 使用杂记二

下面的是关于sql*loader 的使用的一点总结 有些是来自itpub上的一些网友的总结

大部分是oracle专家高级编程上的实例 只是我实践以后写的结果

sqlldr userid=lgone/tiger control=actl

LOAD DATA

INFILE tdat // 要导入的文件

// INFILE ttdat// 导入多个文件

// INFILE *// 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

INTO TABLE table_name// 指定装入的表

// into table t_name partition (p_)分区的载入

BADFILE c:\badtxt // 指定坏文件地址

************* 以下是种装入表的方式

APPEND // 原先的表有数据 就加在后面

// INSERT// 装载空表 如果原先的表有数据 sqlloader会停止 默认值

// REPLACE // 原先的表有数据 原先的数据会全部删除

// TRUNCATE// 指定的内容和replace的相同 会用truncate语句删除现存数据

SKIP 可以用 SKIP n 关键字来指定导入时可以跳过多少行数据

*************指定的TERMINATED可以在表的开头 也可在表的内部字段部分

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY

// 装载这种数据: lglglglg

// 在表中结果: lglg lglg

// TERMINATED BY X // 以十六进制格式 表示的

// TERMINATED BY WRITESPACE // 装载这种数据: lg lg

TRAILING NULLCOLS************* 表的字段没有对应的值时允许为空

*************下面是表的字段

(

col_ col_ col_filler FILLER // FILLER 关键字 此列的数值不会被装载

// 如: lglgnot结果 lglg

)

// 当没声明FIELDS TERMINATED BY 时

// (

// col_ [interger external] TERMINATED BY

// col_ [date ddmonyyy] TERMINATED BY

// col_ [char]TERMINATED BY OPTIONALLY ENCLOSED BY lg

// )

// 当没声明FIELDS TERMINATED BY 用位置告诉字段装载数据

// (

// col_ position(:)

// col_ position(:)

// col_ position(*:)// 这个字段的开始位置在前一字段的结束位置

// col_ position(:)

// col_ position(:) char()// 指定字段的类型

// )

BEGINDATA// 对应开始的 INFILE *要导入的内容就在control文件里

Sqlwhat

lgshow

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

////////////注意begindata后的数值前面不能有空格

***** 普通装载

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY

(DEPTNO

DNAME

LOC

)

BEGINDATA

SalesUSA

AccountingVirginiaUSA

ConsultingVirginia

FinanceVirginia

FinanceVirginia// loc 列将为空

FinanceVirginia// loc 列将为空

***** FIELDS TERMINATED BY WHITESPACE 和FIELDS TERMINATED BY x 的情况

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY WHITESPACE

FIELDS TERMINATED BY x

(DEPTNO

DNAME

LOC

)

BEGINDATA

SalesVirginia

***** 指定不装载那一列 还可用 POSTION(x:y) 来分隔数据

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY

( DEPTNO

FILLER_ FILLER// 下面的 Something Not To Be Loaded 将不会被装载

DNAME

LOC

)

BEGINDATA

Something Not To Be LoadedAccountingVirginiaUSA

***** position的列子

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

( DEPTNOposition(:)

DNAME position(*:)// 这个字段的开始位置在前一字段的结束位置

LOC position(*:)

ENTIRE_LINE position(:)

)

BEGINDATA

AccountingVirginiaUSA

***** 使用函数日期的一种表达TRAILING NULLCOLS的使用

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS //其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应

//的列的值的 如果第一行改为 SalesVirginia// 就不用TRAILING NULLCOLS了

(DEPTNO

DNAMEupper(:dname)// 使用函数

LOCupper(:loc)

LAST_UPDATED date dd/mm/yyyy// 日期的一种表达方式 还有ddmonyyyy 等

ENTIRE_LINE:deptno||:dname||:loc||:last_updated

)

BEGINDATA

SalesVirginia//

AccountingVirginia//

ConsultingVirginia//

FinanceVirginia//

***** 使用自定义的函数 // 解决的时间问题

使用函数这仅适合于常规导入并不适合 direct导入方式 i可能可以

create or replace

function my_to_date( p_string in varchar ) return date

as

type fmtArray is table of varchar();

l_fmtsfmtArray := fmtArray( ddmonyyyy ddmonthyyyy

dd/mm/yyyy

dd/mm/yyyy hh:mi:ss );

l_return date;

begin

for i in 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(ddmmyyyy) + /// *

p_string GMT EST );

end if;

return l_return;

end;

/

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated ) // 使用自定义的函数

)

BEGINDATA

SalesVirginiaapril

AccountingVirginia//

ConsultingVirginia// ::

FinanceVirginia

FinanceVirginiaapr

FinanceVirginiaNot a date

***** 合并多行记录为一行记录

LOAD DATA

INFILE *

concatenate // 通过关键字concatenate 把几行的记录看成一行记录

INTO TABLE DEPT

replace

FIELDS TERMINATED BY

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED date dd/mm/yyyy

)

BEGINDATA

Sales// 其实这行看成一行SalesVirginia//

Virginia

//

// 这列子用 continueif list= 也可以

告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行

LOAD DATA

INFILE *

continueif this(:) = // 找每行的开始是否有连接字符 有就把下一行连接为一行

// 如 SalesVirginia

//// 就是一行SalesVirginia//

// 其中: 表示从第一行开始 并在第一行结束还有continueif next 但continueif list最理想

INTO TABLE DEPT

replace

FIELDS TERMINATED BY

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED date dd/mm/yyyy

)

BEGINDATA// 但是好象不能象右面的那样使用

SalesVirginiaSalesVirginia

// //

FinanceVirginia//

FinanceVirginia//

================================ 用别的语言帮助解决的方法

txt文件中的每行作为一个记录插入到数据库中的一条记录文件是定长的

插入数据记录是

可以把换行符作为一个分隔符来处理

到去下载一个activeperl MSI

安装 PERL

你的文本文件示例testold

a b c d e

f g

我的PERL程序testpl

$mycount=;

open(FILE_OLDTESTOLD);

open(FILE_NEW>TESTNEW);

while(<FILE_OLD>)

{

chomp;

if ($mycount% == )

{print FILE_NEW $_ ;}

else

{print FILE_NEW $_\n;}

$mycount++;

}

在命令窗口下执行 perl testpl

得到一个新的文本文件testnew内容如下

a b c d e f g

load data

infile testtxt

concatenate()

into table aa

fields terminated by whitespace

(FIELDFIELDFIELDFIELDFIELDFIELDFIELD)

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

***** 载入每行的行号

load data

infile *

into table t

replace

( seqnoRECNUM //载入每行的行号

text Position(:))

BEGINDATA

fsdfasj //自动分配一行号给载入 表t 的seqno字段此行为

fasdjfasdfl //此行为

***** 载入有换行符的数据

注意: unix 和 windows 不同\\n&/n

还可以用 dbms_lob和 bfile 看一个文件的回车 换行 等其他特殊字符

< > 使用一个非换行符的字符

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS replace(:comments\nchr()) //replace 的使用帮助转换换行符

)

BEGINDATA

SalesVirginiaaprilThis is the Sales\nOffice in Virginia

AccountingVirginia//This is the Accounting\nOffice in Virginia

ConsultingVirginia// ::This is the Consulting\nOffice in Virginia

FinanceVirginiaThis is the Finance\nOffice in Virginia

< > 使用fix属性

LOAD DATA

INFILE demodat fix

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS

)

demodat

SalesVirginiaaprilThis is the Sales

Office in Virginia

AccountingVirginia//This is the Accounting

Office in Virginia

ConsultingVirginia// ::This is the Consulting

Office in Virginia

FinanceVirginiaThis is the Finance

Office in Virginia

//这样装载会把换行符装入数据库下面的方法就不会 但要求数据的格式不同

LOAD DATA

INFILE demodat fix

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS

)

demodat

SalesVirginiaaprilThis is the Sales

Office in Virginia

AccountingVirginia//This is the Accounting

Office in Virginia

ConsultingVirginia// ::This is the Consulting

Office in Virginia

FinanceVirginiaThis is the Finance

Office in Virginia

< > 使用var属性

LOAD DATA

INFILE demodat var

// 告诉每个记录的前个字节表示记录的长度 如第一个记录的 表示此记录有 个字节

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS

)

demodat

SalesVirginiaaprilThis is the Sales

Office in Virginia

AccountingVirginia//This is the Accounting

Office in Virginia

ConsultingVirginia// ::This is the Consulting

Office in Virginia

FinanceVirginiaThis is the Finance

Office in Virginia

< > 使用str属性

// 最灵活的一中 可定义一个新的行结尾符win 回车换行 : chr()||chr()

此列中记录是以 a|\r\n 结束的

select utl_rawcast_to_raw(|||chr()||chr()) from dual;

结果 CDA

LOAD DATA

INFILE demodat str XCDA

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS

)

demodat

SalesVirginiaaprilThis is the Sales

Office in Virginia|

AccountingVirginia//This is the Accounting

Office in Virginia|

ConsultingVirginia// ::This is the Consulting

Office in Virginia|

FinanceVirginiaThis is the Finance

Office in Virginia|

***** 将数据导入多个表

LOAD DATA

INFILE *

REPLACE

INTO TABLE emp WHEN empno !=

(

empno POSITION(:) INTEGER EXTERNAL

ename POSITION(:) CHAR

deptno POSITION(:) CHAR

mgr POSITION(:) INTEGER EXTERNAL

)

INTO TABLE projWHEN projno !=

(

projno POSITION(:) INTEGER EXTERNAL

empno POSITION(:) INTEGER EXTERNAL

)

***** 转载 RAW 数据 或 转载长字段

options(bindsize=rows=)

load data

infile mydata fix // * =是小于K 的最大因子

concatenate

preserve blanks

into table foo

append

(id constant bigdata raw())

***** 转载 LOB 数据

用 dbms_lob dbms的数据要在服务器上 通过网络的不行

drop table demo;

create or replace directory dir as c:\temp\;

create or replace directory dir as c:\temp\;

create table demo

( idint primary key

theClob clob

)

/

host echo Hello World\! > c:\temp\testtxt

declare

l_clobclob;

l_bfile bfile;

begin

insert into demo values ( empty_clob() )

returning theclob into l_clob;

l_bfile := bfilename( DIR testtxt ); DIR 要大写

dbms_lobfileopen( l_bfile );

dbms_lobloadfromfile( l_clob l_bfile

dbms_lobgetlength( l_bfile ) );

dbms_lobfileclose( l_bfile );

end;

/

select dbms_lobgetlength(theClob) theClob from demo

/

用 sqlldr在同一行的LOBlob数据在同一个数据文件中

LOAD DATA

INFILE demodat str XCDA

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY

TRAILING NULLCOLS

(DEPTNO

DNAMEupper(:dname)

LOCupper(:loc)

LAST_UPDATED my_to_date( :last_updated )

COMMENTS char()

)

SalesVirginiaaprilThis is the Sales

Office in Virginia|

AccountingVirginia//This is the Accounting

Office in Virginia|

ConsultingVirginia// ::This is the Consulting

Office in Virginia|

FinanceVirginiaThis is the Finance

Office in Virginia it has embedded commas and is

much longer then the other comments fieldIf you

feel the need to add double quoted text in here like

this: You will need to double up those quotes! to

preserve them in the stringThis field keeps going for upto

bytes or until we hit the magic end of record marker

the | followed by a end of line it is right here >|

用 sqlldr不在同一行的LOB就是lob数据在单独的文件中

create table lob_demo

( owner varchar()

timestamp date

filenamevarchar()

textclob

)

/

LOAD DATA /////////// window 的

INFILE *

REPLACE

INTO TABLE LOB_DEMO

( ownerposition(:)

timestampposition(:) to_date(:timestamp||mmm/dd/yyyyhh:miam)

filename position(:) 下面的LOB的filename是从这里来的

text LOBFILE(filename) TERMINATED BY EOF

)

BEGINDATA

//:p BUILTIN\Administrators demolog

// 这是 windows 下面的情况 上面的数据是用 dir /q/n 看见的情况*******

///// unix 下的情况

用 ls l 得到上面数据的情况

控制文件就改下时间的格式

lob 到对象列

create table image_load( id number name varchar()

image ordsysordimage )

/

desc ordsysordimage

desc ordsysordsource

LOAD DATA

INFILE *

INTO TABLE T

replace

fields terminated by

(

id

name

fiel_name filler

image column object

(

source column object

(

localdatalobfile(file_name) terminated by bof

nullif file_name=NONE

)

)

)

begindata

iconsiconsgif

***** 转载varrays /嵌套表

create type myArrayType

as varray() of number()

/

create table t

( x int primary key y myArrayType )

/

LOAD DATA

INFILE *

INTO TABLE T

replace

fields terminated by

(

x

y_cnt FILLER

y varray count (y_cnt)

(

y

)

)

BEGINDATA

create or replace type myTableType

as table of number()

/

create table t

( x int primary key y myTableType )

nested table y store as y_tab

/

LOAD DATA

INFILE *

INTO TABLE T

replace

fields terminated by

(

x

y nested table count (CONSTANT )

(

y

)

)

BEGINDATA

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

象这样的数据 用 nullif 子句

janFlipper seemed unusually hungry today

janSpread over three meals

id position(:) nullif id=blanks// 这里可以是blanks 或者别的表达式

//下面是另一个列子 第一行的 在数据库中将成为 null

LOAD DATA

INFILE *

INTO TABLE T

REPLACE

(n position(:) integer external nullif n=

v position(:)

)

BEGINDATA

lg

如果是英文的日志 格式可能需要修改环境变量 nls_lang or nls_date_format