CREATE OR REPLACE TRIGGER sbo_c_case_info_look_update INSTEAD OF update ON sbo_c_case_info_look
REFERENCING NEW AS NEW OLD AS OLD
declare
v_user_id number(10);
v_forum_id number(10);
v_blob blob;
v_blob1 blob;--子串
v_post_id number(10);
v_topic_id number(10);
directions blob;
amount integer;
offset2 integer;
v_blob_maxlength integer;--每次读取的最大长度,赋予常量
v_index integer;--索引
v_sub_count integer;--子串的个数
v_cursor integer;--游标
v_temp_len integer;--临时长度值
BEGIN
select user_id into v_user_id from jforum_users ju where ju.userid_master = (select c.userid from pub_t_bu_image bb,secu_t_user c
where :new.FILEDEAL = bb.datakey and bb.upuserid = c.userid);--获得对应的用户id
select jb.vblob into v_blob from (select bb.vblob from sbo_c_case_info_look a,pub_t_bu_image bb,secu_t_user c
where a.FILEDEAL = bb.datakey and bb.upuserid = c.userid and a.datakey=:new.datakey) jb;--获得典型案例内容
select jf.forum_id into v_forum_id from jforum_forums jf where jf.forum_name = '典型案例';
if :new.IFPUBLISH=1 and :new.IFPUBLISH<>:old.IFPUBLISH then
insert into jforum_topics(topic_id,forum_id,topic_title,datakey,user_id) values(jforum_topics_seq.nextval,v_forum_id,:new.CASETITLE,:old.datakey,v_user_id);
insert into jforum_posts(post_id,topic_id,forum_id,user_id,poster_ip,post_time,post_edit_time,enable_html) values(jforum_posts_seq.nextval,jforum_topics_seq.currval,v_forum_id,v_user_id,'127.0.0.1',sysdate,sysdate,0);
select jforum_topics_seq.currval into v_topic_id from dual;
select jforum_posts_seq.currval into v_post_id from dual;--获得post_id
--select UTL_RAW.cast_to_varchar2(v_blob) into v_string from dual;
update jforum_topics jt set jt.topic_first_post_id=v_post_id,jt.topic_last_post_id=v_post_id where jt.topic_id=v_topic_id;--更新两个字段
1, insert into jforum_posts_text(post_id,post_subject,post_text) values(v_post_id,:new.CASETITLE,EMPTY_BLOB()); --更新和新增一样要将blob字段设置为empty_blob()
2, select post_text into directions from jforum_posts_text where post_id=v_post_id for update; --一定要用for update锁住记录,否则
--dbms_lob.open会出错
3, dbms_lob.open(directions, dbms_lob.lob_readwrite);
v_blob_maxlength:=1000;--允许的长度
amount := lengthb(v_blob); --blob的总长度
v_sub_count :=amount/v_blob_maxlength+1;--分成子串的个数
v_index :=1;--索引从1开始
offset2 := 1; --begin writing to the first character of the clob
4, while v_index < v_sub_count
loop
v_cursor:=v_index*v_blob_maxlength;
DBMS_LOB.read(v_blob,v_blob_maxlength,offset2,v_blob1);
v_temp_len:= lengthb(v_blob1);
dbms_lob.writeappend(directions,v_temp_len,v_blob1);
v_index:=v_index+1;
offset2:=v_cursor+1;
end loop;
5, if offset2<amount then
v_temp_len:=amount-offset2;
DBMS_LOB.read(v_blob,v_temp_len,offset2,v_blob1);
dbms_lob.writeappend(directions,lengthb(v_blob1),v_blob1);
end if;
6, dbms_lob.close(directions);
elsif :new.IFPUBLISH=0 and :new.IFPUBLISH<>:old.IFPUBLISH then
delete from jforum_posts_text pt where pt.post_id in (select p.post_id from jforum_posts p where p.topic_id IN (select jt.topic_id from jforum_topics jt where jt.datakey=:new.datakey));
delete from jforum_posts jp where jp.topic_id in (select jt.topic_id from jforum_topics jt where jt.datakey=:new.datakey);
delete from jforum_topics t where t.datakey=:new.datakey;
end if;
END;
/
该触发器最核心的部分是实现两张表中blob字段的拷贝,简单流程图如下:
有了这个流程图和上面的编号,会家子应该可以看明白些了,但我想大部分人还是都不明白。这就要联系到java中流的实现了,这里原理与之极其相似,只是那里的API很多,只需寥寥数行代码即可完成,这里却要什么都得自己来写。比如源的总长度,每次读写的长度,读写的次数,偏移量,最后一次读写的长度。都要考虑到位才行,否则会出现无法读写,读写不完整这些异常。
这里的核心点有以下若干个:
1,第一步,先给目标字段插入一个空数据,empty_blob(),这里无论是insert还是update都要如此。
2,第二步,使用for update锁住该字段,以免其他操作介入,导致出错,这是一个同步机制。
3,第三步,dbms_lob.open(directions, dbms_lob.lob_readwrite);相当于使用directions建立一个读写流。
4,将数据分段读出DBMS_LOB.read()和写入dbms_lob.writeappend()注意两个函数的参数配置。
5,偏移量的手动设置,在进行了循环读写之后,还有最后一段数据没有读写,这时候需要谨慎对待,不要把偏移量弄错。
6,每次读写的最大长度,是32766,多了就不行了。
该方式必定是最笨一种,只是要出结果,急于求成,就暂时采用了,希望各位网友多多赐教。