【网学网提醒】：本文主要为网学会员提供SQLLoader的详细语法及实例，希望对需要SQLLoader的详细语法及实例网友有所帮助，学习一下!
sqlload的一点小总结
以下是本人对sqlload的一些运用总结不足之处还多包涵
sqlldruserid=lgone/tigercontrol=a.ctldirect=trueparallel=true
LOADDATA
INFILE't.dat'//要导入的文件
INFILE'tt.date'//导入多个文件
INFILE*//要导入的内容就在control文件里下面的BEGINDATA后面就是导入的内容
INTOTABLEtable_name//指定装入的表
BADFILE'c:\bad.txt'//指定坏文件地址
*************以下是4种装入表的方式****************************************
APPEND//原先的表有数据就加在后面
INSERT//装载空表如果原先的表有数据sqlloader会停止默认值
REPLACE//原先的表有数据原先的数据会全部删除
TRUNCATE//指定的内容和replace的相同会用truncate语句删除现存数据
*************指定的TERMINATED可以在表的开头也可在表的内部字段部分************************************
FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'
//装载这种数据:10,lg,"""lg""","lg,lg"
//在表中结果:10lg"lg"lg,lg
//TERMINATEDBYX'09'//以十六进制格式'09'表示的
//TERMINATEDBYWRITESPACE//装载这种数据:10lglg
TRAILINGNULLCOLS*************表的字段没有对应的值时允许为空
*************下面是表的字段
(
col_1,col_2,col_fillerFILLER//FILLER关键字此列的数值不会被装载
//如:lg,lg,not结果lglg
)
//当没声明FIELDSTERMINATEDBY','时
//(
//col_1[intergerexternal]TERMINATEDBY',',
//col_2[date"dd-mon-yyy"]TERMINATEDBY',',
//col_3[char]TERMINATEDBY','OPTIONALLYENCLOSEDBY'lg'
//)
//当没声明FIELDSTERMINATEDBY','用位置告诉字段装载数据
//(
//col_1position(1:2),
//col_2position(3:10),
//col_3position(*:16),//这个字段的开始位置在前一字段的结束位置
//col_4position(1:16),
//col_5position(3:10)char(8)//指定字段的类型
//)
BEGINDATA//对应开始的INFILE*要导入的内容就在control文件里
10,Sql,what
20,lg,show
=====================================================================================
////////////注意begindata后的数值前面不能有空格
1*****普通装载
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'
(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*****FIELDSTERMINATEDBYWHITESPACE和FIELDSTERMINATEDBYx'09'的情况
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBYWHITESPACE
--FIELDSTERMINATEDBYx'09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10SalesVirginia
3*
****指定不装载那一列
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'
(DEPTNO,
FILLER_1FILLER,//下面的"SomethingNotToBeLoaded"将不会被装载
DNAME,
LOC
)
BEGINDATA
20,SomethingNotToBeLoaded,Accounting,"Virginia,USA"
4*****position的列子
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
(DEPTNOposition(1:2),
DNAMEposition(*:16),//这个字段的开始位置在前一字段的结束位置
LOCposition(*:29),
ENTIRE_LINEposition(1:29)
)
BEGINDATA
10AccountingVirginia,USA
5*****使用函数日期的一种表达TRAILINGNULLCOLS的使用
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS//其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应
//的列的值的如果第一行改为10,Sales,Virginia,1/5/2000,,就不用TRAILINGNULLCOLS了
(DEPTNO,
DNAME"upper(:dname)",//使用函数
LOC"upper(:loc)",
LAST_UPDATEDdate'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*****使用自定义的函数//解决的时间问题
createorreplace
functionmy_to_date(p_stringinvarchar2)returndate
as
typefmtArrayistableofvarchar2(25);
l_fmtsfmtArray:=fmtArray('dd-mon-yyyy','dd-month-yyyy',
'dd/mm/yyyy',
'dd/mm/yyyyhh24:mi:ss');
l_returndate;
begin
foriin1..l_fmts.count
loop
begin
l_return:=to_date(p_string,l_fmts(i));
exception
whenothersthennull;
end;
EXITwhenl_returnisnotnull;
endloop;
if(l_returnisnull)
then
l_return:=
new_time(to_date('01011970','ddmmyyyy')+1/24/60/60*
p_string,'GMT','EST');
endif;
returnl_return;
end;
/
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
(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/200112:02:02
40,Finance,Virginia,987268297
50,Finance,Virginia,02-apr-2001
60,Finance,Virginia,Notadate
7*****合并多行记录为一行记录
LOADDATA
INFILE*
concatenate3//通过关键字concatenate把几行的记录看成一行记录
INTOTABLEDEPT
replace
FIELDSTERMINATEDBY','
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATEDdate'dd/mm/yyyy'
)
BEGINDATA
10,Sales,//其实这3行看成一行10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
//这列子用continueiflis
t=","也可以
告诉sqlldr在每行的末尾找逗号找到逗号就把下一行附加到上一行
LOADDATA
INFILE*
continueifthis(1:1)='-'//找每行的开始是否有连接字符-有就把下一行连接为一行
//如-10,Sales,Virginia,
//1/5/2000就是一行10,Sales,Virginia,1/5/2000
//其中1:1表示从第一行开始并在第一行结束还有continueifnext但continueiflist最理想
INTOTABLEDEPT
replace
FIELDSTERMINATEDBY','
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATEDdate'dd/mm/yyyy'
)
BEGINDATA//但是好象不能象右面的那样使用
-10,Sales,Virginia,-10,Sales,Virginia,
1/5/20001/5/2000
-40,40,Finance,Virginia,13/04/2001
Finance,Virginia,13/04/2001
--------------------------------------------------------------------------------
txfy回复于：2003-12-0117:07:21
8*****载入每行的行号
loaddata
infile*
intotablet
replace
(seqnoRECNUM//载入每行的行号
textPosition(1:1024))
BEGINDATA
fsdfasj//自动分配一行号给载入表t的seqno字段此行为1
fasdjfasdfl//此行为2...
9*****载入有换行符的数据
注意:unix和windows不同\\n&;/n
<1>;使用一个非换行符的字符
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
(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,ThisistheSales\nOfficeinVirginia
20,Accounting,Virginia,13/04/2001,ThisistheAccounting\nOfficeinVirginia
30,Consulting,Virginia,14/04/200112:02:02,ThisistheConsulting\nOfficeinVirginia
40,Finance,Virginia,987268297,ThisistheFinance\nOfficeinVirginia
<2>;使用fix属性
LOADDATA
INFILEdemo17.dat"fix101"
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATED"my_to_date(:last_updated)",
COMMENTS
)
demo17.dat
10,Sales,Virginia,01-april-2001,ThisistheSales
OfficeinVirginia
20,Accounting,Virginia,13/04/2001,ThisistheAccounting
OfficeinVirginia
30,Consulting,Virginia,14/04/200112:02:02,ThisistheConsulting
OfficeinVirginia
40,Finance,Virginia,987268297,ThisistheFinance
OfficeinVirginia
//这样装载会把换行符装入数据库下面的方法就不会但要求数据的格式不同
LOADDATA
INFILEdemo18.dat"fix101"
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'
TRAILINGNULLCOLS
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATED"my_to_date(:last_updated)",
COMMENTS
)
demo18.dat
10,Sales,Virginia,01-april-2001,"
ThisistheSales
OfficeinVirginia"
20,Accounting,Virginia,13/04/2001,"ThisistheAccounting
OfficeinVirginia"
30,Consulting,Virginia,14/04/200112:02:02,"ThisistheConsulting
OfficeinVirginia"
40,Finance,Virginia,987268297,"ThisistheFinance
OfficeinVirginia"
<3>;使用var属性
LOADDATA
INFILEdemo19.dat"var3"
//3告诉每个记录的前3个字节表示记录的长度如第一个记录的071表示此记录有71个字节
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATED"my_to_date(:last_updated)",
COMMENTS
)
demo19.dat
07110,Sales,Virginia,01-april-2001,ThisistheSales
OfficeinVirginia
07820,Accounting,Virginia,13/04/2001,ThisistheAccounting
OfficeinVirginia
08730,Consulting,Virginia,14/04/200112:02:02,ThisistheConsulting
OfficeinVirginia
07140,Finance,Virginia,987268297,ThisistheFinance
OfficeinVirginia
<4>;使用str属性
//最灵活的一中可定义一个新的行结尾符win回车换行:chr(13)||chr(10)
此列中记录是以a|\r\n结束的
selectutl_raw.cast_to_raw('|'||chr(13)||chr(10))fromdual;
结果7C0D0A
LOADDATA
INFILEdemo20.dat"strX'7C0D0A'"
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
(DEPTNO,
DNAME"upper(:dname)",
LOC"upper(:loc)",
LAST_UPDATED"my_to_date(:last_updated)",
COMMENTS
)
demo20.dat
10,Sales,Virginia,01-april-2001,ThisistheSales
OfficeinVirginia|
20,Accounting,Virginia,13/04/2001,ThisistheAccounting
OfficeinVirginia|
30,Consulting,Virginia,14/04/200112:02:02,ThisistheConsulting
OfficeinVirginia|
40,Finance,Virginia,987268297,ThisistheFinance
OfficeinVirginia|
==============================================================================
象这样的数据用nullif子句
10-jan-200002350Flipperseemedunusuallyhungrytoday.
10510-jan-200009945Spreadoverthreemeals.
idposition(1:3)nullifid=blanks//这里可以是blanks或者别的表达式
//下面是另一个列子第一行的1在数据库中将成为null
LOADDATA
INFILE*
INTOTABLET
REPLACE
(nposition(1:2)integerexternalnullifn='1',
vposition(3:8)
)
BEGINDATA
110
20lg
************************************************************************************************************
OracleSQLLoader的详细语法
SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据
仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。现在，我们抛开其理论不谈，用实例来使您快速掌握SQL*LOADER的使用方法。
首先，我们认识一下SQL*LOADER。
在NT下，SQL*LOADE
R的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。
如执行：d:/oracle>sqlldr
SQL*Loader:Release8.1.6.0.0-Productionon星期二1月811:06:422002
(c)Copyright1999OracleCorporation.Allrightsreserved.
用法:SQLLOAD关键字=值[,keyword=value,...]
有效的关键字:
userid--ORACLEusername/password
control--Controlfilename
log--Logfilename
bad--Badfilename
data--Datafilename
discard--Discardfilename
discardmax--Numberofdiscardstoallow(全部默认)
skip--Numberoflogicalrecordstoskip(默认0)
load--Numberoflogicalrecordstoload(全部默认)
errors--Numberoferrorstoallow(默认50)
rows--Numberofrowsinconventionalpathbindarrayorbetweendirectpathdatasaves
（默认:常规路径64,所有直接路径）
bindsize--Sizeofconventionalpathbindarrayinbytes(默认65536)
silent--Suppressmessagesduringrun(header,feedback,errors,discards,partitions)
direct--usedirectpath(默认FALSE)
parfile--parameterfile:nameoffilethatcontainsparameterspecifications
parallel--doparallelload(默认FALSE)
file--Filetoallocateextentsfrom
skip_unusable_indexes--disallow/allowunusableindexesorindexpartitions(默认FALSE)
skip_index_maintenance--donotmaintainindexes,markaffectedindexesasunusable(默认FALSE)
commit_discontinued--commitloadedrowswhenloadisdiscontinued(默认FALSE)
readsize--SizeofReadbuffer(默认1048576)
PLEASENOTE:命令行参数可以由位置或关键字指定
。前者的例子是'sqlloadscott/tigerfoo';后者的例子是'sqlloadcontrol=foouserid=scott/tiger'.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,
'SQLLOADSCott/tigercontrol=foologfile=log',但'不允许
sqlloadscott/tigercontrol=foolog',即使允许参数'log'的位置正确。
d:/oracle>
我们可以从中看到一些基本的帮助信息，这里，我用到的是中文的WIN2000ADVSERVER。
我们知道，SQL*LOADER只能导入纯文本，所以我们现在开始以实例来讲解其用法。
一、已存在数据源result.csv，欲倒入ORACLE中FANCY用户下。
result.csv内容：
1,默认Web站点,192.168.2.254:80:,RUNNING
2,other,192.168.2.254:80:test,STOPPED
3,third,192.168.2.2
54:81:thirdabc,RUNNING
从中，我们看出4列，分别以逗号分隔，为变长字符串。
二、制定控制文件result.ctl
result.ctl内容：
loaddata
infile'result.csv'
intotableresultxt
(resultidcharterminatedby',',
websitecharterminatedby',',
ipportcharterminatedby',',
statuscharterminatedbywhitespace)
说明：
infile指数据源文件这里我们省略了默认的discardfileresult.dscbadfileresult.bad
intotableresultxt默认是INSERT，也可以intotableresultxtAPPEND为追加方式，或REPLACE
terminatedby','指用逗号分隔
terminatedbywhitespace结尾以空白分隔
三、此时我们执行加载：
D:/>sqlldruserid=fancy/testpasscontrol=result.ctllog=resulthis.out
SQL*Loader:Release8.1.6.0.0-Productionon星期二1月810:25:422002
(c)Copyright1999OracleCorporation.Allrightsreserved.
SQL*Loader-941:在描述表RESULTXT时出现错误
ORA-04043:对象RESULTXT不存在
提示出错，因为数据库没有对应的表。
四、在数据库建立表
createtableresultxt
(resultidvarchar2(500),
websitevarchar2(500),
ipportvarchar2(500),
statusvarchar2(500))
/
五、重新执行加载
D:/>sqlldruserid=fancy/k1i7l6l8control=result.ctllog=resulthis.out
SQL*Loader:Release8.1.6.0.0-Productionon星期二1月810:31:572002
(c)Copyright1999OracleCorporation.Allrightsreserved.
达到提交点，逻辑记录计数2
达到提交点，逻辑记录计数3
已经成功！我们可以通过日志文件来分析其过程：resulthis.out内容如下：
SQL*Loader:Release8.1.6.0.0-Productionon星期二1月810:31:572002
(c)Copyright1999OracleCorporation.Allrightsreserved.
控制文件:result.ctl
数据文件:result.csv
错误文件:result.bad
废弃文件:未作指定
:
(可废弃所有记录)
装载数:ALL
跳过数:0
允许的错误:50
绑定数组:64行，最大65536字节
继续:未作指定
所用路径:常规
表RESULTXT
已载入从每个逻辑记录
插入选项对此表INSERT生效
列名位置长度中止包装数据类型
--------------------------------------------------------------------------
RESULTIDFIRST*,CHARACTER
WEBSITENEXT*,CHARACTER
IPPORTNEXT*,CHA
RACTER
STATUSNEXT*WHTCHARACTER
表RESULTXT:
3行载入成功
由于数据错误,0行没有载入。
由于所有WHEN子句失败,0行没有载入。
由于所有字段都为空的,0行没有载入。
为结合数组分配的空间:65016字节（63行）
除绑定数组外的内存空间分配:0字节
跳过的逻辑记录总数:0
读取的逻辑记录总数:3
拒绝的逻辑记录总数:0
废弃的逻辑记录总数:0
从星期二1月0810:31:572002开始运行
在星期二1月0810:32:002002处运行结束
经过时间为:00:00:02.70
CPU时间为:00:00:00.10(可
六、并发操作
sqlldruserid=/control=result1.ctldirect=trueparallel=true
sqlldruserid=/control=result2.ctldirect=trueparallel=true
sqlldruserid=/control=result2.ctldirect=trueparallel=true
当加载大量数据时（大约超过10GB），最好抑制日志的产生：
SQL>ALTERTABLERESULTXTnologging;
这样不产生REDOLOG，可以提高效率。然后在CONTROL文件中loaddata上面加一行：unrecoverable此选项必须要与DIRECT共同应用。
在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到1－10G就算不错了，开始可用结构相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。
有关SQLLDR的问题
控制文件：input.ctl，内容如下：
loaddata--1、控制文件标识
infile'test.txt'--2、要输入的数据文件名为test.txt
appendintotabletest--3、向表test中追加记录
fieldsterminatedbyX'09'--4、字段终止于X'09'，是一个制表符（TAB）
(id,username,password,sj)-----定义列对应顺序
其中append为数据装载方式，还有其他选项：
a、insert，为缺省方式，在数据装载开始时要求表为空
b、append，在表中追加新记录
c、replace，删除旧记录，替换成新装载的记录
d、truncate，同上
==================================================================================
PS:下面是我用C#写的调用SQL_LOADER的方法
PrivateFunctionExec_SqlLoader(ByVals_aFilePathAsString,ByValo_afileNameAsHashtable,ByRefs_aErrMsgAsString)AsBoolean
FilePath=s_aFilePath.Trim("/")
DimctlPathAsString=FilePath+"/control"
s_aErrMsg=""
Try
ForiAsInteger=1To4
DimstrmReaderAsNewStreamReader(ctlPath+"/001"+i.ToSt
ring()+".ctl")
DimctlContentAsString=strmReader.ReadToEnd()
ctlContent=ctlContent.Replace("{{dataFile}}",o_afileName.Item(i.ToString))
strmReader.Close()
strmReader=Nothing
DimstrCtlNameAsString=ctlPath+"/001"+i.ToString()+"new.ctl"
DimstrLogNameAsString=ctlPath+"/001"+i.ToString()+"new.log"
DimstrBadNameAsString=ctlPath+"/001"+i.ToString()+"new.bad"
DimstrDscNameAsString=ctlPath+"/001"+i.ToString()+"new.dsc"
DimstrmWriterAsStreamWriter=File.CreateText(strCtlName)
strmWriter.Write(ctlContent)
strmWriter.Close()
strmWriter=Nothing
If(File.Exists(strBadName))Then
File.Delete(strBadName)
EndIf
DimconnSettingAsString()=thisVersion.MSD_ConnectionStr.Split(";")
DimserviceNameAsString=CType(connSetting.GetValue(0),String)
serviceName=serviceName.Substring(serviceName.IndexOf("=")+1)
DimuserIdAsString=CType(connSetting.GetValue(1),String)
userId=userId.Substring(userId.IndexOf("=")+1)
DimpasswordAsString=CType(connSetting.GetValue(2),String)
password=password.Substring(password.IndexOf("=")+1)
DimstrPathNameAsString
strPathName="sqlldr"+userId+"/"+password+"@"+serviceName+"control='"+strCtlName+"'log='"+strLogName+"'bad='"+strBadName+"'discard='"+strDscName+"'errors=500"
Microsoft.VisualBasic.Shell(strPathName,AppWinStyle.MinimizedFocus,True)
If(File.Exists(strBadName))Then
File.Copy(strBadName,FilePath+"/BatchLog/ERROR_001"+i.ToString()+".txt",True)
a_sErrMsg+="这里写抛出的警告信息"+vbCrLf+vbTab
EndIf
Next
ReturnTrue
CatchexAsException
a_sErrMsg+=ex.ToString()
ms_BatchLogMsg=ex.Message
ReturnFalse
EndTry
EndFunction
*********************************************************************************************************
