SqlLoader如何使用

51次阅读
没有评论

共计 11022 个字符,预计需要花费 28 分钟才能阅读完成。

自动写代码机器人,免费开通

这篇文章将为大家详细讲解有关 SqlLoader 如何使用,丸趣 TV 小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。

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 terminated 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 要加载的数据就包含在控制文件本身 into table dept -- 加载到哪个表 fields terminated by  ,  -- 数据加载形式应该是逗号分隔的值(deptno,dname,loc) -- 所要加载的列 begindata -- 告诉 sqlldr 后面的行市要加载到 dept 表的数据 10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance,Virginiacreate table dept(deptno number(2) constraint dept_pk primary key,dname varchar2(14),loc varchar2(13))sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctlselect * from dept;1 10 Sales Virginia2 20 Accounting Virginia3 30 Consulting Virginia4 40 Finance Virginia

sqlldr 导入的四种加载方式:

APPEND:原先的表有数据 就加在后面 INSERT:装载空表 如果原先的表有数据 sqlloader 会停止 默认值 REPLACE:原先的表有数据 原先的数据会全部删除 TRUNCATE:指定的内容和 replace 的相同 会用 truncate 语句删除现存数据

用 SQLLDR 加载数据的 FAQ

1、如何加载定界数据

1)定界数据即用某个特殊字符分隔的数据,可能用引号括起,这是当前平面文件最常见的数据格式。对于定界数据,最常用的格式是逗号分隔值格式。采用这种文件格式,数据中的每个字段与下一个字段用一个逗号分隔。文本串可以用引号括起,这样就串本身包含逗号。如果串还必须包含引号,一般约定是使用两个引号。加载定界数据,相应的典型控制文件与前面例子相似,但是 fields terminated by 子句通常如下指定:

 fields terminated by  ,  optionally enclose by 

它指定用逗号分隔数据字段,每个字段可以用双引号括起。如果把这个控制文件的最后部分修改如下:

 fields terminated by  ,  optionally enclosed by   (deptno,dname,loc) begindata 10,Sales, Virginia,USA  20,Accounting, Va, USA  30,Consulting,Virginia 40,Finance,Virginiaselect * from dept1 10 Sales Virginia,USA2 20 Accounting Va, USA 3 30 Consulting Virginia 4 40 Finance Virginia

2)另一种常用的格式是制表符定界数据。有两种方法使用 terminated by 子句来加载这种数据:

terminated by X 09 – 使用十六进制格式的制表符;若用 ASCII,制表符应该是 9

 terminated by whitespace-- 使用 terminated by whitespaceload datainfile *into table deptreplacefields terminated by whitespace(deptno,dname,loc) begindata 10 Sales Virginia select * from dept;1 10 Sales Virginia-- 使用 terminated by X 09 load datainfile *into table deptreplacefields terminated by X 09 (deptno,dname,loc) begindata 10 Sales Virginiaselect * from dept;1 10

Sales – 因为一旦遇到一个制表符就会输出一个值。

因此,将 10 赋给 deptno,dname 得到了 null,因为在第一个制表符和第二个制表符之间没有数据

3)sqlldr 的 filler 关键字使用

如跳过制表符

load datainfile *into table deptreplacefields terminated by X 09 (deptno,dummy1 filler,dname,dummy2 filler,loc) begindata 10 Sales Virginiaselect * from dept;1 10 Sales Virginia

2、如何加载固定格式数据

要加载定宽的固定位置数据,将会在控制文件中使用 position 关键字。

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29) ) begindata 10Accounting Virginia,USAselect * from dept;1 10 Accounting Virginia,USA

这个控制文件没有使用 terminated by 子句;而是使用了 position 来告诉 sqlldr 字段从哪里开始,到哪里结束。对于 position,我们可以使用重叠的位置,可以在记录中来回反复。如下修改 dept 表:

alter table dept add entire_line varchar(29);

并使用如下控制文件:

