预置条件:
- 环境:达梦数据库已启动
- 数据库服务器创建/opt/bigdata目录,拷贝1.txt,2.png文件至该目录下;修改目录权限 :chown -R dmdba:dinstall /opt/bigdata
--创建大字段表
drop table if exists bigdata_test;
create table bigdata_test(filename varchar,file1 clob,png blob);
create or replace directory images as '/opt/bigdata/';
insert into bigdata_test values ('1',null,null);
--执行以下语句读取文件,进行更新
declare
l_bfile bfile;
l_bfile2 bfile;
l_blob blob;
l_clob clob;
begin
update bigdata_test set png=empty_blob() where filename=1;
update bigdata_test set file1=empty_clob() where filename=1;
select png into l_blob from bigdata_test where filename=1;
l_bfile:=bfilename('IMAGES','2.png');
dbms_lob.open(l_bfile,dbms_lob.file_readonly);
dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.close(l_bfile);
select file1 into l_clob from bigdata_test where filename=1;
l_bfile2:=bfilename('IMAGES','1.txt');
dbms_lob.open(l_bfile2,dbms_lob.file_readonly);
dbms_lob.LOADCLOBFROMFILE(l_clob,l_bfile2,dbms_lob.getlength(l_bfile2),1,1,0,1,1);
dbms_lob.close(l_bfile2);
commit;
end;
--查看数据
select *from bigdata_test;