如何使用SqlLoader

SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于将数据以各种平面文件格式加载到 Oralce 数据库中。今天看到使用*loader的应用，自己尝试了一下，记录在这里

1。假设要插入数据的表ftest，字段为(id,username,password,sj)

2.导入到表中的数据以txt格式存储，命名为data.txt

 1 f f 2010-8-192 f1 f1 2010-8-193 f2 f2 2010-8 -194 f3 f3 2010-8-195 f4 f4 2010-8-19

3.以ctl格式编写控制文件，命名为cont.ctl。内容如下：

load data infile 'c:\data.txt' insert into table ftest fields returned by " "(id,username,password,sj)

注意：如果表中没有数据，则使用insert，如果有数据，则使用append，删除旧数据并插入新数据，使用replace或truncate

4 在cmd命令窗口执行

sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt

5 查看plsql中的表ftest

，发现插入成功。

重新学习sqlldr

sqlldr导入数据最简单的例子：

load datainfile * - - 告诉sqlldr要加载的数据包含在控制文件本身到表dept中 -- 加载以', '结尾的字段 -- 数据加载格式应该是逗号分隔值(deptno, dname, loc) --要加载的列 begindata --告诉sqlldr后面的market加载数据10, Sales, Virginia20, Accounting, Virginia30到dept表中 ,Consulting,Virginia40,Finance,Virginiacreate table dept(deptno number(2)约束 dept_pk primary key,dname varchar2(14),loc varchar2(13))sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctlselect * from dept;1 10 销售弗吉尼亚2 20 会计弗吉尼亚3 30 咨询弗吉尼亚4 40 财务弗吉尼亚
sqlldr导入的四种加载方式：
APPEND：原表如果有数据，则在INSERT后面添加：加载空表。如果原表有数据，sqlloader就会停止。默认值REPLACE：原表有数据。所有原始数据将被删除。 TRUNCATE：指定如果内容与replace相同，则使用truncate语句删除现有数据
SQLLDR加载数据常见问题
<强>1。如何加载分隔数据
1) 分隔数据是用特殊字符分隔的数据，可以用引号括起来。这是当前平面文件最常见的数据格式。对于分隔数据，最常见的格式是逗号分隔值格式。在这个文件格式，数据中的每个字段与下一个字段之间用逗号分隔。文本字符串可以用引号引起来，以便字符串本身包含逗号。如果字符串还必须包含引号，则一般约定是使用两个引号。要加载分隔数据，相应的典型控制文件与前面的示例类似，但终止于子句的字段通常指定如下：
字段终止于 ','可选用 ' "' 括起来
指定用逗号分隔数据字段，每个字段可以用双引号括起来。如果此控制文件的最后部分修改如下：
 以 ',' 结尾的字段，可选用 '"' 括起来 (deptno,dname,loc) begindata 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA"" " 30，咨询，弗吉尼亚州 40，财务，弗吉尼亚州select * from dept1 10 Sales Virginia,USA2 20 Accounting Va,"USA"3 30 Consulting Virginia 4 40 Finance Virginia
2) 另一种常见最常用的格式是制表符分隔的数据。使用终止于子句有两种方法加载此数据： 
终止于 X'09' --使用十六进制格式的制表符；如果使用 ASCII，制表符应为 9
 终止于空白--使用终止于空白加载 datainfile *into 表 deptreplacefields 终止于空白(deptno,dname,loc) begindata 10 销售弗吉尼亚 select * from dept;1 10 销售弗吉尼亚 -- 使用由 X'09' 终止加载数据输入文件*到表 deptreplacefields 终止于 X'09'(deptno,dname,loc) begindata 10 销售 弗吉尼亚 select * from dept;1 10 
