客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
SQL*Loader（SQLLDR）是Oracle的高速批量数据加载工具。这是一个非常有用的工具，可用于多种平面文件格式向Oralce数据库中加载数据。今天看了申请了*loader的使用，自己小试了下，记录在这 1、假设要插入数据的表ftest，字段是（id,username,password,sj） 2、导入表的数据 以txt格式存储，名为data.txt 1 f f 2010-8-19 2 f1 f1 2010-8-19 3 f2 f2 2010-8-19 4 f3 f3 2010-8-19 5 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 data infile * --告诉sqlldr要加载的数据就包含在控制文件本身 into table dept --加载到哪个表 fields terminated by ',' --数据加载形式应该是逗号分隔的值 (deptno,dname,loc) --所要加载的列 begindata --告诉sqlldr后面的行市要加载到dept表的数据 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia create table dept (deptno number(2) constraint dept_pk primary key, dname varchar2(14), loc varchar2(13) ) sqlldr userid=gwm/gwm@fgisdb control=c:\demol.ctl select * from dept; 1 10 Sales Virginia 2 20 Accounting Virginia 3 30 Consulting Virginia 4 40 Finance Virginia sqlldr导入的四种加载方式： APPEND ：原先的表有数据 就加在后面 用SQLLDR加载数据的FAQ 1、如何加载定界数据 1)定界数据即用某个特殊字符分隔的数据，可能用引号括起，这是当前平面文件最常见的数据格式。 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,Virginia select * from dept 1 10 Sales Virginia,USA 2 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 whitespace load data infile * into table dept replace fields terminated by whitespace (deptno,dname,loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales Virginia --使用terminated by X'09' load data infile * into table dept replace fields terminated by X'09' (deptno,dname,loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales --因为一旦遇到一个制表符就会输出一个值。 因此，将10赋给deptno，dname得到了null，因为在第一个制表符和第二个制表符之间没有数据 3）sqlldr的filler关键字使用 如跳过制表符 load data infile * into table dept replace fields terminated by X'09' (deptno,dummy1 filler,dname,dummy2 filler,loc) begindata 10 Sales Virginia select * from dept; 1 10 Sales Virginia 2、如何加载固定格式数据 要加载定宽的固定位置数据，将会在控制文件中使用position关键字。 load data infile * into table dept replace (deptno position(1:2), dname position(3:16), loc position(17:29) ) begindata 10Accounting Virginia,USA select * from dept; 1 10 Accounting Virginia,USA 这个控制文件没有使用terminated by子句；而是使用了position来告诉sqlldr 字段从哪里开始，到哪里结束。 alter table dept add entire_line varchar(29); 并使用如下控制文件： load data infile * into table dept replace (deptno position(1:2), dname position(3:16), loc position(17:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA select * from dept; 1 10 Accounting Virginia,USA 10Accounting Virginia,USA 使用position时，可以使用相对偏移量，也可以使用绝对偏移量。前面的例子使用了绝对偏移量，明确指定字段从哪开始，从哪结束，也可以将 load data infile * into table dept replace (deptno position(1:2), dname position(*:16), loc position(*:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA *指示控制文件得出上一个字段在哪里结束。因此，在这种情况下，(*:16)与(3:16)是一样的。注意，控制文件可以混合使用相对位置和绝对位置。 position子句中的结束位置必须是数据结束的绝对列位置。有时，可能指定每个字段的长度更为容易，特别是如果这些字段是连续的。采用这种 方式，只需告诉sqlldr：记录从第一个字节开始，然后指定每个字段的长度。如下： load data infile * into table dept replace (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 data infile * into table dept replace fields terminated by ',' (deptno, dname, loc, last_updated date 'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select * from dept; 1 10 Accounting Virginia 2000-5-1 4、如何使用函数加载数据 如果想确保加载的数据是大写的，可以改写控制文件如下： load data infile * into table dept replace fields terminated by ',' (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated date 'dd/mm/yyyy' ) begindata 10,Accounting,Virginia,1/5/2000 select * from dept; 1 10 ACCOUNTING VIRGINIA 2000-5-1 如下控制文件加载数据无法导入 load data infile * into table dept replace fields 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 data infile * into table dept replace fields 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/2000 select * from dept; 1 10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 2000-5-1 2）case在sqlldr中的使用 假设输入文件中有以下格式的日期： 可用如下的控制文件 load data infile * into table dept replace fields terminated by ',' TRAILING NULLCOLS (deptno, dname "upper(:dname)", loc "upper(:loc)", last_updated "case when length(:last_updated)>9 then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy') when instr(:last_updated,':')>0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end" ) begindata 10,Sales,Virginia,12:03:03 17/10/2005 20,Accounting,Virginia,02:23:54 30,Consulting,Virginia,01:24:00 21/10/2006 40,Finance,Virginia,17/8/2005 alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'; select * from dept; 5、如何加载有内嵌换行符的数据 1）用非换行符的其它字符来表示换行符，并在加载时使用一个sql函数用一个CHR(10)替换该文本。 alter table dept add comments varchar2(4000); --使用下列来加载文本 load data infile * into table dept replace fields terminated by ',' trailing nullcols (deptno, dname "upper(:dname)", loc "upper(:loc)", comments "replace(:comments,'\\n',chr(10))" --'\\n'换行符用chr(10)这个代替 ) begindata 10,Sales,Virginia,this is the sales\noffice in Virginia 注：调用中必须用\\n来表示替换符，而不是\n 2）在infile指令上使用FIX属性，加载一个定长平面文件。 --控制文件 load data infile demo.dat "fix 80" --指定了输入数据文件demo.dat，这个文件中每个记录80字节 into table dept replace fields 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)； select * from dept; 3)在infile指令在、上使用VAR属性，加载一个变宽文件，在该文件使用的格式中，每一行前几个字节指定了这一行的长度 --控制文件 load data infile demo.dat "var 3" --表明了前三个字节用于记录每一行的字节数 into table dept replace fields 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属性，加载一个变宽文件，其中用某个字符序列来表示行结束符，而不是用换行符表示
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 data
infile demo.dat "str x'7C0D0A'"
into table dept
replace
fields terminated by ','
trailing nullcols
(deptno,
dname "upper(:dname)",
loc "upper(:loc)",
comments
)
--数据文件
10,Sales,Virginia,this is the sales
office in Virginia|
select * from dept;
6、加载lob数据 1）加载内联的lob数据。这些lob数据通常内嵌有换行符和其他特殊字符 --修改表dept truncate table dept; alter table dept drop column comments; alter table dept add comments clob; --数据文件 10,Sales,Virginia,this is the sales office in Virginia| 20,Accounting,Virginia,this is the Accounting office in Virginia| 30,Consuling,Virginia,this is the Consuling office in Virginia| 40,Finance,Virginia,"this is the Finance office in Virginia,it has embedded commas and is much longer than the other comments filed.If you feel the need to add double quotes text in here like this:""you will need to double up those quotes!""to preserve them in the string. This field keeps going for up to 1000000 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 data infile demo.dat "str x'7C0D0A'" into table dept replace fields 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种方法之一来避开输入数据中 lobfile数据采用以下某种格式: 定长字段（从lobfile加载字节100到10000）； --加载数据的表 create table lob_demo (owner varchar2(255), time_stamp date, filename varchar2(255), data blob) --假设有一目录，其中包含想要加载到数据库中的文件。以下为想要加载文件的owner，time_stamp，文件名及文件本身 load data infile * replace into 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.zip select 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 data
infile *
into table image_load
replace
fields terminated by ','
(id,
name,
file_name filler,
image column object
(
source column object
(
localdata lobfile(file_name) terminated by EOF
nullif file_name='none'
)
)
)
begindata
1,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.txt
declare
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怎么使用的相关资料，希望大家喜欢。 |
请发表评论