最先答对且答案未经编辑的puber将获得纪念章一枚(答案不可编辑但可发新贴补充或纠正),其他会员如果提供有价值的分析、讨论也可获得纪念章一枚。
以往旧题索引:
http://www.itpub.net/forum.php?m ... eid&typeid=1808
原始出处:
http://www.plsqlchallenge.com/
作者: Kim Berg Hansen
运行环境:SQLPLUS, SERVEROUTPUT已打开
注:本题给出答案时候要求给予简要说明才能得到奖品
我想利用DBMS_SCHEDULER包的日历调度语法来调度我的作业。为了测试我写的日历语法是否正确,我写了一个小小的帮助函数。我还创建了一些作业。(注意:用户已经被授予CREATE JOB权限,才能创建这些作业)
create type plch_date_table_type as table of date
/
create or replace function plch_repeat_interval_test(
p_repeat_interval in varchar2
, p_start_date in date default sysdate
, p_num_repeats in integer default 20
)
return plch_date_table_type pipelined
is
l_date_after date;
l_next_date date;
begin
l_date_after := p_start_date;
for l_repeat in 1..p_num_repeats loop
dbms_scheduler.evaluate_calendar_string(
calendar_string => p_repeat_interval
, start_date => p_start_date
, return_date_after => l_date_after
, next_run_date => l_next_date
);
pipe row (l_next_date);
l_date_after := l_next_date;
end loop;
end plch_repeat_interval_test;
/
begin
dbms_scheduler.create_schedule(
schedule_name => 'MondayAt05'
, repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=5'
);
dbms_scheduler.create_schedule(
schedule_name => 'FridayAt22'
, repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=22'
);
dbms_scheduler.create_schedule(
schedule_name => 'MonAt05FriAt22'
, repeat_interval => 'MondayAt05,FridayAt22'
);
end;
/
帮助函数允许我能够测试repeat_interval表达式中使用的日历语法。例如:
select to_char(
column_value
, 'YYYY-MM-DD HH24:MI:SS Dy'
) as next_date
from table(plch_repeat_interval_test(
'FREQ=WEEKLY'
, DATE '2015-01-01'
, 7
))
/
NEXT_DATE
-----------------------
2015-01-08 00:00:00 Thu
2015-01-15 00:00:00 Thu
2015-01-22 00:00:00 Thu
2015-01-29 00:00:00 Thu
2015-02-05 00:00:00 Thu
2015-02-12 00:00:00 Thu
2015-02-19 00:00:00 Thu
现在我需要创建一个repeat_interval表达式,这个作业每周需要执行两次——星期一早晨5点 (05:00)和星期五晚上10点(22:00).
我有个未完成的查询,用来测试我的repeat_interval表达式:
select to_char(
column_value
, 'YYYY-MM-DD HH24:MI:SS Dy'
) as next_date
from table(
plch_repeat_interval_test(
##REPLACE##
, DATE '2015-01-01'
, 14
))
/
哪些选项包含了一个repeat_interval表达式,可用来取代 ##REPLACE## 使得查询返回这样的输出结果:
NEXT_DATE
-----------------------
2015-01-02 22:00:00 Fri
2015-01-05 05:00:00 Mon
2015-01-09 22:00:00 Fri
2015-01-12 05:00:00 Mon
2015-01-16 22:00:00 Fri
2015-01-19 05:00:00 Mon
2015-01-23 22:00:00 Fri
2015-01-26 05:00:00 Mon
2015-01-30 22:00:00 Fri
2015-02-02 05:00:00 Mon
2015-02-06 22:00:00 Fri
2015-02-09 05:00:00 Mon
2015-02-13 22:00:00 Fri
2015-02-16 05:00:00 Mon
(A)
'FREQ=WEEKLY; BYDAY=1,5; BYHOUR=5,22'
(B)
'FREQ=WEEKLY; BYDAY=MON,FRI; BYHOUR=5,22'
(C)
'FREQ=WEEKLY; BYDAY=MON,FRI; BYHOUR=5,22; BYSETPOS=1,4'
(D)
'FREQ=WEEKLY; INCLUDE=MondayAt05,FridayAt22'
(E)
'MondayAt05,FridayAt22'
(F)
'MonAt05FriAt22'