SqlLoader怎么使用
16lz
2022-01-08
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
load data infile 'c:\data.txt' insert into table ftest fields terminated by " "(id,username,password,sj)
4 在cmd命令窗口中执行
sqlldr fyzh/fyzh control=c:\cont.ctl data=c:\data.txt
查看已成功插入。
重新学习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
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
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
因此，将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
要加载定宽的固定位置数据，将会在控制文件中使用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
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
load datainfile *into table deptreplace(deptno position(1:2), dname position(*:16), loc position(*:29), entire_line position(1:29) ) begindata 10Accounting Virginia,USA
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;
使用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
如果想确保加载的数据是大写的，可以改写控制文件如下：
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
解决方法，就是使用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
假设输入文件中有以下格式的日期：
HH24:MI:SS:只有一个时间；日期时间默认为sysdate
DD/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,'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" ) 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 hh24:mi:ss';select * from dept;
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
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;
--控制文件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
select * from dept;
select utl_raw.cast_to_raw('|'||chr(10)) from dual;--可见在unix上为x'7C0A' 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;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;
需要把包含有一些文件名的数据文件加载在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;
一般用于加载图像
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
使用的列名是image.source.localdata
select * from image_load
使用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';