存储过程 替换字符串

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

转载于:https://www.cnblogs.com/lanblogs/p/3279243.html