1、在数据库中建一个新表用于测试。
create tbale tb_test
(
id number,
blb blob
);
commit;
2、往tb_test表中插入一条新记录用于测试。
insert into tb_test values(1, empty_blob());
commit;
注:往有blob类型的字段的数据表中插入新记录,不能直接填入值,必须先往blob字段插入一个empty_blob(),然后再用dbms_lob.write函数写入blob的值。
3、向id为1的记录的blb字段写入以下字符串:'follow i-75 across the mackinac bridge.你好!';
declare
directions blob;
amount binary_integer;
offset integer;
first_direction varchar2(100);
more_directions varchar2(500);
begin
update tb_test set blb = empty_blob() where id = 1; --更新和新增一样要将blob字段设置为empty_blob()
select blb into directions from tb_test where id = 1 for update; --一定要用for update锁住记录,否则
--dbms_lob.open会出错
dbms_lob.open(directions, dbms_lob.lob_readwrite);
first_direction := 'follow i-75 across the mackinac bridge.你好!';
amount := lengthb(first_direction); --number of characters to write
--有中文必须用lengthb
offset := 1; --begin writing to the first character of the clob
dbms_lob.write(directions, amount, offset, utl_raw.cast_to_raw(first_direction));
--utl_raw.cast_to_raw函数将字符串转换成二进制数
dbms_lob.close(directions);
commit;
end;