sqlloader使用指南.docx
《sqlloader使用指南.docx》由会员分享，可在线阅读，更多相关《sqlloader使用指南.docx（22页珍藏版）》请在冰点文库上搜索。
sqlloader使用指南
OracleSQL*Loader使用指南
整理：
Angel.John
SQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载.
如何使用SQL*Loader工具
我们可以用Oracle的sqlldr工具来导入数据。
例如:
sqlldrscott/tigercontrol=loader.ctl
控制文件（loader.ctl）将加载一个外部数据文件（含分隔符）.loader.ctl如下:
loaddata
infile'c:
\data\mydata.csv'
intotableemp
fieldsterminatedby","optionallyenclosedby'"'
（empno,empname,sal,deptno）
mydata.csv如下:
10001,"ScottTiger",1000,40
10002,"FrankNaude",500,20
下面是一个指定记录长度的示例控制文件。
"*"代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。
loaddata
infile*
replace
intotabledepartments
（deptposition（02:
05）char（4）,
deptnameposition（08:
27）char（20）
）
begindata
COSCCOMPUTERSCIENCE
ENGLENGLISHLITERATURE
MATHMATHEMATICS
POLYPOLITICALSCIENCE
Unloader这样的工具
Oracle没有提供将数据导出到一个文件的工具。
但是，我们可以用SQL*Plus的select及format数据来输出到一个文件：
setechooffnewpage0space0pagesize0feedoffheadofftrimspoolon
spooloradata.txt
selectcol1||','||col2||','||col3
fromtab1
wherecol2='XYZ';
spooloff
另外，也可以使用使用UTL_FILEPL/SQL包处理:
remRemembertoupdateinitSID.ora,utl_file_dir='c:
\oradata'parameter
declare
fputl_file.file_type;
begin
fp:
=utl_file.fopen（'c:
\oradata','tab1.txt','w'）;
utl_file.putf（fp,'%s,%s\n','TextField',55）;
utl_file.fclose（fp）;
end;
/
当然你也可以使用第三方工具，如SQLWays,TOADforQuest等。
加载可变长度或指定长度的记录
如：
LOADDATA
INFILE*
INTOTABLEload_delimited_data
FIELDSTERMINATEDBY","OPTIONALLYENCLOSEDBY'"'
TRAILINGNULLCOLS
（data1,
data2
）
BEGINDATA
11111,AAAAAAAAAA
22222,"A,B,C,D,"
下面是导入固定位置（固定长度）数据示例：
LOADDATA
INFILE*
INTOTABLEload_positional_data
（data1POSITION（1:
5）,
data2POSITION（6:
15）
）
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
固定程度数据load可以通过position来指定
例子：
LOADDATA
INFILE'zipcodes.dat'
REPLACEINTOTABLEzipcodes（
city_namePOSITION（1:
10）CHAR（10）,
zip_codePOSITION（*:
15）CHAR,
state_abbrPOSITION（17-18）CHAR
）
city_namePOSITION（1:
10）CHAR（10）,
Thecitynamebeginsatposition1andgoesthroughposition10.Inthiscase,
thelengthwasspecifiedredundantlyinthedatatypespecification.Acolon
wasusedtoseparatethebeginningandendingvalues
zip_codePOSITION（*:
15）CHAR,
The*indicatesthattheZIPCodebeginswiththefirstbytefollowingthecity
name.Theendingpositionhasbeenhardcodedasthe15thbyte.Acolonhas
beenusedtoseparatethetwovalues.Nolengthhasbeenspecifiedwiththe
datatype,soSQL*Loaderwillcomputethelengthasending–beginning+1.
state_abbrPOSITION（17–18）CHAR
Thestateabbreviationhasbeenspecifiedinabsoluteterms.Thistime,a
hyphenhasbeenusedtoseparatethetwovalues.Again,nolengthhasbeen
specifiedwiththedatatype,soSQL*Loaderwillderivethelengthbasedonthe
beginningandendingvalues.
跳过数据行：
可以用"SKIPn"关键字来指定导入时可以跳过多少行数据。
如：
LOADDATA
INFILE*
INTOTABLEload_positional_data
SKIP5
（data1POSITION（1:
5）,
data2POSITION（6:
15）
）
BEGINDATA
11111AAAAAAAAAA
22222BBBBBBBBBB
导入数据时修改数据：
在导入数据到数据库时，可以修改数据。
注意，这仅适合于常规导入，并不适合direct导入方式.如：
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之间的字符:
LOADDATA
INFILE'mydata.dat'BADFILE'mydata.bad'DISCARDFILE'mydata.dis'
APPEND
INTOTABLEmy_selective_table
WHEN（01）<>'H'and（01）<>'T'and（30:
37）='19991217'
（
regionCONSTANT'31',
service_keyPOSITION（01:
11）INTEGEREXTERNAL,
call_b_noPOSITION（12:
29）CHAR
）
导入时跳过某些字段：
可用POSTION（x:
y）来分隔数据.在Oracle8i中可以通过指定FILLER 字段实现。
FILLER字段用来跳过、忽略导入数据文件中的字段.如：
LOADDATA
TRUNCATEINTOTABLET1
FIELDSTERMINATEDBY','
（field1,
field2FILLER,
field3
）
导入多行记录：
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE:
-usewhenSQL*Loadershouldcombinethesamenumberofphysicalrecordstogethertoformonelogicalrecord.
CONTINUEIF-useifaconditionindicatesthatmultiplerecordsshouldbetreatedasone.Eg.byhavinga'#'characterincolumn1.
增加些新内容：
1：
当出现物理折行的时候可以使用CONTINUEIFLAST来“合并物理行”
LOADDATA
INFILE'data15.dat'
REPLACECONTINUEIFLAST=','
INTOTABLEmichigan_features
（
feature_nameCHARTERMINATEDBY','ENCLOSEDBY'"',
feature_typeCHARTERMINATEDBY','ENCLOSEDBY'"',
countyCHARTERMINATEDBY','ENCLOSEDBY'"',
latitudeCHARTERMINATEDBY','ENCLOSEDBY'"',
longitudeCHARTERMINATEDBY','ENCLOSEDBY'"',
elevationINTEGEREXTERNALTERMINATEDBY','ENCLOSEDBY'"'
）
Begindata
"GraceHarbor","bay","Keweenaw","475215N",
"0891330W","601"
"MinongRidge","ridge","Keweenaw","480115N","0885348W","800"
"SiskiwitLake",
"lake",
"Keweenaw",
"480002N",
"0884745W",
"659"
例子：
SQL>connhr/hr;
已连接。
SQL>createtablemichigan_features（feature_namevarchar2（100）,
2feature_typevarchar2（100）,
3countyvarchar2（100）,
4latitudevarchar2（100）,
5longitudevarchar2（100）,
6elevationnumber）;
表已创建。
SQL>hostsqlldrhr/hrcontrol=c:
\data\loader.ctllog=c:
\data\load.log;
SQL*Loader:
Release10.2.0.1.0-Productionon星期二8月1216:
18:
342008
Copyright（c）1982,2005,Oracle.Allrightsreserved.
达到提交点-逻辑记录计数5
SQL>select*frommichigan_features;
FEATURE_NAME
---------------------------------------------------------------------------
GraceHarbor
MinongRidge
SiskiwitLake
2：
对记录字段记录进行拆分：
LOADDATA
INFILE*
REPLACEINTOTABLEmichigan_features
（
feature_nameCHARTERMINATEDBY',',
feature_typeCHARTERMINATEDBY',',
countyCHARTERMINATEDBY',"',
lat_degreesINTEGEREXTERNAL
（2）,
lat_minutesINTEGEREXTERNAL
（2）,
lat_secondsINTEGEREXTERNAL
（2）,
lat_directionCHARTERMINATEDBY'","',
long_degreesINTEGEREXTERNAL（3）,
long_minutesINTEGEREXTERNAL
（2）,
long_secondsINTEGEREXTERNAL
（2）,
long_directionCHARTERMINATEDBY'"'
）
Begindata
"WagnerFalls","falls","Alger","462316N","0863846W"
"TanneryFalls","falls","Alger","462456N","0863737W"
例子：
SQL>connhr/hr
已连接。
SQL>setwrapoff
SQL>createtablemichigan_features（
2feature_namevarchar2（100）,
3feature_typevarchar2（100）,
4countyvarchar2（100）,
5lat_degreesnumber,
6lat_minutesnumber,
7lat_secondsnumber,
8lat_directionvarchar2（100）,
9long_degreesnumber,
10long_minutesnumber,
11long_secondsnumber,
12long_directionvarchar2（100））;
表已创建。
SQL>hostsqlldrhr/hrcontrol=c:
\data\loader.ctllog=c:
\data\load.log;
SQL*Loader:
Release10.2.0.1.0-Productionon星期二8月1216:
42:
572008
Copyright（c）1982,2005,Oracle.Allrightsreserved.
达到提交点-逻辑记录计数2
SQL>select*frommichigan_features;
行将被截断
FEATURE_NAME
--------------------------------------------------------------------------------
"WagnerFalls"
"TanneryFalls"
3，使用sql表达式，在这个例子中使用了to_number函数，同样可以使用自定义的函数
LOADDATA
INFILE*
REPLACEINTOTABLEbook
fieldsterminatedby","optionallyenclosedby'"'
（
book_title,
book_price
"GREATEST（TO_NUMBER（:
book_price）/100*TO_NUMBER（:
book_pages*0.10））",
book_pages
）
Begindata
OracleEssentials,3495,355
SQL*Plus:
TheDefinitiveGuide,3995,502
OraclePL/SQLProgramming,4495,87
Oracle8DesignTips,1495,115
例子：
SQL>createtableBOOK
2（
3BOOK_IDNUMBER,
4BOOK_TITLEVARCHAR2（35）,
5BOOK_PRICENUMBER,
6BOOK_PAGESNUMBER
7）;
表已创建。
SQL>sqlldrhr/hrcontrol=c:
\data\loader.ctllog=c:
\data\log.log
SP2-0734:
未知的命令开头"sqlldrhr/..."-忽略了剩余的行。
SQL>hostsqlldrhr/hrcontrol=c:
\data\loader.ctllog=c:
\data\log.log;
SQL*Loader:
Release10.2.0.1.0-Productionon星期四8月1409:
19:
032008
Copyright（c）1982,2005,Oracle.Allrightsreserved.
达到提交点-逻辑记录计数3
达到提交点-逻辑记录计数4
SQL>select*frombook;
BOOK_IDBOOK_TITLEBOOK_PRICEBOOK_PAGES
-----------------------------------------------------------------
OracleEssentials1240.725355
SQL*Plus:
TheDefinitiveGuide2005.49502
OraclePL/SQLProgramming391.06587
Oracle8DesignTips171.925115
SQL>
4，字符串转换：
ThefollowingLOADstatementprovidesanexampleofhowtospecifyacharacter
set.Inthiscase,thecharactersetisanEBCDICcharactersetnamed
WE8EBCDIC37C.LookfortheCHARACTERSETclauseinthesecondlineofthe
LOADcommand:
LOADDATA
CHARACTERSET'WE8EBCDIC37C'
INFILE'book_prices.dat'
REPLACEINTOTABLEbook
（
book_titlePOSITION
（1）CHAR（35）,
book_pricePOSITION（37）
"GREATEST（TO_NUMBER（:
book_price）/100,
TO_NUMBER（:
book_pages*0.10））",
book_pagesPOSITION（42）INTEGEREXTERNAL（3）,
book_
5，设置行结束标志（STR），在导入大对象时很有用！
LOADDATA
INFILE'c:
\data\mydata.csv'"str'*****'"
REPLACEINTOTABLEbook
fieldsterminatedby","optionallyenclosedby'"'
（
book_title,
book_price,
book_pages,
book_id
）
Mydata.csv文件内容：
OracleEssentialsbbbbbbbbbbbbb
3495,355,1*****
SQL*Plus:
TheDefinitive
Guide,3995,502,2*****
OraclePL/SQL
Programming,4495,87,3*****
Oracle8DesignTips,1495,115,4*****
例子：
SQL>truncatetablebook;
表被截断。
SQL>hostsqlldrhr/hrcon
升级会员 