异常处理过程

有问题过程会比执行三次还是不行就会停止执行
create or replace procedure exception_procedure is
cursor log_data  is select m.pro_name from  T_DATA_UPDATE_LOG m where m.status=0 and m.info='异常' and to_char(m.begin_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd') group by m.pro_name;
v_sql    varchar2(100);
info_p   varchar2(100);
record_new number(38);
start_time date;
begin
     PRO_DATA_UPDATE_LOG('exception_procedure', '开始', null,null);
  for n in log_data loop
   select count(1) into record_new from  T_DATA_UPDATE_LOG where  PRO_NAME=n.pro_name and to_char(begin_date,'yyyymmdd')>=to_char(sysdate-1,'yyyymmdd') and status=0 and info='异常' order by begin_date desc;

    if record_new < 3 then
    v_sql := 'BEGIN '||n.pro_name||'; END;';
    --v_sql := 'BEGIN pro_job_log('''||'dd'||'''); END;';
    execute immediate v_sql;
     select max(begin_date),info into start_time,info_p from  T_DATA_UPDATE_LOG where pro_name=n.pro_name and to_char(begin_date,'yyyymmdd')=to_char(sysdate,'yyyymmdd') and info='正常' group by info;

           if info_p = '正常' then

           update T_DATA_UPDATE_LOG set status=1 where info='异常' and status=0 and pro_name=n.pro_name;
           COMMIT;
           end if;
    end if;
  end loop;
PRO_DATA_UPDATE_LOG('exception_procedure', 'ok', '结束',1);
exception
  when others then
   /* --sg_log_err('manage_partition',sqlerrm);
    pro_name :='manage_partition';
    err_info :=sqlerrm;
    select sysdate into sj from dual;
   v_Sql := 'insert into err_log values('||'''pro_name'''||','''||err_info||''','''||sj||''')';
    execute immediate v_Sql;
   commit;*/
    PRO_DATA_UPDATE_LOG('exception_procedure', sqlerrm, '异常',0);
    dbms_output.put_line(sqlcode||sqlerrm);

end exception_procedure;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-2121447/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-2121447/