Решение задачи - Расписание от ИТ "Магнит"

Код решения

Суть примерно такая. Как сформировать красиво строку v_scheduler_calendar_str - дело десятое. Решение с гугла.

create or replace function next_run_date(pi_date_after date, pi_calendar_str varchar2) return date is

 v_by_minute varchar2(50);

 v_by_hour varchar2(50);

 v_by_week_day varchar2(50);

 v_by_month_day varchar2(50);

 v_by_month varchar2(50);

 v_scheduler_calendar_str varchar2(512);

 v_result date;

begin

 v_by_minute   := regexp_substr(pi_calendar_str, '[^;]+', 1, 1);

 v_by_hour     := regexp_substr(pi_calendar_str, '[^;]+', 1, 2);

 v_by_week_day := regexp_substr(pi_calendar_str, '[^;]+', 1, 3);

 v_by_month_day := regexp_substr(pi_calendar_str, '[^;]+', 1, 4);

 v_by_month    := regexp_substr(pi_calendar_str, '[^;]+', 1, 5);

 v_by_week_day := replace(v_by_week_day, 1, 'SUN');

 v_by_week_day := replace(v_by_week_day, 2, 'MON');

 v_by_week_day := replace(v_by_week_day, 3, 'TUE');

 v_by_week_day := replace(v_by_week_day, 4, 'WED');

 v_by_week_day := replace(v_by_week_day, 5, 'THU');

 v_by_week_day := replace(v_by_week_day, 6, 'FRI');

 v_by_week_day := replace(v_by_week_day, 7, 'SAT');

 v_scheduler_calendar_str := 'FREQ=YEARLY' ||

   ';BYMONTH=' || v_by_month ||

   ';BYMONTHDAY=' || v_by_month_day ||

   ';BYDAY=' || v_by_week_day ||

   ';BYHOUR=' || v_by_hour ||

   ';BYMINUTE=' || v_by_minute;

 dbms_scheduler.evaluate_calendar_string(

   calendar_string  => v_scheduler_calendar_str,

   start_date       => pi_date_after,

   return_date_after => pi_date_after,

   next_run_date    => v_result

 );

 return v_result;

end;

/

-- Тестирование

declare

 v_date date;

begin

 v_date := next_run_date(

   to_date('09.07.2010 23:36','dd.mm.YYYY hh24:mi'),

   '0,45;12;1,2,6;3,6,14,18,21,24,28;1,2,3,4,5,6,7,8,9,10,11,12;'

 );

 dbms_output.put_line(to_char(v_date, 'dd.mm.YYYY hh24:mi'));

end;

/