Oracle外部表与SQLLDR
两种方法建立外部表
-
在建表语句中把EXTERNAL_TABLE参数设定为SQLLDR；
-
从12C起，可以使用模式来运行SQLLDR；
在建表语句中把EXTERNAL_TABLE参数设定为SQLLDR:
创建表dept，如下：
create table dept as select * from scott.dept where 1 = 2;
建立控制文件，如下：
LOAD DATA
INFILE * --指定数据文件，*表示数据就在此控制文件中(BEGINDATA后面的时数据部分)
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
abc,xyz,hello
运行sqlldr命令，如下:
D:\app\Orcl\data_dir>sqlldr eoda/foo@muphy dept.ctl external_table=generate_only
-
external_table为generate_only时，会生成ddl和dml语句防在日志中，不加载数据，然后手动执行这些语句，最后将数据重外部表导入dept表
-
external_table为execute时，会直接加载数据到存在的外部表，需要先创建对应合适的表
从12C起，可以使用模式来运行SQLLDR
数据文件dept.dat，文件内容CSV格式，如下：
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
abc,xyz,hello
创建表dept，如下：
create table dept(
deptno number(2) constraint dept_pk primary key,
dname varchar2(14),
loc varchar2(13)
)
运行sqlldr命令，如下:
D:\app\Orcl\data_dir>sqlldr eoda/foo@muphy table=dept
查询表数据
EODA@muphy> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
外部表的限制
在以下两种情况下不宜使用外部表，更应该使用sqlldr：
-
必须通过网络加载数据，文件不在数据库服务器上。
-
多用户必须使用相同外部表来处理不同的输入文件
多用户与并行问题
可以直接修改外部表指向：
EODA@muphy> alter table sys_sqlldr_x_ext_dept location('file1.bat','file2.bat');
按照特殊字符串区分命名文件：
-
%p:PID
-
%a:并行执行服务器代理ID，并行执行服务器指定了数字001,002,003等。
外部表预处理
预处理可以执行脚本，将脚本的处理后的输出作为数据源(脚本也可以读取数据)
create table csv3
( col1 varchar2(20)
,col2 varchar2(20)
)
organization external
(
type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline
preprocessor exec_dir:'run_sed.bsh'
fields terminated by '|' ldrtrim
)
location
(
data_dir:'load.csv'
)
)
外部表数据泵卸载，与expdp工具文件格式一样
创建目录：
EODA@muphy>create or replace directory data_dir as 'D:\app\Orcl\data_dir';
卸载数据：
create table all_objects_unload
organization external
( type oracle_datapump
default directory data_dir
location('allobjects.dat')
) as
select * from all_objects;
SQLLDR加载数据
加载定界数据：
LOAD DATA
INFILE * --指定数据文件，*表示数据就在此控制文件中(BEGINDATA后面的时数据部分)
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'--可以保护逗号和分号
(DEPTNO,
DNAME char(1000), --默认char255
LOC "upper(:loc)", --使用函数
LAST_UPDATE date 'dd/mm/yyyy') --加载时间
BEGINDATA
10,Sales,"Virginia,USA",1/5/2014
20,Accounting,"Virginia,""USA""",6/5/2014
30,Consulting,Virginia,15/3/2014
加载定长数据：
LOAD DATA
INFILE * --指定数据文件，*表示数据就在此控制文件中(BEGINDATA后面的时数据部分)
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
DNAME position(3) char(10),--固定长度，位置3-12
LOC position(*:20) --*表示上一个结束位置12加1 可以使用*+2
LAST_UPDATE date 'dd/mm/yyyy'
)
BEGINDATA
20AccountingVirginia
加载LOB：
-
创建表：
create table lob_demo
(...
filename varchar2(255),
data blob
）
-
lob内联在普通数据中
数据与控制文件必须分开
可以使用str，var和fix等参数来加载数据
-
INFILE demo.dat "fix 80" --表示每条记录80字节
-
INFILE demo.dat "var 3" --表示每条记录前三字节用于该条记录的长度
-
INFILE demo.dat "str X'7C0A'" --表示自定义|每条记录的分隔符
-
lob外联存储数据
.....
filename position(44:100),
data lobfile(filename) terminated by eof
)
bigindata
-rwxr-xr-x 1 oracle dba 14889 Jui 22 22:01 dl.sql
运行命令：
D:\app\Orcl\data_dir>sqlldr userid=eoda control=dept.ctl
PL/SQL加载lob
创建表
EODA@muphy> create table demo ( id int primary key, theClob clob);
创建测试文件
EODA@muphy> host echo 'hello world!' > D:\app\Orcl\data_dir\test.txt
读取文件
EODA@muphy> declare
l_clob clob;
l_bfile bfile;
begin
insert into demo values(1,empty_clob()) returning theClob into l_clob; --让l_clob指向lob
l_bfile := bfilename('DATA_DIR','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;
读取文件，解决乱码
EODA@muphy> declare
l_clob clob;
l_bfile bfile;
dest_offset integer :=1;
src_offset integer :=1;
src_csidnumber :=NLS_CHARSET_ID('WE8ISO8859P1');
lang_context integer :=dbms_lob.default_lang_ctx;
warning integer;
begin
insert into demo values(1,empty_clob()) returning theClob into l_clob; --让l_clob指向lob
l_bfile := bfilename('DATA_DIR','test.txt');
dbms_lob.fileopen(l_bfile);
dbms_lob.loadclobfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile),dest_offset,src_offset,src_csid,lang_context,warining);
dbms_lob.fileclose(l_bfile);
end;
查询表
EODA@muphy> select dbms_lob.getlength(theClob),theClob from demo;
来自《Oracle编程艺术深入理解数据库体系结构第三版》的学习总结
