简述数据库导入Oracle SQL*Loader指南
2008-09-02 12:47:15 来源:WEB开发网LOADDATA
INFILE*
INTOTABLEmodified_data
(rec_no"my_db_sequence.nextval",
regionCONSTANT’31’,
time_loaded"to_char(SYSDATE,’HH24:MI’)",
data1POSITION(1:5)":data1/100",
data2POSITION(6:15)"upper(:data2)",
data3POSITION(16:22)"to_date(:data3,’YYMMDD’)"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOADDATA
INFILE’mail_orders.txt’
BADFILE’bad_orders.txt’
APPEND
INTOTABLEmailing_list
FIELDSTERMINATEDBY","
(addr,
city,
state,
zipcode,
mailing_addr"decode(:mailing_addr,null,:addr,:mailing_addr)",
mailing_city"decode(:mailing_city,null,:city,:mailing_city)",
mailing_state
)
将数据导入多个表:
如:
LOADDATA
INFILE*
REPLACE
INTOTABLEemp
WHENempno!=’’
(empnoPOSITION(1:4)INTEGEREXTERNAL,
enamePOSITION(6:15)char,
deptnoPOSITION(17:18)char,
mgrPOSITION(20:23)INTEGEREXTERNAL
)
INTOTABLEproj
WHENprojno!=’’
(projnoPOSITION(25:27)INTEGEREXTERNAL,
empnoPOSITION(1:4)INTEGEREXTERNAL
)
导入选定的记录:
如下例: (01) 代表第一个字符, (30:37) 代表30到37之间的字符:
赞助商链接