Sales --因为一旦遇到制表符就会输出一个值。 
因此，将 10 分配给 deptno，dname 为 null，因为第一个制表符和第二个制表符之间没有数据
3) sqlldr 使用填充关键字
< p>比如跳过制表符
load datainfile *into table deptreplacefields ter由 X'09'(deptno,dummy1 填充符, dname,dummy2 填充符,loc) 组成 begindata 10 销售弗吉尼亚州 select * from dept;1 10 销售弗吉尼亚州
2.如何加载固定格式数据
加载特定Width固定位置数据将使用控制文件中的position关键字。 
将数据输入文件 * 加载到表 deptreplace(deptno 位置(1:2), dname 位置(3:16), loc 位置(17:29) ) begindata 10Accounting Virginia, USAselect * from dept;1 10 Accounting Virginia,USA
此控制文件不使用终止于子句；相反，它使用位置来告诉 sqlldr 字段的开始位置和结束位置。对于位置，我们可以使用重叠位置，这些位置可以在记录中来回重复。修改dept表如下：
alter table dept add full_line varchar(29);
并使用以下控制文件：
 
加载数据文件 *到表 deptreplace(deptno 位置(1:2), dname位置(3:16), loc 位置(17:29), full_line 位置(1:29) ) begindata 10会计弗吉尼亚州,美国select * from dept;1 10 会计弗吉尼亚州,美国 10会计弗吉尼亚州,美国
何时使用位置，您可以使用相对偏移或绝对偏移。前面的示例使用绝对偏移量来明确指定字段的开始和结束位置。您还可以重写之前的控制文件，如下所示：
load datainfile *into table deptreplace(deptnoposition(1:2), dname position(*:16), loc position(*:29), full_lineposition(1:29) ) begindata 10Accounting Virginia,USA
* 指示控制文件找出前一个字段的结束位置。因此，在这种情况下，(*:16) 与 (3:16) 相同。请注意，控制文件可以混合使用相对位置和绝对位置。或者，当使用 * 表示法时，您可以将其添加到偏移量中。例如dname从deptno结束后的两个字符开始，可以使用(*+2:16)，相当于(5:16).
位置子句中的结束位置必须是数据绝对列位置的结束。有时指定每个字段的长度可能更容易，特别是如果字段是连续的。这样，只要告诉sqlldr：记录从第一个字节开始，然后指定每个字段的长度。如下：
load datainfile *into table deptreplace(deptnoposition(1)char(2),dnameposition(*)char(14),locposition(*) char (13), 整个行位置(1) char(29) ) begindata 10会计弗吉尼亚州，美国 select * from dept;
3.如何加载日期
 要使用 sqlldr 加载日期，只需在控制文件中添加日期数据类型并指定要使用的日期掩码。此日期掩码与数据库中 to_char 和 to_date 中使用的日期掩码相同。 
