Oracle中，将VARCHAR2类型的字符串写入_oracle

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; 

欢迎大家阅读《oracle中，将VARCHAR2类型的字符串写入_oracle》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

喜欢 (0)赏

[搞代码]

分享 (0)