實戰儲存過程排程過程

實行功能

    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;