存储过程 替换字符串

declare
cursor c1 is select * from tb_yqcj_resultinfo for update;
begin
for cur1 in c1 loop
if instr(cur1.link,'www.hngp.gov.cn')>0 then
update tb_yqcj_resultinfo set link=replace(link,'www.hngp.gov.cn//','www.hngp.gov.cn/')
where current of c1;
end if;
end loop;
commit;
end;

模仿与：

declare 

cursor c1 is select * from a_test for update;

begin

for cur1 in c1 loop

  for cur2 in (select * from b_test)loop

    if instr(cur1.formula_det,cur2.element_id)>0 then

      update a_test set formula_det=replace(formula_det,cur2.element_id,cur2.name)

        where current of c1;

    end if;

  end loop;

end loop;

commit;

end;

 

学习语句:

insert into tb_qycj_trigger_reulst
select t7.*,t8.duty_id
from tb_yqcj_resultinfo t7,
(select t5.duty_id, t5.add_id, t6.keyword_name
from tb_yqcj_dutylist t5,
(select t2.*
from tb_yqcj_keywordtype t2,
(select t1.duty_id
from qrtz_triggers t, tb_qycj_trigger_duty_rel t1
where t.trigger_name =
'd618a16e-b2ca-4796-94ed-1f7b1fd65c8e'

and t.trigger_name = t1.trigger_name) t3
where t2.duty_id = t3.duty_id) t6
where t5.duty_id = t6.duty_id) t8
where t7.description = t8.add_id
and instr(t7.title_result, t8.keyword_name) >= 1
and t7.create_date >= sysdate - 2
and t7.ri_id not in
(select t9.ri_id
from tb_qycj_trigger_reulst t9
where t9.create_date > sysdate - 2);

select * from tb_qycj_trigger_duty_rel