实战存储过程调度过程

实行功能

    1.可以按流程执行存储

    2.如果中间某一个过程执行错误会立即退出流程

    3.在次执行时候会从错误过程里面继续往下跑

    4.会把调度job信息dispatch_table

create or replace procedure dispatch_produrce is
pro_count     number(38);
v_sql         varchar(800);
v_pro_name    varchar(50);
v_sqlerror    varchar(3000);
v_dis_time    date;
v_num         number(38);
v_status      number(38);
v_info        varchar(100);
v_action      varchar(2000);
v_name    varchar2(100);
v_error       number(38);

--v_jl          number(10);
begin
  PRO_DATA_UPDATE_LOG('dispatch_produrce', '开始', null,null);
  pro_count:=0;
 select pro_name into v_name FROM dispatch_table C WHERE c.cause is not null;

if v_name is not null then
 for m in (select *  from dispatch_table  where id >=(select id FROM dispatch_table C WHERE nvl(length(c.cause),0)>0) order by id)loop
  v_sql := 'BEGIN '||m.pro_name||'; END;';

       execute immediate v_sql;
         v_pro_name:=m.pro_name;
        
       select count(*) into v_error
          from T_DATA_UPDATE_LOG c
         where c.status = 0
           and c.info = '异常'
           and to_char(c.begin_date, 'yyyymmdd') =
               to_char(sysdate, 'yyyymmdd') and c.id in(select max(id) from T_DATA_UPDATE_LOG where pro_name=v_pro_name);
        
         pro_count :=pro_count+1;

    
      
           if v_error>0 then
      
         select action into v_action
          from T_DATA_UPDATE_LOG c
         where c.status = 0
           and c.info = '异常'
           and to_char(c.begin_date, 'yyyymmdd') =
               to_char(sysdate, 'yyyymmdd') and c.id in(select max(id) from T_DATA_UPDATE_LOG where pro_name=v_pro_name);
            v_pro_name:=m.pro_name;
            v_num :=m.num;
            v_status:=m.status;
            update dispatch_table set status=0,cause=v_action,num=pro_count,dispatch_time=sysdate  where pro_name=v_pro_name;
            commit;
            exit;
           end if;
          
          
         
           update dispatch_table set status=1,cause=null,num=pro_count,dispatch_time=sysdate   where pro_name=m.pro_name;
       
        commit;
      
 
 end loop;

end if;
 
PRO_DATA_UPDATE_LOG('dispatch_produrce', 'ok', '结束',1);
  exception
   
     WHEN NO_DATA_FOUND THEN
        for m in (select * from dispatch_table) loop
   
   
    
    --if to_char(sysdate,'yyyymmdd')= to_char(m.dispatch_time,'yyyymmdd') then
      --select  sysdate + interval '60' MINUTE into v_dis_time from dual;
  
    
     
        v_sql := 'BEGIN '||m.pro_name||'; END;';

       execute immediate v_sql;
        v_pro_name:=m.pro_name;
       
       select count(*) into v_error
          from T_DATA_UPDATE_LOG c
         where c.status = 0
           and c.info = '异常'
           and to_char(c.begin_date, 'yyyymmdd') =
               to_char(sysdate, 'yyyymmdd') and c.id in(select max(id) from T_DATA_UPDATE_LOG where pro_name=v_pro_name);
              
        
         pro_count :=pro_count+1;

      
      
      
           if v_error>0 then
             select action into v_action
          from T_DATA_UPDATE_LOG c
         where c.status = 0
           and c.info = '异常'
           and to_char(c.begin_date, 'yyyymmdd') =
               to_char(sysdate, 'yyyymmdd') and c.id in(select max(id) from T_DATA_UPDATE_LOG where pro_name=v_pro_name);
      
        
            v_pro_name:=m.pro_name;
            v_num :=m.num;
            v_status:=m.status;
            update dispatch_table set status=0,cause=v_action,num=pro_count,dispatch_time=sysdate  where pro_name=v_pro_name;
            commit;
            exit;
           end if;
         
          
         
          
           update dispatch_table set status=1,cause=null,num=pro_count,dispatch_time=sysdate   where pro_name=m.pro_name;
       
        commit;
      
     
    end loop;

PRO_DATA_UPDATE_LOG('dispatch_produrce','正常', '结束',1);

    when others then
      rollback;
      dbms_output.put_line(sqlerrm);
       PRO_DATA_UPDATE_LOG('dispatch_produrce', sqlerrm||chr(10)||dbms_utility.format_error_backtrace, '异常',0);
   
end dispatch_produrce;

 

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

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