SQLLDR应用举例.docx
《SQLLDR应用举例.docx》由会员分享，可在线阅读，更多相关《SQLLDR应用举例.docx（13页珍藏版）》请在冰豆网上搜索。
SQLLDR应用举例
SQLLDR应用举例
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列将为空
注：
BEGINDATA后的数值前面不能有空格
2、TERMINATEDBYWHITESPACE和FIELDSTERMINATEDBYx'09'的情况
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBYWHITESPACE
--FIELDSTERMINATEDBYx'20'
（DEPTNO,
DNAME,
LOC
）
BEGINDATA
10SalesVirginia
注：
x'20'表示字符ASCII码的16进制数值
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
结果：
10|AccountingVir |ginia,USA| 10AccountingVirginia,USA
5、使用函数日期的一种表达TRAILINGNULLCOLS的使用
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
--这句的意思是将没有对应值的列都置为null
--如果第一行改为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
注：
可以通过:
dname类型调用函数，特别注意date函数的使用。
6、合并多行记录为一行记录
LOADDATA
INFILE*
concatenate3 --通过关键字concatenate把几行的记录看成一行记录
INTOTABLEDEPT
replace
FIELDSTERMINATEDBY','
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDdate'dd/mm/yyyy'
）
BEGINDATA
10,Sales,
Virginia,
1/5/2000
注：
例如有些文本文件以N行为一循环记录数据，则可以这样导入。
7、使用continueif来合并记录行
上例可直接使用continueiflast=','来告诉Oracle如果前一个数据以','结尾，则这个附加到上一行
LOADDATA
INFILE*
continueiflast=','
INTOTABLEDEPT
replace
FIELDSTERMINATEDBY','
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDdate'dd/mm/yyyy'
）
BEGINDATA
10,Sales,
Virginia,
1/5/2000
注：
ContinueIf还可以使用this或next选项，具体操作见文档
8、载入每行的行号
LOADDATA
INFILE*
INTOTABLEDEPT
replace
（DEPTNORECNUM//载入每行的行号
ENTIRE_LINEPosition（1:
1024）
）
BEGINDATA
fsdfasj --自动分配行号到DEPTNO字段，此行为1
fasdjfasdfl --自动递增，此行为2
9、载入有换行符的数据
①使用一个非换行符的字符
LOADDATA
INFILE*
INTOTABLEDEPT
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATED"my_to_date（:
last_updated）",
COMMENTS"replace（:
comments,'%%',chr（10））" --用replace函数转换成换行符
）
BEGINDATA
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
注：
换行的特殊字符如果使用'\n'，则会在Windows编译过程中直接换成换行符，导致无法转换
②使用fix属性
LoadDATA
INFILEdemo1.dat"fix68"
INTOTABLEt1_a
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDDate'dd/mm/yyyy',
ENTIRE_LINE
）
demo1.dat
10,aaaab,Virginia,01/05/2001,Thisistheaaaab
OfficeinVirginia
20,aaaac,Virginia,13/04/2001,Thisistheaaaac
OfficeinVirginia
30,aaaad,Virginia,14/04/2001,Thisistheaaaad
OfficeinVirginia
40,aaaae,Virginia,16/02/2001,Thisistheaaaae
OfficeinVirginia
注：
fix只能加在外部文件数据导入时，另外需要每行数据长度都相等。
--下面这种方法也一样
LoadDATA
INFILEdemo17.dat"fix70"
INTOTABLEt1_a
REPLACE
FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'
TRAILINGNULLCOLS
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDDate'dd/mm/yyyy',
ENTIRE_LINE
）
demo2.dat
10,aaaab,Virginia,01/05/2001,"Thisistheaaaab
OfficeinVirginia"
20,aaaac,Virginia,13/04/2001,"Thisistheaaaac
OfficeinVirginia"
30,aaaad,Virginia,14/04/2001,"Thisistheaaaad
OfficeinVirginia"
40,aaaae,Virginia,16/02/2001,"Thisistheaaaae
OfficeinVirginia"
③使用var属性
LoadDATA
INFILEdemo17.dat"var3"
INTOTABLEt1_a
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDDate'dd/mm/yyyy',
ENTIRE_LINE
）
demo17.dat
03510,Sales,Virginia,01/01/2001,This
03920,Accounting,Virginia,13/04/2001,Thi
04530,Consulting,Virginia,14/04/2001,Thisist
07140,Finance,Virginia,14/04/2001,ThisistheFinanceOffice
inVirginia
注：
var3表示前三位用于说明该条记录的长度（但是谁告诉我长度怎么数的？
-_-|||）
④使用str属性
可使用str来定义一个行结尾符
计算以|\r\n结束的值：
selectutl_raw.cast_to_raw（'|'||chr（13）||chr（10））fromdual;
结果7C0D0A
LoadDATA
INFILEdemo17.dat"strX'7C0D0A'"
INTOTABLEt1_a
REPLACE
FIELDSTERMINATEDBY','
TRAILINGNULLCOLS
（DEPTNO,
DNAME"upper（:
dname）",
LOC"upper（:
loc）",
LAST_UPDATEDDate'dd/mm/yyyy',
ENTIRE_LINE
）
demo17.dat
10,Sales,Virginia,01/01/2001,ThisistheSales
OfficeinVirginia|
20,Accounting,Virginia,13/04/2001,ThisistheAccounting
OfficeinVirginia|
30,Consulting,Virginia,14/04/2001,ThisistheConsulting
OfficeinVirginia|
40,Finance,Virginia,14/04/2002,ThisistheFinance
OfficeinVirginia
注意：
同样需要在外部文件数据导入中使用，且最后一个不用加；另外注意不要有空格
10、nullif导入
LOADDATA
INFILE*
INTOTABLE t1_a
REPLACE
（DEPTNOposition（1:
2）integerexternalnullifDEPTNO='1',
--当导入deotno的值为'1'时，则该条记录不导入
DNAMEposition（3:
8）
）
BEGINDATA
110
20lg
注：
需要注意的是在前面指定的数据类型以及后面的引号！
==========================================================================================================
OracleSQL*Loader使用指南（转载）
如何使用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
跳过数据行：
可以用"SKIPn"关键字来指定导入时可以跳过多少行数据。
如：
LOADDATA
INFILE*
INTOTABLEload_positional_data
SKIP5 --似乎不行？
需要在DOS层级下操作才有效
（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.
升级会员 