SqlLoader怎么使用
SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。今天看了申请了*loader的使用，自己小试了下，记录在这
1、假设要插入数据的表ftest，字段是（id,username,password,sj）
2、导入表的数据以txt格式存储，名为data.txt
1ff2010-8-19 2f1f12010-8-19 3f2f22010-8-19 4f3f32010-8-19 5f4f42010-8-19
3、写控制文件，格式为ctl，命名为cont.ctl内容如下：
loaddata infile'c:\data.txt' insertintotableftest fieldsterminatedby"" (id,username,password,sj)
注：如果表中没有数据就用insert，有数据就用append，删除旧数据插入新的数据用replace或truncate
4在cmd命令窗口中执行
sqlldrfyzh/fyzhcontrol=c:\cont.ctldata=c:\data.txt
5在plsql中查看表ftest
查看已成功插入。
重新学习sqlldr
sqlldr导入数据的一个最简单例子：
loaddata infile*--告诉sqlldr要加载的数据就包含在控制文件本身 intotabledept--加载到哪个表 fieldsterminatedby','--数据加载形式应该是逗号分隔的值 (deptno,dname,loc)--所要加载的列 begindata--告诉sqlldr后面的行市要加载到dept表的数据 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia createtabledept (deptnonumber(2)constraintdept_pkprimarykey, dnamevarchar2(14), locvarchar2(13) ) sqlldruserid=gwm/gwm@fgisdbcontrol=c:\demol.ctl select*fromdept; 110SalesVirginia 220AccountingVirginia 330ConsultingVirginia 440FinanceVirginia
sqlldr导入的四种加载方式：
APPEND：原先的表有数据就加在后面
INSERT：装载空表如果原先的表有数据sqlloader会停止默认值
REPLACE：原先的表有数据原先的数据会全部删除
TRUNCATE：指定的内容和replace的相同会用truncate语句删除现存数据
用SQLLDR加载数据的FAQ
1、如何加载定界数据
1)定界数据即用某个特殊字符分隔的数据，可能用引号括起，这是当前平面文件最常见的数据格式。
对于定界数据，最常用的格式是逗号分隔值格式。采用这种文件格式，数据中的每个字段与下一个字段用一个逗号分隔。文本串可以用引号括起，这样就串本身包含逗号。如果串还必须包含引号，一般约定是使用两个引号。加载定界数据，相应的典型控制文件与前面例子相似，但是fieldsterminatedby子句通常如下指定：
fieldsterminatedby','optionallyencloseby'"'
它指定用逗号分隔数据字段，每个字段可以用双引号括起。如果把这个控制文件的最后部分修改如下:
fieldsterminatedby','optionallyenclosedby'"' (deptno,dname,loc) begindata 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA""" 30,Consulting,Virginia 40,Finance,Virginia select*fromdept 110SalesVirginia,USA 220AccountingVa,"USA" 330ConsultingVirginia 440FinanceVirginia
2)另一种常用的格式是制表符定界数据。有两种方法使用terminatedby子句来加载这种数据：
terminatedbyX'09'--使用十六进制格式的制表符；若用ASCII，制表符应该是9
terminatedbywhitespace --使用terminatedbywhitespace loaddata infile* intotabledept replace fieldsterminatedbywhitespace (deptno,dname,loc) begindata 10SalesVirginia select*fromdept; 110SalesVirginia --使用terminatedbyX'09' loaddata infile* intotabledept replace fieldsterminatedbyX'09' (deptno,dname,loc) begindata 10SalesVirginia select*fromdept; 110
Sales--因为一旦遇到一个制表符就会输出一个值。
因此，将10赋给deptno，dname得到了null，因为在第一个制表符和第二个制表符之间没有数据
3）sqlldr的filler关键字使用
如跳过制表符
loaddata infile* intotabledept replace fieldsterminatedbyX'09' (deptno,dummy1filler,dname,dummy2filler,loc) begindata 10SalesVirginia select*fromdept; 110SalesVirginia
2、如何加载固定格式数据
要加载定宽的固定位置数据，将会在控制文件中使用position关键字。
loaddata infile* intotabledept replace (deptnoposition(1:2), dnameposition(3:16), locposition(17:29) ) begindata 10AccountingVirginia,USA select*fromdept; 110AccountingVirginia,USA
这个控制文件没有使用terminatedby子句；而是使用了position来告诉sqlldr字段从哪里开始，到哪里结束。
对于position，我们可以使用重叠的位置，可以在记录中来回反复。如下修改dept表：
altertabledeptaddentire_linevarchar(29);
并使用如下控制文件：
loaddata infile* intotabledept replace (deptnoposition(1:2), dnameposition(3:16), locposition(17:29), entire_lineposition(1:29) ) begindata 10AccountingVirginia,USA select*fromdept; 110AccountingVirginia,USA10Accounting Virginia,USA
使用position时，可以使用相对偏移量，也可以使用绝对偏移量。前面的例子使用了绝对偏移量，明确指定字段从哪开始，从哪结束，也可以将
前面的控制文件改写如下：
loaddata infile* intotabledept replace (deptnoposition(1:2), dnameposition(*:16), locposition(*:29), entire_lineposition(1:29) ) begindata 10Accounting Virginia,USA
*指示控制文件得出上一个字段在哪里结束。因此，在这种情况下，(*:16)与(3:16)是一样的。注意，控制文件可以混合使用相对位置和绝对位置。
另外，使用*表示法时，可以把它与偏移量相加。例如dname从deptno结束之后的；两个字符开始，可以使用（*+2:16），即相当于（5:16）.
position子句中的结束位置必须是数据结束的绝对列位置。有时，可能指定每个字段的长度更为容易，特别是如果这些字段是连续的。采用这种
方式，只需告诉sqlldr：记录从第一个字节开始，然后指定每个字段的长度。如下：
loaddata infile* intotabledept replace (deptnoposition(1)char(2), dnameposition(*)char(14), locposition(*)char(13), entire_lineposition(1)char(29) ) begindata 10AccountingVirginia,USA select*fromdept;
3、如何加载日期
使用sqlldr加载日期只需在控制文件中date数据类型，并指定要使用的日期掩码。这个日期掩码与数据库中to_char和to_date中使用的日期掩码一样。
如修改dept表如下：
altertabledeptaddlast_updateddate; loaddata infile* intotabledept replace fieldsterminatedby',' (deptno, dname, loc, last_updateddate'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select*fromdept; 110 Accounting Virginia 2000-5-1
4、如何使用函数加载数据
如果想确保加载的数据是大写的，可以改写控制文件如下：
loaddata infile* intotabledept replace fieldsterminatedby',' (deptno, dname"upper(:dname)", loc"upper(:loc)", last_updateddate'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select*fromdept; 110 ACCOUNTING VIRGINIA 2000-5-1
如下控制文件加载数据无法导入
loaddata infile* intotabledept replace fieldsterminatedby',' (deptno, dname"upper(:dname)", loc"upper(:loc)", last_updateddate'dd/mm/yyyy', entire_line":deptno||:dname||:loc||:last_updated" ) begindata 10,Accounting,Virginia,1/5/2000
1）TRAILINGNULLCOLS的使用：一般默认用的好
解决方法，就是使用TRAILINGNULLCOLS。这样，如果输入记录中不存在某一列的数据，sqlldr就会为该列绑定一个null值。
这种情况下，增加TRAILINGNULLCOLS会导致绑定变量:entire_line成为null。
loaddata infile* intotabledept replace fieldsterminatedby',' TRAILINGNULLCOLS (deptno, dname"upper(:dname)", loc"upper(:loc)", last_updateddate'dd/mm/yyyy', entire_line":deptno||:dname||:loc||:last_updated" ) begindata 10,Accounting,Virginia,1/5/2000 select*fromdept; 110ACCOUNTINGVIRGINIA10AccountingVirginia1/5/20002000-5-1
2）case在sqlldr中的使用
假设输入文件中有以下格式的日期：
HH24:MI:SS:只有一个时间；日期时间默认为sysdate
DD/MM/YYYY:只有一个日期，时间默认为午夜0点
HH24:MI:SSDD/MM/YYYY:日期时间都显式提供
可用如下的控制文件
loaddata infile* intotabledept replace fieldsterminatedby',' TRAILINGNULLCOLS (deptno, dname"upper(:dname)", loc"upper(:loc)", last_updated "case whenlength(:last_updated)>9 thento_date(:last_updated,'hh24:mi:ssdd/mm/yyyy') wheninstr(:last_updated,':')>0 thento_date(:last_updated,'hh24:mi:ss') elseto_date(:last_updated,'dd/mm/yyyy') end" ) begindata 10,Sales,Virginia,12:03:0317/10/2005 20,Accounting,Virginia,02:23:54 30,Consulting,Virginia,01:24:0021/10/2006 40,Finance,Virginia,17/8/2005 altersessionsetnls_date_format='dd-mon-yyyyhh24:mi:ss'; select*fromdept;
5、如何加载有内嵌换行符的数据
1）用非换行符的其它字符来表示换行符，并在加载时使用一个sql函数用一个CHR(10)替换该文本。
altertabledeptaddcommentsvarchar2(4000); --使用下列来加载文本 loaddata infile* intotabledept replace fieldsterminatedby',' trailingnullcols (deptno, dname"upper(:dname)", loc"upper(:loc)", comments"replace(:comments,'\\n',chr(10))"--'\\n'换行符用chr(10)这个代替 ) begindata 10,Sales,Virginia,thisisthesales\nofficeinVirginia
注：调用中必须用\\n来表示替换符，而不是\n
2）在infile指令上使用FIX属性，加载一个定长平面文件。
使用该方法，输入数据必须出现在定长记录中。对于固定位置的数据，使用FIX属性就特别合适，这些文件一般为定长文件。
另外使用该方法时，数据必须在外部存储，不能存储在控制文件本身。
--控制文件 loaddata infiledemo.dat"fix80"--指定了输入数据文件demo.dat，这个文件中每个记录80字节 intotabledept replace fieldsterminatedby',' trailingnullcols (deptno, dname"upper(:dname)", loc"upper(:loc)", comments ) --数据文件 10,Sales,Virginia,thisisthesales\nofficeinVirginia 20,，,Sales,Virginia,thisisthesales\nofficeinVirginia
注：
在unix上，行结束标记是\n即CHR(10),而windowsnt平台的行结束标记是\r\n即CHR(13)||CHR(10)；
可以在控制文件中使用trim内置sql函数来完成截断尾部的空白符
select*fromdept;
3)在infile指令在、上使用VAR属性，加载一个变宽文件，在该文件使用的格式中，每一行前几个字节指定了这一行的长度
--控制文件 loaddata infiledemo.dat"var3"--表明了前三个字节用于记录每一行的字节数 intotabledept replace fieldsterminatedby',' trailingnullcols (deptno, dname"upper(:dname)", loc"upper(:loc)", comments ) --数据文件 05410,Sales,Virginia,thisisthesalesofficeinVirginia
注：在unix上换行符只算一个字节，在windowsnt上算两个字节
select*fromdept;
4）在infile指令上使用STR属性，加载一个变宽文件，其中用某个字符序列来表示行结束符，而不是用换行符表示
STR属性以十六进制指定，要得到十六进制串，最容易的办法就是使用sql和utl_raw来生成十六进制串。如在unix平台，行结束标记是CHR(10)，我们的特殊字符是一个管道符号（|），则可以写成：
selectutl_raw.cast_to_raw('|'||chr(10))fromdual;--可见在unix上为x'7C0A'
在windows上用
selectutl_raw.cast_to_raw('|'||chr(13)||chr(10))fromdual;--为x'7C0D0A'
--控制文件
loaddata
infiledemo.dat"strx'7C0D0A'"
intotabledept
replace
fieldsterminatedby','
trailingnullcols
(deptno,
dname"upper(:dname)",
loc"upper(:loc)",
comments
)
--数据文件
10,Sales,Virginia,thisisthesales
officeinVirginia|
select*fromdept;
6、加载lob数据
1）加载内联的lob数据。这些lob数据通常内嵌有换行符和其他特殊字符
--修改表dept truncatetabledept; altertabledeptdropcolumncomments; altertabledeptaddcommentsclob; --数据文件 10,Sales,Virginia,thisisthesales officeinVirginia| 20,Accounting,Virginia,thisistheAccounting officeinVirginia| 30,Consuling,Virginia,thisistheConsuling officeinVirginia| 40,Finance,Virginia,"thisistheFinance officeinVirginia,ithasembeddedcommasandis muchlongerthantheothercommentsfiled.Ifyou feeltheneedtoadddoublequotestextinherelike this:""youwillneedtodoubleupthosequotes!""to preservetheminthestring.Thisfieldkeepsgoingforupto 1000000bytes(becauseofthecontrolfiledefinitionIused) oruntilwehitthemagicandofrecordmarker, the|followedbyanendofline-itisrighthere->"| --控制文件 loaddata infiledemo.dat"strx'7C0D0A'" intotabledept replace fieldsterminatedby','optionallyenclosedby'"' trailingnullcols (deptno, dname"upper(:dname)", loc"upper(:loc)", commentschar(1000000)--sqlldr默认输入的字段都是char(255)。char(1000000)表示允许输入多达1000000个字符 ) select*fromdept;
2)加载外联的lob数据。
需要把包含有一些文件名的数据文件加载在lob中，而不是让lob数据与结构化数据混在一起。这样就不必使用上述的4种方法之一来避开输入数据中
的内嵌换行符问题，而这种情况在大量的文本或二进制数据中频繁出现。sqlldr称这种额外的数据文件为lobfile。
sqlldr还可以支持加载结构化数据文件。可以告诉sqlldr如何从另外一个文件解析lob数据，这样就可以加载其中的一部分作为结构化数据中的每一行。
sqlldr称这种外部引用的文件为复杂二级数据文件。
lobfile数据采用以下某种格式:
定长字段（从lobfile加载字节100到10000）；
定界字段（以某个字符结束，或用某个字符括起）；--最常见，以一个文件结束符（EOF）结束
长度/值对，这是一个边长字段
--加载数据的表 createtablelob_demo (ownervarchar2(255), time_stampdate, filenamevarchar2(255), datablob) --假设有一目录，其中包含想要加载到数据库中的文件。以下为想要加载文件的owner，time_stamp，文件名及文件本身 loaddata infile* replace intotablelob_demo (ownerposition(17:25), time_stampposition(44:55)date"MonDDHH24:MI", filenameposition(57:100), datalobfile(filename)terminatedbyEOF ) begindata -rw-r--r--1tkytetkyte1220342jun1715:26classes12.zip selectowner,time_stamp,filename,dbms_lob.getlength(data)fromlob_demo;
3）将lob数据加载到对象列
一般用于加载图像
createtableimage_load( idnumber, namevarchar2(255), imageordsys.ordimage)--首先要了解ordsys.ordimage类型
加载这种数据的控制文件如下所示：
loaddata infile* intotableimage_load replace fieldsterminatedby',' (id, name, file_namefiller, imagecolumnobject ( sourcecolumnobject ( localdatalobfile(file_name)terminatedbyEOF nulliffile_name='none' ) ) ) begindata 1,icons,icons.gif
注：columnobject告诉sqlldr这不是一个列名，而是列名的一部分。
使用的列名是image.source.localdata
select*fromimage_load
--继续编辑加载进来数据的属性 begin forcin(select*fromimage_load)loop c.image.setproperties;--setproperties是ordsys.ordimage类型提供的方法，处理图像本身，并用适当的值更新对象的其余属性 endloop; end;
额外介绍：
使用plsql加载lob数据
createtabledemo(idintprimarykey,theclobclob)
createorreplacedirectorydir1as'D:\oracle';
SQL>hostecho'helloworld!'>d:/oracle/test.txt
declare
l_clobclob;
l_bfilebfile;
begin
insertintodemovalues(1,empty_clob())returningtheclobintol_clob;
l_bfile:=bfilename('DIR1','test.txt');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
end;
selectdbms_lob.getlength(theclob),theclobfromdemo;
注：
创建的目录默认为大写DIR1，如果目录写成dir1就会提示错误，如果要想使用混有大小写的目录名，在创建这样的目录时应该带引号的标识符，如下所示：
createorreplacedirectory"dir2"as'D:\oracle';
以上内容是小编给大家分享的关于SqlLoader怎么使用的相关资料，希望大家喜欢。