LOB数据加载一例:
--(1)、 Create table
create table LOB_TAB
(
ID NUMBER,
CLOB_COL CLOB,
BLOB_COL BLOB,
BFILE_COL BFILE
)
;
--(2)、 Create directory
create or replace directory ORCL_DIR
as 'E:\orcl_dir';
--(3)、LOB数据插入脚本
declare
V_files bfile; --此参数主要为blob和clob指定二进制文件位置用
V_bfiles bfile;
V_blobs blob;
V_clobs clob;
dest_offset number;
src_offset number;
no_warning number:=dbms_lob.no_warning;
def_lang_ctx number:=dbms_lob.default_lang_ctx;
begin
--1、load befile from file
V_bfiles := bfilename('ORCL_DIR', 'cpjtxx.txt');
--2、create two temporary lob
dbms_lob.createtemporary(lob_loc => V_blobs,
cache => true,
dur => dbms_lob.call);
dbms_lob.createtemporary(lob_loc => V_clobs,
cache => true,
dur => dbms_lob.call);
--3、load blob from file
V_files := bfilename('ORCL_DIR', '18105.jpg');
dest_offset:=1;
src_offset:=1;
dbms_lob.open(V_files,dbms_lob.lob_readonly);
dbms_lob.loadblobfromfile(dest_lob => V_blobs,
src_bfile => V_files,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset,
src_offset => src_offset);
dbms_lob.close(V_files);
--4、load clob from file
V_files :=bfilename('ORCL_DIR','cpjtxx.txt');
dest_offset:=1;
src_offset:=1;
dbms_lob.open(V_files,dbms_lob.lob_readonly);
dbms_lob.loadclobfromfile(dest_lob => V_clobs,
src_bfile => V_files,
amount => dbms_lob.lobmaxsize,
dest_offset => dest_offset,
src_offset => src_offset,
bfile_csid => nls_charset_id('ZHS16GBK'),
lang_context => def_lang_ctx,
warning => no_warning);
dbms_lob.close(V_files);
--5、insert into test_lob table
insert into Lob_Tab(ID,CLOB_COL,BLOB_COL,BFILE_COL)
select 1,V_clobs,V_blobs,V_bfiles from dual;
commit;
--6、free temporary lob
dbms_lob.freetemporary(lob_loc => V_blobs);
dbms_lob.freetemporary(lob_loc => V_clobs);
end;
/
其他说明:
1、LOB类型数据库性能测试发现,如果数据库无lob查询io操作数是,则数据库有lob但是不查询lob的io操作大概2,数据库有lob并且查询lob的io操作大概14。通过这个结论可以看出lob是很影响表的查询性能的,如果一个张表里面有要用到lob数据类型,建议独立出一张表,这样即使查询非lob字段也不会影响查询的性能。Lob能不用尽量别用,可以用静态文件代替。
2、其实在pl/sql Developer中,用for update语句也可以手动插入、查看LOB数据,但速度有点慢。