例如，修改dept表如下：
alter table dept add last_updated date;load datainfile *into table deptreplacefields终止d by ','(deptno, dname, loc, last_updated date 'dd/mm/yyyy' ) begindata 10,会计,弗吉尼亚州,1/ 5/2000select * from dept;1 10 会计弗吉尼亚 2000-5-1
4.如何使用函数加载数据
如果要保证加载的数据是大写的，控制文件可以重写如下：
将数据文件 * 加载到表 deptreplacefields 终止于 ','(deptno, dname "upper(:dname)", loc " upper(:loc)", last_updated date 'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000select * from dept;1 10 ACCOUNTING  VIRGINIA 2000-5-1
以下控制文件加载数据无法导入
将数据文件 * 加载到表 deptreplacefields 终止于 ','(deptno , dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy', full_line ": deptno||:dname||:loc||:last_updated" ) begindata 10，会计，弗吉尼亚州，2000 年 1 月 5 日
1) 尾随 NULLCOLS 的使用：通常 t默认值很好
解决方案是使用 TRAILING NULLCOLS。这样，如果输入记录中不存在某一列的数据，sqlldr就会给该列绑定一个空值。 
在这种情况下，添加 TRAILING NULLCOLS 将导致绑定变量：entire_line 变为 null。 
将数据文件*加载到以 ','TRAILING NULLCOLS 终止的表 deptreplacefields(deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated日期 'dd/mm/yyyy', full_line ":deptno||:dname||:loc||:last_updated" ) begindata 10，会计，弗吉尼亚州，1/5/2000select * from dept;1 10 会计弗吉尼亚州 10会计弗吉尼亚州1/5 /2000 2000-5-1
2）sqlldr中大小写的使用
假设输入文件有一个日期，格式如下：HH24:MI:SS: 有只有一次;日期和时间默认为 sysdateDD/MM/YYYY：只有一个日期，时间默认为午夜 0:00 HH24:MI:SS DD/MM/YYYY：明确提供日期和时间
< p>佛可以使用以下控制文件
将数据文件 * 加载到表 deptreplacefields 终止于 ','TRAILING NULLCOLS(deptno, dname "upper(:dname)", loc "upper( :loc)", last_updated "当 length(:last_updated)>9 时则 to_date(:last_updated,'hh34:mi:ss dd/mm/yyyy') 当 instr(:last_updated,':')>0 时 to_date( :last_updated,'hh34:mi:ss') else to_date(:last_updated, 'dd/mm/yyyy') end" ) begindata10，销售，弗吉尼亚州，12:03:03 17/10/200520，会计，弗吉尼亚州，02 :23:5430，咨询，弗吉尼亚州，01:24:00 21/10/200640，财务，弗吉尼亚州，17/8/2005更改会话设置 nls_date_format='dd-mon-yyyy hh34:mi:ss';从部门中选择 * ;
5.如何加载嵌入换行符的数据
1）使用换行符以外的字符来表示换行符，并使用sql函数将文本替换为CHR(10) 
alter table dept add comments varchar2(4000);--使用以下内容加载文本load datainfile*进入表 deptreplacefields 以 ',' 结尾的 nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments "replace(:comments,'\\n',chr(10)) " --'\\n' 换行符替换为 chr(10))begindata10,Sales,Virginia ,这是销售\n弗吉尼亚州办事处
注意：必须使用 \\n 来表示调用中的替换字符，而不是 \n
2) 在 infile 指令上使用 FIX 属性，加载某个长的平面文件。使用此方法，输入数据必须出现在固定长度的记录中。对于固定位置的数据，使用FIX属性特别合适。这些文件通常是固定长度的文件。另外，使用这种方法时，数据必须存储在外部，不能存储在控制文件本身中。 
--控制文件加载datainfile demo.dat "fix 80" --指定输入数据文件demo.dat，该文件中每条记录80字节放入表deptreplacefields中由 ',' 结尾的 nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments)--数据文件 10,Sales,Virginia,这是销售\n弗吉尼亚州的办公室 20 ,,,Sales,Virginia,这是销售\n办公室在Virginia
注意：
在Unix上，行结束标记是\n，即CHR(10)，而windows nt平台的行结束标记是\r\n即，CHR(13)||CHR(10); 可以使用控制文件中的trim内置sql函数来截断尾随空白字符
select * from dept;
3）在infile指令上使用VAR属性来加载变宽文件，在文件使用的格式中，每行的前几个字节指定了该行的长度
--控制文件加载 datainfile demo.dat "var 3" -- 表示前三个字节用于将每一行记录到以 结尾的表 deptreplacefields 中的字节数',' 尾随 nullcols(deptno, dname "upper(:dname)", loc "upper(: loc)", comments)--数据文件 05410，销售，弗吉尼亚州，t他是弗吉尼亚州的销售办事处
注意：换行符在 Unix 上仅算作一个字节，在 Windows NT 上仅算作两个字节。 Bytes
select * from dept;
4) 使用 infile 命令上的 STR 属性加载具有特定字符序列的可变宽度文件表示行终止符而不是换行符。 STR 属性以十六进制指定。要获取十六进制字符串，最简单的方法是使用sql和utl_raw生成十六进制字符串。在unix平台中，行尾标记是CHR(10)，我们的特殊字符是管道符号(|)，可以写成：
 select utl_raw.cast_to_raw('|' ||chr(10)) from Dual;--在unix上可以看作x'7C0A'