load datainfile *into table deptreplace(deptno position(1:2), dname position(3:16), loc position(17:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USAselect * from dept;1 10 Accounting Virginia,USA 10Accounting Virginia,USA

使用 position 时,可以使用相对偏移量,也可以使用绝对偏移量。前面的例子使用了绝对偏移量,明确指定字段从哪开始,从哪结束,也可以将前面的控制文件改写如下:

load datainfile *into table deptreplace(deptno position(1:2), dname position(*:16), loc position(*:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA

* 指示控制文件得出上一个字段在哪里结束。因此,在这种情况下,(*:16)与 (3:16) 是一样的。注意,控制文件可以混合使用相对位置和绝对位置。另外,使用 * 表示法时,可以把它与偏移量相加。例如 dname 从 deptno 结束之后的;两个字符开始,可以使用(*+2:16),即相当于(5:16).

position 子句中的结束位置必须是数据结束的绝对列位置。有时,可能指定每个字段的长度更为容易,特别是如果这些字段是连续的。采用这种

方式,只需告诉 sqlldr:记录从第一个字节开始,然后指定每个字段的长度。如下:

load datainfile *into table deptreplace(deptno position(1) char(2), dname position(*) char(14), loc position(*) char(13), entire_line position(1) char(29) ) begindata 10Accounting Virginia,USA select * from dept;

3、如何加载日期

使用 sqlldr 加载日期只需在控制文件中 date 数据类型,并指定要使用的日期掩码。这个日期掩码与数据库中 to_char 和 to_date 中使用的日期掩码一样。

如修改 dept 表如下:

alter table dept add last_updated date;load datainfile *into table deptreplacefields terminated by  , (deptno, dname, loc, last_updated date  dd/mm/yyyy  ) begindata 10,Accounting,Virginia,1/5/2000select * from dept;1 10 Accounting Virginia 2000-5-1

4、如何使用函数加载数据

如果想确保加载的数据是大写的,可以改写控制文件如下:

load datainfile *into table deptreplacefields terminated by  , (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

如下控制文件加载数据无法导入

load datainfile *into table deptreplacefields terminated by  , (deptno, dname  upper(:dname) , loc  upper(:loc) , last_updated date  dd/mm/yyyy , entire_line  :deptno||:dname||:loc||:last_updated  ) begindata 10,Accounting,Virginia,1/5/2000

1)TRAILING NULLCOLS 的使用:一般默认用的好

解决方法,就是使用 TRAILING NULLCOLS。这样,如果输入记录中不存在某一列的数据,sqlldr 就会为该列绑定一个 null 值。

这种情况下,增加 TRAILING NULLCOLS 会导致绑定变量:entire_line 成为 null。

load datainfile *into table deptreplacefields terminated by  , TRAILING NULLCOLS(deptno, dname  upper(:dname) , loc  upper(:loc) , last_updated date  dd/mm/yyyy , entire_line  :deptno||:dname||:loc||:last_updated  ) begindata 10,Accounting,Virginia,1/5/2000select * from dept;1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1

2)case 在 sqlldr 中的使用

假设输入文件中有以下格式的日期:HH24:MI:SS: 只有一个时间;日期时间默认为 sysdateDD/MM/YYYY: 只有一个日期,时间默认为午夜 0 点 HH24:MI:SS DD/MM/YYYY: 日期时间都显式提供

可用如下的控制文件

load datainfile *into table deptreplacefields terminated by  , TRAILING NULLCOLS(deptno, dname  upper(:dname) , loc  upper(:loc) , last_updated  case when length(:last_updated) 9 then to_date(:last_updated, hh34:mi:ss dd/mm/yyyy) when instr(:last_updated, :) 0 then to_date(:last_updated, hh34:mi:ss) else to_date(:last_updated, dd/mm/yyyy) end  ) begindata10,Sales,Virginia,12:03:03 17/10/200520,Accounting,Virginia,02:23:5430,Consulting,Virginia,01:24:00 21/10/200640,Finance,Virginia,17/8/2005alter session set nls_date_format= dd-mon-yyyy hh34:mi:ss select * from dept;

5、如何加载有内嵌换行符的数据

1)用非换行符的其它字符来表示换行符,并在加载时使用一个 sql 函数用一个 CHR(10)替换该文本。

alter table dept add comments varchar2(4000);-- 使用下列来加载文本 load datainfile *into table deptreplacefields terminated by  , trailing nullcols(deptno, dname  upper(:dname) , loc  upper(:loc) , comments  replace(:comments, \\n ,chr(10))  -- \\n 换行符用 chr(10)这个代替)begindata10,Sales,Virginia,this is the sales\noffice in Virginia

注:调用中必须用 \\n 来表示替换符,而不是 \n

2)在 infile 指令上使用 FIX 属性,加载一个定长平面文件。使用该方法,输入数据必须出现在定长记录中。对于固定位置的数据,使用 FIX 属性就特别合适,这些文件一般为定长文件。另外使用该方法时,数据必须在外部存储,不能存储在控制文件本身。

-- 控制文件 load datainfile demo.dat  fix 80  -- 指定了输入数据文件 demo.dat,这个文件中每个记录 80 字节 into table deptreplacefields terminated by  , trailing nullcols(deptno, dname  upper(:dname) , loc  upper(:loc) , comments)-- 数据文件 10,Sales,Virginia,this is the sales\noffice in Virginia 20,,,Sales,Virginia,this is the sales\noffice in Virginia

注:

在 unix 上,行结束标记是 \n 即 CHR(10), 而 windows nt 平台的行结束标记是 \r\n 即 CHR(13)||CHR(10);可以在控制文件中使用 trim 内置 sql 函数来完成截断尾部的空白符

select * from dept;

3)在 infile 指令在、上使用 VAR 属性,加载一个变宽文件,在该文件使用的格式中,每一行前几个字节指定了这一行的长度

