oracle DBMS_SCHEDULER.DROP_JOB 仅当存在时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20178030/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DBMS_SCHEDULER.DROP_JOB only if exists
提问by user2183505
I have a sql script that I must run after I import a dump. among other things the script does, it does the following:
我有一个 sql 脚本，我必须在导入转储后运行它。除其他外，脚本还执行以下操作：
BEGIN
--remove program
SYS.DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'STATISTICS_COLUMNS_PROG',FORCE=>TRUE);
--remove job
SYS.DBMS_SCHEDULER.DROP_JOB (job_name => 'STATISTICS_COLUMNS_JOB');
END;
Somtimes the job was already dropped in the original schema, the dump comes without the job and the script fails:
有时作业已经被删除到原始模式中，转储没有作业并且脚本失败：
ERROR at line 1:
ORA-27475: "DMP_6633.STATISTICS_SET_COLUMNS_JOB" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 213
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 5
How can I avoid this failure in case the job does not exists but still be able to drop it if it is?
如果作业不存在但仍然能够删除它，我如何避免这种失败？
回答by Ben
There are two main patterns you can apply to exception handling; "look before you leap" (LBYL) and "it's easier to ask forgiveness than permission" (EAFP). LBYL would advocate checking to see if the job exists before attempting to drop it. EAFP would involve attempting to drop the job and then capturing and ignoring that specific error, if it occurs.
有两种主要模式可以应用于异常处理；“跳之前先看看”（LBYL）和“请求宽恕比许可更容易”（EAFP）。LBYL 提倡在尝试删除作业之前检查作业是否存在。EAFP 将涉及尝试删除作业，然后捕获并忽略该特定错误（如果发生）。
If you were to apply LBYL you can query the system view USER_SCHEDULER_JOBSto see if your job exists. If it does, drop it.
如果您要应用 LBYL，您可以查询系统视图USER_SCHEDULER_JOBS以查看您的工作是否存在。如果是，请放下它。
declare
l_job_exists number;
begin
select count(*) into l_job_exists
from user_scheduler_jobs
where job_name = 'STATISTICS_COLUMNS_JOB'
;
if l_job_exists = 1 then
dbms_scheduler.drop_job(job_name => 'STATISTICS_COLUMNS_JOB');
end if;
end;
For EAFP it's slightly different; define your own exceptionby naming an internally defined exceptionand instantiating it with the error code you're looking to catch. If that error is then raised, do nothing.
对于 EAFP，它略有不同；通过命名内部定义的异常并使用您要捕获的错误代码实例化它来定义您自己的异常。如果随后出现该错误，则什么也不做。
declare
job_doesnt_exist EXCEPTION;
PRAGMA EXCEPTION_INIT( job_doesnt_exist, -27475 );
begin
dbms_scheduler.drop_job(job_name => 'STATISTICS_COLUMNS_JOB');
exception when job_doesnt_exist then
null;
end;
It's worth noting two things about this second method.
关于第二种方法有两点值得注意。
I am onlycatching the error raised by this specific exception. It would be possible to achieve the same thing using
EXCEPTION WHEN OTHERSbut I would highly recommend againstdoing this.If you handle an exception you should know exactly what you're going to do with it. It's unlikely that you have the ability to handle every single Oracle exception properly using
OTHERSand if you do so you should probably be logging them somewhere where they'll be noticed. To quote from Oracle's Guidelines for Avoiding and Handling Exceptions:Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.
Oracle's exception propagationworks from internal block to external block so the original cause for the error will be the first exception.
我只捕捉到这个特定异常引发的错误。使用可以实现相同的目的，
EXCEPTION WHEN OTHERS但我强烈建议不要这样做。如果您处理异常，您应该确切地知道您将如何处理它。您不太可能有能力正确使用每个单独的 Oracle 异常
OTHERS，如果您这样做了，您可能应该将它们记录在它们会被注意到的地方。引用 Oracle 的《避免和处理异常指南》：只要有可能，为命名异常编写异常处理程序，而不是使用 OTHERS 异常处理程序。
Oracle 的异常传播从内部块到外部块工作，因此错误的原始原因将是第一个异常。
