数据类型

create table mytable(id blob);
create table mytable(id clob);
create table mytable (dd date);
insert into mytable values(current_timestamp);
select * from mytable;
create table mytable (dd number(20,3));
create table mytable (dd varchar2(20));
create table mytable (dd NVARCHAR2(10));
create table mytable (dd rowid);
create table mytab (name1 BINARY_DOUBLE);
create table mytab (name1 BINARY_FLOAT);
create table test(id int, val binary_float);
insert into test values (1, 1.25),(2, 8.234), (3, 0.1234);

1、创建directory对象
create directory d_bfile as '/tmp/data/bfiletest';
2、创建包含bfile数据类型列的表
create table testbfile(id number,bfile_name bfile);
3、将bfilename函数构造的数据插入到表中
insert into testbfile values(1,bfilename('d_file', 'bfile.data'));
4、从表中读取数据，调用dbms_lob接口对数据进行操作
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, '');

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;

--实际的返回结果应是将文件bfile.data中的对应长度的内容打印到界面
select f_read_bfile();

--bfile数据修改示例
update bfiletest set bfile_name=bfilename('d_bfile',bfile.data') where id=1;