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;
//---------------------------------------------------------------------------------------------------------------------------------
上面是在网上看到的,下面是我试的另一种方法:
create or replace procedure InsertNewRecord
is
filename blob;
buffer varchar2(100);
amount INT;
begin
buffer := '我的祖国,伟大的祖国';
amount :=LENGTHB(buffer);
insert into p_fax_send (fax_send_id,file_name) values(1001,EMPTY_BLOB());
select file_name into filename from p_fax_send where fax_send_id = 1001 for update;
dbms_lob.writeappend(filename,amount,UTL_RAW.cast_to_raw(buffer));
commit;
end InsertNewRecord;
不用UTL_RAW.cast_to_raw()的话会报错,类型不匹配。