Oracle dbms_job：作业只能在工作日的早上 6 点到晚上 8 点运行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5800875/
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
Oracle dbms_job: job have to run only weekday 6am-8pm
提问by The Bndr
i have an oracle 10g Database with different jobs. One of thoses jobs have to run every two hours. Therefore the "next_dat" and "interval" parameters looks like that.
我有一个有不同工作的 oracle 10g 数据库。其中一项工作必须每两个小时运行一次。因此“next_dat”和“interval”参数看起来像这样。
,next_date => to_date('27.04.2011 10:18:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'to_date(to_char(sysdate + (2/24), ''DD.MM.YYYY HH24:MI''), ''DD.MM.YYYY HH24:MI'')'
The jobs don't need to run at night. So the question is: is it possible to design for example "interval" in that way, that the jobs only runs between 6am and 8pm (in hh24 06:00 until 20:00).
这些作业不需要在晚上运行。所以问题是：是否可以以这种方式设计例如“间隔”，作业仅在早上 6 点到晚上 8 点之间运行（在 hh24 06:00 到 20:00）。
My first idea (and last choice) is an addition job which sets the main-job to "broken" between 8pm an 6 am. But i don't like the idea of an different job.
我的第一个想法（也是最后一个选择）是一个额外的工作，它将主要工作设置为在晚上 8 点到早上 6 点之间“中断”。但我不喜欢换一份工作的想法。
Thank you!
谢谢！
回答by Nick Pierpoint
You could use a case statement in the interval to check for the time of day then skip:
您可以在间隔中使用 case 语句来检查一天中的时间，然后跳过：
case
when to_char(trunc(sysdate, 'HH24') + 2/24, 'HH24') between '06' and '20' then
trunc(sysdate, 'HH24') + 2/24
else
trunc(sysdate)+1+(6/24)
end
This can be expanded to include a check on weekday. (You need to be careful on checking for the day of week as it is dependent on the NLS settings.)
这可以扩展为包括工作日的支票。（您需要小心检查星期几，因为它取决于 NLS 设置。）
Something like the following would handle the weekday, but you'll have to confirm the edge cases and check your NLS settings (for me, Monday is day 1 and Sunday is day 7):
类似以下内容将处理工作日，但您必须确认边缘情况并检查您的 NLS 设置（对我来说，星期一是第 1 天，星期日是第 7 天）：
case
when
to_number(to_char(sysdate, 'D')) between 1 and 5 then
case
when to_char(trunc(sysdate, 'HH24') + 2/24, 'HH24') between '06' and '20'
then
trunc(sysdate, 'HH24') + 2/24
else
trunc(sysdate)+1+(6/24)
end
else
trunc(sysdate) + (8 - to_number(to_char(sysdate, 'D'))) + 6/24
end
Although... think we are now firmly in the "time to right a function" stage. :)
虽然......认为我们现在正处于“正确功能的时间”阶段。:)
回答by cagcowboy
Make the interval a function, then you can have whatever control you like.
使间隔成为一个函数，然后您就可以拥有任何您喜欢的控件。
See here for an example:
请参见此处的示例：