-- 控制文件 load datainfile demo.dat  var 3  -- 表明了前三个字节用于记录每一行的字节数 into table deptreplacefields terminated by  , trailing nullcols(deptno, dname  upper(:dname) , loc  upper(:loc) , comments)-- 数据文件 05410,Sales,Virginia,this is the sales office in Virginia

注:在 unix 上换行符只算一个字节,在 windows nt 上算两个字节

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 上用

 select utl_raw.cast_to_raw(| ||chr(13)||chr(10)) from dual;-- 为 x 7C0D0A -- 控制文件 load datainfile demo.dat  str x 7C0D0A  into table deptreplacefields terminated by  , trailing nullcols(deptno, dname  upper(:dname) , loc  upper(:loc) , comments)-- 数据文件 10,Sales,Virginia,this is the salesoffice in Virginia|select * from dept;

6、加载 lob 数据

1)加载内联的 lob 数据。这些 lob 数据通常内嵌有换行符和其他特殊字符

-- 修改表 depttruncate table dept;alter table dept drop column comments;alter table dept add comments clob;-- 数据文件 10,Sales,Virginia,this is the salesoffice in Virginia|20,Accounting,Virginia,this is the Accountingoffice in Virginia|30,Consuling,Virginia,this is the Consulingoffice in Virginia|40,Finance,Virginia, this is the Financeoffice in Virginia,it has embedded commas and ismuch longer than the other comments filed.If youfeel the need to add double quotes text in here likethis: you will need to double up those quotes! topreserve them in the string. This field keeps going for up to1000000 bytes (because of the control file definition I used)or until we hit the magic and of record marker,the | followed by an end of line - it is right here - |-- 控制文件 load datainfile demo.dat  str x 7C0D0A  into table deptreplacefields terminated by  ,  optionally enclosed by  trailing 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 如何从另外一个文件解析 lob 数据,这样就可以加载其中的一部分作为结构化数据中的每一行。sqlldr 称这种外部引用的文件为复杂二级数据文件。

lobfile 数据采用以下某种格式:

定长字段(从 lobfile 加载字节 100 到 10000);定界字段(以某个字符结束,或用某个字符括起);– 最常见,以一个文件结束符(EOF)结束 长度 / 值对,这是一个边长字段

-- 加载数据的表 create table lob_demo(owner varchar2(255),time_stamp date,filename varchar2(255),data blob)-- 假设有一目录,其中包含想要加载到数据库中的文件。以下为想要加载文件的 owner,time_stamp,文件名及文件本身 load data infile *replaceinto table lob_demo(owner position(17:25), time_stamp position(44:55) date  Mon DD HH24:MI ,filename position(57:100),data lobfile(filename) terminated by 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 数据加载到对象列

一般用于加载图像

 create table image_load( id number, name varchar2(255), image ordsys.ordimage) -- 首先要了解 ordsys.ordimage 类型

加载这种数据的控制文件如下所示:

load datainfile *into table image_loadreplacefields terminated by  , (id,name,file_name filler,image column object( source column object ( localdata lobfile(file_name) terminated by EOF nullif file_name= none  )))begindata1,icons,icons.gif

注:column object 告诉 sqlldr 这不是一个列名,而是列名的一部分。

使用的列名是 image.source.localdata

select * from image_load
-- 继续编辑加载进来数据的属性 begin for c in (select * from image_load) loop c.image.setproperties;--setproperties 是 ordsys.ordimage 类型提供的方法,处理图像本身,并用适当的值更新对象的其余属性  end loop;end;

额外介绍:

使用 plsql 加载 lob 数据

create table demo (id int primary key,theclob clob)create or replace directory dir1 as  D:\oracle SQL  host echo  hello world!   d:/oracle/test.txtdeclare l_clob clob; l_bfile bfile;begin insert into demo values (1, empty_clob()) returning theclob into 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 就会提示错误,如果要想使用混有大小写的目录名,在创建这样的目录时应该带引号的标识符,如下所示:

create or replace directory  dir2  as  D:\oracle

关于“SqlLoader 如何使用”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。

向 AI 问一下细节

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-12-04发表,共计11022字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)