如何将VARCHAR2类型的字符串写入BLOB字段？

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;

本文地址：http://www.45fan.com/a/question/71878.html