在windows上使用
选择utl_raw。 cast_to_raw('|'||chr(13)||chr(10)) from Dual;--对于 x'7C0D0A'--控制文件加载 datainfile demo.dat "str x'7C0D0A'" 到表 deptreplacefields 终止于 ', '尾随空值(deptno, dname "upper(:dname)", loc "upper(:loc)", comments)--数据文件 10，销售，弗吉尼亚州，这是弗吉尼亚州的销售办事处|select * from dept;
< p>6。加载 lob 数据
1) 加载内联 lob 数据。这些lob数据通常嵌入换行符和其他特殊字符
--Modify table depttruncate table dept;alter table dept drop column comments;alter table dept add comments clob; --数据文件 10，销售，弗吉尼亚州，这是弗吉尼亚州的销售办公室|20，会计，弗吉尼亚州，这是弗吉尼亚州的会计办公室|30，咨询，弗吉尼亚州，这是弗吉尼亚州的咨询办公室|40，财务，弗吉尼亚州，"这是弗吉尼亚州的财务办公室，它嵌入了逗号，并且比提交的其他评论长得多。如果您觉得需要在此处添加双引号文本，如下所示：""您将需要将这些引号加倍！""将它们保留在字符串。该字段持续最多 1000000 字节（因为我使用的控制文件定义d）或者直到我们达到了记录标记的魔力，|后跟行尾 - 就在这里 ->"|--控制文件加载 datainfile demo.dat "str x' 7C0D0A'" 到表 deptreplacefields 以 ',' 结尾，可选地用 '"' 尾随 nullcols(deptno, dname "upper(:dname)", loc "upper(:loc)", comments char(1000000) --sqlldr默认输入字段都是char(255)，char(1000000)表示最多允许1000000个字符输入) select * from dept;
2) 加载外部 lob 数据。 
需要将包含一些文件名的数据文件加载到lob中，而不是让lob数据与结构化数据混合在一起。这样就无需使用上述 4 种方法之一来避免输入数据中嵌入换行符的问题，这种问题在大量文本或二进制数据中经常发生。 sqlldr 将此附加数据文件称为 lobfile。 sqlldr还可以支持加载结构化数据文件。您可以告诉 sqlldr 如何解析来自另一个 f 的 lob 数据ile，以便它的一部分可以作为每行结构化数据加载。 sqlldr 将此外部引用文件称为复杂的辅助数据文件。 
lobfile数据采用以下格式之一：
固定长度字段（从lobfile加载100到10000字节）；定界字段（以某个字符结尾，或者使用某个字符括号）；--最常见的是，长度/值对以文件结束符（EOF）结尾，这是一个边长字段
 
--用于加载数据表 create table lob_demo(owner varchar2(255), time_stamp date, filename varchar2(255), data blob) - 假设有一个目录包含您想要的文件加载到数据库中。以下是要加载的文件的所有者、时间戳、文件名和文件本身 load data infile *replaceinto table lob_demo(ownerposition(17:25), time_stampposition(44:55) date "Mon DD HH24:MI ",文件名位置(57:100),数据lobfile(文件名)以EOF终止)begindata-rw-r--r-- 1 tkyte tkyte 1220342 jun 17 15:26 classes12.zipselect Owner,time_stamp,filename,dbms_lob.getlength(data) from lob_demo;
3) 将 lob 数据加载到object columns
一般用于加载图片
 create table image_load( id number, name varchar2(255), image ordsys.ordimage) --First最重要的是，你必须了解ordsys.ordimage类型
加载此数据的控制文件如下：
load datainfile *into table image_loadreplacefields终止于 ','(id,name,file_name 填充符,图像列对象(源列对象 ( localdata lobfile(file_name) 终止于 EOF nullif file_name='none' ))) begindata1,icons,icons.gif
< p>注意：列对象告诉sqlldr这不是列名，而是列名的一部分。 
使用的列名是 image.source.localdata
select * from image_load
--继续编辑l的属性oaded data begin for c in (select * from image_load)循环c.image.setproperties;--setproperties是ordsys.ordimage类型提供的方法，它处理图像本身并用适当的值更新对象的其余属性​​end Loop;end;
补充介绍：
使用plsql加载lob数据
创建表demo(id int主键，theclob clob）创建或替换目录 dir1 为 'D :\oracle';SQL> host echo 'hello world!' >d:/oracle/test.txt声明 l_clob clob; l_bfile bfile;开始插入演示值(1,empty_clob())将clob返回到l_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;select dbms_lob.getlength(theclob),theclob from demo;
注意：
创建的目录默认为大写的DIR1。如果目录写成dir1，会提示错误。如果 y如果您想使用大小写混合的目录名，则在创建此类目录时应使用带引号的标识符，如下所示：
创建或替换目录“dir2”为 ' D:\oracle';
这篇关于《如何使用SqlLoader》的文章就分享到这里，希望以上内容能够给大家带来一些帮助，方便大家进一步了解知识。如果您觉得文章不错，请转发出去，让更多的人看到。