oracle按rownum更新数据

create or replace procedure repair_accept_final_x_20160520 is
begin
declare
    cursor c_job
    is
    select t.act_code from acceptance t where nvl(t.act_flag,'0')='1' and t.is_history!='1';
     c_row c_job%rowtype;
     v_count1 number;
     v_count2 number;
     v_grant_code number;
     v_wf_id number;
     v_status varchar2(10);
     v_count3 number;
     v_rn number;
begin
   for c_row in c_job loop
     v_count3:=2;
       select t.grant_code into v_grant_code from project t where t.prj_code in(select a.prj_code from acceptance a where a.act_code=c_row.act_code);
       if instr(',429,198,199,200,201,202,203,204,',','||v_grant_code||',')>0 then
         v_wf_id:=120000103;
       else
         v_wf_id:=100000401;
       end if;
       select count(0) into v_count1 from audit_comment t where t.key_code=c_row.act_code and t.comment_type='acceptance_final';
       select count(0) into v_count2 from wf_his_task t where t.op_code=c_row.act_code and t.wf_id=v_wf_id and t.status!='00';
       if v_count1>0 and v_count1=v_count2 then
         for i in 1..v_count1 loop
           select rn,status into v_rn,v_status from (select rownum rn,status from wf_his_task w where w.wf_id=v_wf_id and w.status!='00' and w.op_code=c_row.act_code and rownum<v_count3 order by w.id ) where rn>=i;
           update audit_comment t set t.status=v_status
                  where t.seq_no in(select seq_no from (select seq_no,rownum rn from audit_comment t where t.key_code=c_row.act_code and t.comment_type='acceptance_final' and rownum<v_count3 order by t.seq_no) where rn>=i);
          v_count3:=v_count3+1;
         end loop;
       end if;
   end loop;
end;
end repair_accept_final_x_20160520;
展开阅读全文

没有更多推荐了,返回首页