VastbaseG100

DBMS_LOB

• 用于提供一组对blob、clob和 bfile数据类型操作的函数或过程。

  ---为clob时：
  create table testclob(data clob);
  insert into testclob values('中文测试');
  select dbms_lob.getlength(data) from testclob;
  ---为blob时：
  create table testblob(data blob);
  insert into testblob values(utl_raw.cast_to_raw('中文测试'));
  select dbms_lob.getlength(data) from testblob;
  ---为bfile时：
  存在数据文件/tmp/data/bfiletest/bfile.data
  create directory d_bfile as '/tmp/data/bfiletest';
  create table testbfile(id number,bfile_name bfile);
  insert into testbfile values(1,bfilename('d_file', 'bfile.data'));
  select dbms_lob.getlength(bfile_name) from testbfile where id=1;
  select dbms_lob.fileopen(bfilename('d_file', 'bfile.data'),0);
  select dbms_lob.read(bfilename('d_bfile','bfile.data'), 1, 0, '');
  
  存在数据文件/tmp/data/bfiletest/bfile.data
  --创建目录和表
  create directory d_bfile as '/tmp/data/bfiletest';
  create table testbfile1(id number,bfile_name bfile);
  --调用bfilename函数构造bfile数据并插入到表中
  insert into testbfile1 values(1,bfilename('d_bfile', 'bfile.data'));
  set serveroutput on;
  create or replace function f_read_bfile() returns void as $$
  declare
  buff raw;
  amount int := 0 ;
  offset int :=0;
  lob_loc bfile;
  filesize int;
  begin
  select bfile_name into lob_loc from testbfile1 where id=1;
  --打开BFILE文件
  dbms_lob.fileopen(lob_loc,0);
  --获取文件大小
  filesize := dbms_lob.getlength(lob_loc);
  raise notice 'amount:%',amount;
  --读取文件全部内容
  amount = filesize;
  dbms_lob.read(lob_loc,amount,0,buff);
  dbms_output.put_line('file data:');
  dbms_output.put_line(utl_raw.cast_to_varchar2(buff));
  --读取文件前8字节内容
  amount = 8;
  dbms_lob.read(lob_loc,amount,offset,buff);
  dbms_output.put_line('First eight bytes:');
  dbms_output.put_line(utl_raw.cast_to_varchar2(buff));
  --读取文件剩余内容
  offset = amount;
  amount = filesize - mount;
  dbms_lob.read(lob_loc,amount,offset,buff);
  dbms_output.put_line('The rest bytes:');
  dbms_output.put_line(utl_raw.cast_to_varchar2(buff));
  --关闭bfile文件
  dbms_lob.fileclose(lob_loc);
  return;
  end;
  $$ language plpgsql;
  
  select f_read_bfile();
  --实际的返回结果应是将文件bfile.data中的对应长度的内容打印到界面