在oracle中增加了异常处理，改进了以下存储过程和作业

Question

我已经在oracle数据库中编写了数据库调度器作业，该数据库目前配置为每12小时运行一次。这个作业调用一个存储过程，只有当mv刷新完成并且mv上次刷新时间大于清理作业的上次运行时间时，存储过程才执行增量表清理任务。

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'TABLE_CLEAN_UP_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'TABLE_CLEAN_UP',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly;interval=12',
    enabled         => TRUE);
END;
/

下面的

是存储过程

create or replace procedure TABLE_CLEAN_UP
is
refresh_date timestamp(6);
v_exists NUMBER;
outcome VARCHAR2(100);
cnt NUMBER;
i NUMBER := 0;
begin
SELECT count(1) into cnt FROM all_mviews WHERE owner = 'M_TO' AND mview_name = 'DC_CASHFLOW_VIEW' or  mview_name = 'DC_CASHFLOW_VIEW_ZERO' and LAST_REFRESH_TYPE='COMPLETE';
   if cnt=2 then
     FOR rec IN (SELECT * FROM all_mviews WHERE owner = 'P_SM_TO' AND mview_name = 'DC_CASHFLOW_VIEW' or  mview_name = 'DC_CASHFLOW_VIEW_ZERO' and LAST_REFRESH_TYPE='COMPLETE')
     LOOP
            Select LAST_START_DATE into refresh_date from USER_SCHEDULER_JOBS 
where JOB_NAME='TABLE_CLEAN_UP_JOB';
          if (CAST(rec.LAST_REFRESH_DATE AS TIMESTAMP) > refresh_date) then
                  i := i + 1;  
      end if; 
     END LOOP;
     if i=2 then
            delete DC_CASHFLOW_DELTA;
     end if; 
   end if;
end;
/

关于这个

有两个问题

首先，我没有做适当的异常处理，所以请告知如何正确处理上述存储过程中的异常

还请告知如何在循环中合并逻辑。如果清理没有发生，它应该休眠15分钟，然后重新尝试。

请告诉大家如何改进如有任何建议，不胜感激。

谁能给点建议呢

Answer 1

这是一个清理后的版本(当然是未经测试的)。我去掉了循环，因为它似乎归结为两个计数，我把你的where子句中的or结构改成了in ()，因为它更简单，你缺少了一些括号，所以它会给出不正确的结果。

我从不热衷于硬编码模式名称-也许他们应该作为参数传递或从配置表中获取?

异常处理看起来很好。(如果没有'TABLE_CLEAN_UP_JOB'，它将与no_data_found一起失败，但我认为它应该在这种情况下失败，因为系统的一部分丢失了。也许针对特定情况的异常处理程序可以使用raise_application_error提供更好的消息，或者只是记录消息并继续，如果这是您想要它做的。由你决定。

create or replace procedure table_clean_up
is
    v_refresh_date   date;
    v_table_count_m  integer;
    v_table_count_p  integer;
begin
    select count(*) into v_table_count_m
    from   all_mviews
    where  owner = 'M_TO'
    and    mview_name in ('DC_CASHFLOW_VIEW','DC_CASHFLOW_VIEW_ZERO')
    and    last_refresh_type = 'COMPLETE';
    if v_table_count_m = 2 then
        select cast(last_start_date as date) into v_refresh_date
        from   user_scheduler_jobs
        where  job_name = 'TABLE_CLEAN_UP_JOB';
        select count(*) into v_table_count_p
        from   all_mviews m
        where  m.owner = 'P_SM_TO'
        and    m.mview_name in ('DC_CASHFLOW_VIEW', 'DC_CASHFLOW_VIEW_ZERO')
        and    m.last_refresh_type = 'COMPLETE'
        and    m.last_refresh_date > v_refresh_date;
        if v_table_count_p = 2 then
            delete dc_cashflow_delta;
        end if;
    end if;
end;

我不太明白日程安排的问题。您有一个作业'TABLE_CLEAN_UP_JOB'，它每12小时调用一次上述过程，并且在该过程中，您检查自上次作业运行(12小时前)以来的一些MV刷新，但是如果没有，那么您希望重新安排它，以便在15分钟内重试。我可能遗漏了一些东西，但为什么不首先安排它每15分钟运行一次呢?

无论如何，如果您真的希望一个过程调用在那里重新尝试长达12小时，您可能会尝试以下内容:

create or replace procedure table_clean_up
is
    v_refresh_date   date;
    v_table_count_m  integer;
    v_table_count_p  integer;
    v_loopcount      integer := 48;
begin
    while v_loopcount > 0 loop
        select count(*) into v_table_count_m
        from   all_mviews
        where  owner = 'M_TO'
        and    mview_name in ('DC_CASHFLOW_VIEW','DC_CASHFLOW_VIEW_ZERO')
        and    last_refresh_type = 'COMPLETE';
        if v_table_count_m = 2 then
            select cast(last_start_date as date) into v_refresh_date
            from   user_scheduler_jobs
            where  job_name = 'TABLE_CLEAN_UP_JOB';
            select count(*) into v_table_count_p
            from   all_mviews m
            where  m.owner = 'P_SM_TO'
            and    m.mview_name in ('DC_CASHFLOW_VIEW', 'DC_CASHFLOW_VIEW_ZERO')
            and    m.last_refresh_type = 'COMPLETE'
            and    m.last_refresh_date > v_refresh_date;
            if v_table_count_p = 2 then
                delete dc_cashflow_delta;
                exit;
            end if;
        end if;
        dbms_lock.sleep(60 * 15);
        v_loopcount := v_loopcount -1;
    end loop;
end;

(休眠时间和尝试迭代次数的神奇数字可能应该作为参数传递或在表中配置)