如何在 dbms 作业 (oracle) 上设置 start_date 和 end_date
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29217415/
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
How to set start_date and end_date on a dbms job (oracle)
提问by darkpirate
I'm creating a scheduler for my oracle DB This is what i got so far :
我正在为我的 oracle 数据库创建一个调度程序 这是我到目前为止所得到的：
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'CREAZIONE_OCCORRENZE',
job_type => 'STORED_PROCEDURE',
job_action => 'pop_occr_lezione'
start_date => A,
end_date => B,
repeat_interval => 'FREQ=WEEKLY'
enabled => true,
auto_drop => false;
)
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ASSEGNAZIONE - AULE',
job_type => 'STORED_PROCEDURE',
job_action => 'ass_aule'
start_date => C,
end_date => D,
repeat_interval => 'FREQ=WEEKLY'
enabled => true,
auto_drop => false;
)
END;
/
As you see i still need to set start_date and end_date for the 2 jobs. Wich is rather confusing, how can i set :
如您所见，我仍然需要为 2 个作业设置 start_date 和 end_date。Wich相当混乱，我该如何设置：
A = the last monday of august ( valid for each year )
A = 八月的最后一个星期一（每年有效）
B = the first monday of august ( a year later of A )
B = 八月的第一个星期一（A 的一年后）
C = the first sunday after A
C = A 之后的第一个星期日
D = the first sunday after B
D = B 之后的第一个星期日
how can i do such a thing ?
我怎么能做这样的事情？
回答by Wernfried Domscheit
start_dateand end_dateare fixedvalues, i.e. you cannot say "the last Monday of August ( valid for each year)". start_dateis only used for the initial value of repeat_interval.
start_date并且end_date是固定值，即您不能说“八月的最后一个星期一（每年有效）”。start_date仅用于 的初始值repeat_interval。
For example start_date => TIMESTAMP '2015-03-26 18:00:00', repeat_interval => 'FREQ=WEEKLY'means every Monday at 18:00:00.
例如start_date => TIMESTAMP '2015-03-26 18:00:00', repeat_interval => 'FREQ=WEEKLY'表示每周一的 18:00:00。
end_dateis the date when your job becomes disabled.
end_date是您的工作被禁用的日期。
repeat_intervalfor "every last Monday of August" would be FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON
repeat_interval因为“八月的每个最后一个星期一”将是 FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON
repeat_intervalfor "every first Sunday of August" would be FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=1 SUN
repeat_interval因为“八月的第一个星期日”将是 FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=1 SUN
You can verify with this procedure:
您可以通过以下程序进行验证：
DECLARE
next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd fmDay') );
END LOOP;
next_run_date := NULL;
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=1 SUN', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd fmDay') );
END LOOP;
END;
2015-08-31 Monday
2016-08-29 Monday
2017-08-28 Monday
2018-08-27 Monday
2019-08-26 Monday
2020-08-31 Monday
2021-08-30 Monday
2022-08-29 Monday
2023-08-28 Monday
2024-08-26 Monday
2015-08-30 Sunday
2016-08-28 Sunday
2017-08-27 Sunday
2018-08-26 Sunday
2019-08-25 Sunday
2020-08-30 Sunday
2021-08-29 Sunday
2022-08-28 Sunday
2023-08-27 Sunday
2024-08-25 Sunday
Check Calendaring Syntaxfor further details
检查日历语法以获取更多详细信息
Based on this you can create another job which set the start_dateof main jobs, i.e.:
基于此，您可以创建另一个设置start_date主要工作的工作，即：
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SET_START_TIME',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE(''CREAZIONE_OCCORRENZE'', ''START_DATE'', LOCALTIMESTAMP); END;',
repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-1 MON'
enabled => TRUE,
auto_drop => FALSE);
END;
回答by Ditto
You can use this query to get the info ... then you just have to stuff the values into your dbms_scheduler. If you need to do this every year, you may need to use dynamic sql to call the scheduler.
您可以使用此查询来获取信息……然后您只需将值填入您的 dbms_scheduler。如果每年都需要这样做，可能需要使用动态sql调用调度器。
with w_current as (
select add_months(trunc(sysdate,'YYYY'),7+12) aug1,
last_day(add_months(trunc(sysdate,'YYYY'),7)) aug31
from dual
),
w_mondays as (
select aug1+mod(9-to_char(aug1,'D'),7) first_mon_aug,
aug31+mod(8-to_char(aug31,'D'),7)-6 last_mon_aug
from w_current
)
select first_mon_aug, last_mon_aug,
first_mon_aug+6 first_sun_after_A,
last_mon_aug+6 first_sun_after_B
from w_mondays;
回答by Wernfried Domscheit
I am confused, you are mixing Monday and Sunday (2015-07-12 is Monday, not Sunday) and you changed the requirements in your question.
我很困惑，您将周一和周日混在一起（2015-07-12 是周一，而不是周日），并且您更改了问题中的要求。
As far as I understand your schedule and in order to simplify, you like to run job CREAZIONE_OCCORRENZEevery Monday exceptfrom "2nd Monday in July till last but one Monday in August". Job ASSEGNAZIONE_AULEruns always five day after.
据我了解你的日程安排，为了简化，你喜欢CREAZIONE_OCCORRENZE每周一运行工作，除了“七月的第二个星期一到最后一个星期一，八月的一个星期一”。作业ASSEGNAZIONE_AULE总是在五天后运行。
Then you could work with exclusions. It should be this one:
然后你可以使用排除项。应该是这个：
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MONDAY_AUGUST',
repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=AUG;BYDAY=-5 MON,-4 MON,-3 MON,-2 MON');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MONDAY_JULY',
repeat_interval => 'FREQ=MONTHLY;INTERVAL=1;BYMONTH=JUL;BYDAY=2 MON,3 MON,4 MON,5 MON');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'MONDAYS',
repeat_interval => 'FREQ=WEEKLY;INTERVAL=1;BYDAY=MON;EXCLUDE=MONDAY_AUGUST,MONDAY_JULY');
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'SUNDAYS',
repeat_interval => 'MONDAYS+OFFSET:6D');
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'CREAZIONE_OCCORRENZE',
job_type => 'STORED_PROCEDURE',
job_action => 'pop_occr_lezione'
start_date => NULL,
end_date => NULL,
repeat_interval => 'MONDAYS'
enabled => true,
auto_drop => false);
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'ASSEGNAZIONE - AULE',
job_type => 'STORED_PROCEDURE',
job_action => 'ass_aule'
start_date => NULL,
end_date => NULL,
repeat_interval => 'SUNDAYS'
enabled => true,
auto_drop => false);
END;
/
And the PL/SQL Block to test the schedule:
和 PL/SQL 块来测试调度：
DECLARE
next_run_date TIMESTAMP WITH TIME ZONE;
BEGIN
next_run_date := NULL;
DBMS_OUTPUT.PUT_LINE ('Excluded Mondays in August' );
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAY_AUGUST', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
END LOOP;
next_run_date := NULL;
DBMS_OUTPUT.PUT_LINE ('Excluded Mondays in July' );
FOR i IN 1..10 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAY_JULY', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
END LOOP;
next_run_date := NULL;
DBMS_OUTPUT.PUT_LINE ('Executions Job 1' );
FOR i IN 1..150 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('MONDAYS', NULL, next_run_date, next_run_date);
IF TO_CHAR(next_run_date, 'IW') BETWEEN 25 AND 38 THEN -- avoid excessive output
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
END IF;
END LOOP;
next_run_date := NULL;
DBMS_OUTPUT.PUT_LINE ('Executions Job 2' );
FOR i IN 1..150 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('SUNDAYS', NULL, next_run_date, next_run_date);
IF TO_CHAR(next_run_date, 'IW') BETWEEN 25 AND 38 THEN -- avoid excessive output
DBMS_OUTPUT.PUT_LINE ( TO_CHAR(next_run_date, 'yyyy-mm-dd wTH fmDay') );
END IF;
END LOOP;
END;
Excluded Mondays in August
2015-08-03 1st Monday
2015-08-10 2nd Monday
2015-08-17 3rd Monday
2015-08-24 4th Monday
2016-08-01 1st Monday
2016-08-08 2nd Monday
2016-08-15 3rd Monday
2016-08-22 4th Monday
2017-08-07 1st Monday
2017-08-14 2nd Monday
Excluded Mondays in July
2015-07-13 2nd Monday
2015-07-20 3rd Monday
2015-07-27 4th Monday
2016-07-11 2nd Monday
2016-07-18 3rd Monday
2016-07-25 4th Monday
2017-07-10 2nd Monday
2017-07-17 3rd Monday
2017-07-24 4th Monday
2017-07-31 5th Monday
Executions Job 1
2015-06-15 3rd Monday
2015-06-22 4th Monday
2015-06-29 5th Monday
2015-07-06 1st Monday
2015-08-31 5th Monday
2015-09-07 1st Monday
2015-09-14 2nd Monday
2016-06-20 3rd Monday
2016-06-27 4th Monday
2016-07-04 1st Monday
2016-08-29 5th Monday
2016-09-05 1st Monday
2016-09-12 2nd Monday
2016-09-19 3rd Monday
2017-06-19 3rd Monday
2017-06-26 4th Monday
2017-07-03 1st Monday
2017-08-28 4th Monday
2017-09-04 1st Monday
2017-09-11 2nd Monday
2017-09-18 3rd Monday
2018-06-18 3rd Monday
2018-06-25 4th Monday
2018-07-02 1st Monday
Executions Job 2
2015-06-21 3rd Sunday
2015-06-28 4th Sunday
2015-07-05 1st Sunday
2015-07-12 2nd Sunday
2015-09-06 1st Sunday
2015-09-13 2nd Sunday
2015-09-20 3rd Sunday
2016-06-26 4th Sunday
2016-07-03 1st Sunday
2016-07-10 2nd Sunday
2016-09-04 1st Sunday
2016-09-11 2nd Sunday
2016-09-18 3rd Sunday
2016-09-25 4th Sunday
2017-06-25 4th Sunday
2017-07-02 1st Sunday
2017-07-09 2nd Sunday
2017-09-03 1st Sunday
2017-09-10 2nd Sunday
2017-09-17 3rd Sunday
2017-09-24 4th Sunday
2018-06-24 4th Sunday
2018-07-01 1st Sunday
2018-07-08 2nd Sunday
