oracle String类型转换成blob类型插入

来源：互联网发布：js 禁止div点击事件编辑：程序博客网时间：2024/05/22 05:27

CERT_1以前是varchar(3000),现在的类型是blob类型，做数据库迁移时，使用导入工具不能直接导入，所以使用SQL，下面是SQL

declare
directions      BLOB;
amount          BINARY_INTEGER;
offset          INTEGER;
first_direction VARCHAR2(3000);
more_directions VARCHAR2(3000);

CURSOR c_TB1 Is

(
Select CERT_ID as ids, CERT_1 as blb From uaapra.tb_user_cert tb1 where CERT_1 is not null);
v_ids integer ;
begin

OPEN c_TB1;
LOOP
    fetch c_TB1
      into v_ids, first_direction;
      update tb_user_cert set cert_1 = empty_blob() where cert_id = v_ids; --更新和新增一样要将BLOB字段设置为EMPTY_BLOB()
      select cert_1 into directions from tb_user_cert where cert_id = v_ids for update; --一定要用for update锁住记录，否则
      --DBMS_LOB.OPEN会出错
      DBMS_LOB.OPEN(directions, DBMS_LOB.LOB_READWRITE);
      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);
      EXIT WHEN c_TB1%NOTFOUND;
    END LOOP;
    COMMIT;
    CLOSE c_TB1;
end;

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xiaxiaorui2003/archive/2009/07/17/4355881.aspx