写入到blob字段的存储过程

create or replace procedure write2blob(directions blob,
                                      v_blob     blob,
                                      v_pos      integer
                                      
                                      )is

  v_1time_maxlength integer; --一次读取的最大长度
  amount            integer; --总长度
  v_blob_sub        blob; --一次读取的长度
  v_index           integer; ---索引
  v_sub_count       integer;
  offset            integer;
  directions2       blob;
begin

  v_1time_maxlength := 32767; --允许的长度
  amount            := length(v_blob); --blob的总长度
  v_sub_count       := amount / v_1time_maxlength + 1; --分成子串的个数
  v_index           := 1; --索引从1开始
  offset            := v_pos;
  directions2       := directions;
  loop
    dbms_output.put_line(length(v_blob));  
  
    DBMS_LOB.read(v_blob, v_1time_maxlength, offset, v_blob_sub); --读至v_blob_sub中
    dbms_lob.writeappend(directions2, length(v_blob_sub), v_blob_sub); --将v_blob_sub中的内容写到字段中
    v_index := v_index + 1;
  
    offset := v_index * v_1time_maxlength + 1;
    exit when v_index > v_sub_count;
  
  end loop;

  dbms_lob.close(directions2);
exception
  when no_data_found then
    dbms_output.put_line('找不到数据');
end;

测试代码：

-- Created on 2013-3-8 by ZHANGXL 
declare 
  -- Local variables here
  i integer;
  v_blob blob;
  directions blob;
  v_post_id number(18):=8814;
begin
  select pt.post_text into v_blob from jforum_posts_text pt where pt.post_id=8817;
  -- Test statements here
  --execute updateblob(jforum_posts_text,post_id,post_text,8817,0,v_blob);
   --update jforum_posts_text pt
    --                       set pt.post_text = EMPTY_BLOB()
    --                     where pt.post_id = v_post_id;
  select pt.post_text into directions from jforum_posts_text pt where  pt.post_id=v_post_id for update; --锁住
  dbms_lob.open(directions, dbms_lob.lob_readwrite);--打开读写流
    write2blob(directions,v_blob,1);
  commit;
end;

表结构：

-- Create table
create table JFORUM_POSTS_TEXT
(
  POST_ID      NUMBER(18) not null,
  POST_TEXT    BLOB,
  POST_SUBJECT VARCHAR2(200),
  ISDELETED    NUMBER(1) default 0
)
tablespace XZFY
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 128
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table JFORUM_POSTS_TEXT
  add primary key (POST_ID)
  using index 
  tablespace XZFY
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

以上的存储过程都是自己写出来的，以下是根据不同的方法进行改造的，最要的问题在于一个超过32766长度的字符串，如果进行循环写入。改进如下：

CREATE OR REPLACE PROCEDURE INVHL_UPDATE_BLOB_DATA(p_table_name      IN VARCHAR2,--表名
                                 p_blob_field_name IN VARCHAR2,--blob列名
                                 p_where_condition IN VARCHAR2,--主键
                                 p_id              IN VARCHAR2,--id
                                 p_position        IN NUMBER,--开始位置，偏移量
                                 p_blob_data       IN blob)--数据
/*表名table_name，
   clob字段名field_name
   确定要更新唯一记录的where条件p_where_condition
  开始处理字符的位置p_position，
  传入的字符串变量P_clob_data*/
 IS
  v_lobloc       BLOB;
  v_blob_data    blob;
  v_amount       BINARY_INTEGER;
  v_position     BINARY_INTEGER;
  v_query_string VARCHAR2(1000);
  v_sub_length   binary_integer;
  v_sub_blob     blob;
  offset         binary_integer;
BEGIN
  v_position     := p_position * 32766 + 1;
  v_amount       := LENGTH(p_blob_data);
  v_blob_data    := p_blob_data;
  v_sub_length   := 32766;
  offset         :=1;
  v_query_string := 'SELECT ' || p_blob_field_name || ' FROM ' ||
                    p_table_name || ' WHERE ' || p_where_condition ||'='||p_id||
                    ' FOR UPDATE';
  dbms_output.put_line(v_query_string);
  --initialize buffer with data to be inserted or updated
  EXECUTE IMMEDIATE v_query_string
    INTO v_lobloc;

  --from pos position, write 32766 varchar2 into lobloc

    /** loop
        dbms_output.put_line(length(v_lobloc));
        dbms_output.put_line('v_amount='||v_amount||'v_sub_length='||v_sub_length||'offset='||offset);
        DBMS_LOB.read(v_lobloc, v_sub_length, offset, v_sub_blob);
        dbms_output.put_line('v_amount='||v_amount||'v_sub_length='||v_sub_length||'offset='||offset);
        DBMS_LOB.writeappend(v_lobloc, v_amount, v_sub_blob);
        offset:=offset+length(v_sub_blob);
        
         exit when offset >= v_amount;
                 
     end loop;**/

  DBMS_LOB.writeappend(v_lobloc, v_amount, v_blob_data);
  select lengthb(post_text) into v_amount from jforum_posts_text pt where post_id = p_id;
  dbms_output.put_line('v_amount'||v_amount);
  commit;
  
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

测试代码：

-- Created on 2013-3-8 by ZHANGXL 
declare 
  -- Local variables here
  i integer;
  v_blob blob;
  directions blob;
  v_post_id number(18):=8814;
  offset integer :=1;
  v_sub_length integer :=32766;
  v_sub_blob blob;
begin
  select pt.post_text,lengthb(pt.post_text) into v_blob,i from jforum_posts_text pt where pt.post_id=8817;
  dbms_output.put_line('i='||i);
     
  
  -- Test statements here
  --execute updateblob(jforum_posts_text,post_id,post_text,8817,0,v_blob);
   /**update jforum_posts_text pt
                           set pt.post_text = EMPTY_BLOB()
                         where pt.post_id = v_post_id;
  select pt.post_text into directions from jforum_posts_text pt where  pt.post_id=v_post_id for update; --锁住
  dbms_lob.open(directions, dbms_lob.lob_readwrite);--打开读写流
    write2blob(directions,v_blob,1);
  commit;**/
  update jforum_posts_text pt
                           set pt.post_text = EMPTY_BLOB()
                         where pt.post_id = v_post_id;
                         
    if i > 32766 then 
       while offset < i loop
           DBMS_LOB.read(v_blob, v_sub_length, offset,v_sub_blob);
            invhl_update_blob_data('jforum_posts_text','post_text','post_id',v_post_id,1,v_sub_blob);
            offset:=length(v_sub_blob)+offset;
       end loop;
       else
            invhl_update_blob_data('jforum_posts_text','post_text','post_id',v_post_id,1,v_blob);
    end if;
 
  commit;
end;

可以继续改进的，需要继续研究。