添加异常处理并改进以下Oracle中的存储过程和作业 [英] Adding exception handling and improving the below stored procedure and job in oracle
问题描述
我在oracle数据库中编写了数据库调度程序作业，该数据库当前配置为每12小时运行一次.此作业仅在mv刷新完成且mv上次刷新时间大于清理作业的上次运行时间时才调用存储过程，该存储过程执行增量表清理任务.
I have written database scheduler job in oracle database which is currently configured to run after every 12 hours. This job call a stored procedure which does the delta table clean up task only if mv refresh is complete and mv last refresh time is greater than last run time of clean up job.
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;
/
现在与此有关的有两个问题
now there are two concerns with respect to this
首先，我没有进行适当的异常处理，因此请告知 如何在上述存储过程中进行适当的异常处理
first I haven't done the proper exception handling so please advise how to proper exception handling in above stored procedure
还请告知如何将逻辑合并到循环中.如果未进行清理，则应休眠15分钟，然后重新尝试.
also please advise how can incorporate the logic in a loop.. if the clean-up doesn’t happen, it should sleep for 15 mins and then re-attempt.
请告知人们如何进行改进 任何建议将不胜感激
Please advise folks how the improvements could be done any suggestions would be appreciated
请问有人可以为此提供建议
Folks can somebody pls advise on this
推荐答案
这里是清理后的版本(当然是未经试用的).我退出循环，因为它似乎可以归结为两个计数，并且我将where子句中的or构造更改为in ()，因为它更简单，而且您缺少一些方括号，因此它会给出错误的结果.
Here is a cleaned-up version (untested of course). I took out the loop as it seemed to boil down to two counts, and I changed the or constructions in your where clauses to in () as it's simpler and you were missing some brackets so it would have given incorrect results.
我从不热衷于对模式名称进行硬编码-也许应该将它们作为参数传递或从配置表中获取?
I'm never keen on hardcoding schema names - perhaps they should be passed as parameters or fetched from a config table?
异常处理看起来很好. (如果没有'TABLE_CLEAN_UP_JOB'，它将失败，并显示no_data_found，但是我想说它应该在这种情况下失败，因为缺少系统的一部分.也许针对特定情况的异常处理程序可以使用以下方法提供更好的消息: raise_application_error，或者只是记录一条消息并继续，如果那是您想要的，则由您决定.)
The exception handling looks fine as it is. (It will fail with a no_data_found if there is no 'TABLE_CLEAN_UP_JOB', but I'd say it should fail in that situation because part of the system is missing. Maybe an exception handler for that specific case could provide a better message using raise_application_error, or just log a message and continue, if that's what you want it to do. It's up to you.)
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分钟运行一次?
I didn't quite understand the scheduling question. You have a job 'TABLE_CLEAN_UP_JOB' that calls the above procedure every 12 hours, and inside the procedure you check for some MV refreshes since the last job run (12 hours ago), but if there weren't any, then you want to reschedule it to retry in 15 minutes. I am probably missing something, but why not just schedule it to run every 15 minutes in the first place?
无论如何，如果您真的想让一个过程调用坐在那里重试最多12个小时，则可以按照以下方式尝试一些操作:
Anyway if you really wanted one procedure call to sit there retrying for up to 12 hours, you might try something along the lines of the following:
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;
(关于休眠时间和尝试迭代次数的幻数应该作为参数传递或在表中进行配置.)
(The magic numbers for how long to sleep and how many iterations to attempt should probably be passed in as parameters or configured in a table.)
这篇关于添加异常处理并改进以下Oracle中的存储过程和作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
