https://t.me/oracle_dbd/388 (тестовое Магнита)

assert.sql

create or replace procedure assert(
    p_condition boolean,
    p_error_message varchar2 := 'Assert violation!',
    p_error_code int := -20100
) as
begin
    if p_condition then
        return;
    end if;

    raise_application_error(p_error_code, p_error_message, true);
end assert;
/

next_date.sql

create or replace function next_date(
    p_start_date date,
    p_schedule varchar2
) return date
as
    v_next_date date;

    -- parsed schedule values
    -- should be a number array but I'll rely on implicit conversion for now
    subtype t_parsed_schedule is sys.odcivarchar2list;

    v_schedule_rows t_parsed_schedule;

    v_minutes  t_parsed_schedule;
    v_hours    t_parsed_schedule;
    v_weekdays t_parsed_schedule;
    v_days     t_parsed_schedule;
    v_months   t_parsed_schedule;

    -- pointers to respective schedule elements
    subtype t_schedule_pointer is int;

    v_current_minute  t_schedule_pointer := 1;
    v_current_hour    t_schedule_pointer := 1;
    v_current_weekday t_schedule_pointer := 1;
    v_current_day     t_schedule_pointer := 1;
    v_current_month   t_schedule_pointer := 1;

    -- 0 means the year of p_start_date,
    -- 1 means next year, etc.
    v_current_year int := 0;

    -- store weekdays in a hash table to speed up lookups
    type it_possible_weekdays is
        table of boolean
        index by pls_integer;

    v_possible_weekdays it_possible_weekdays;

    -- in a real production code this would be a proper one-pass parser
    -- here, this one will do.
    function parse_schedule(sched varchar2, chunk int) return t_parsed_schedule as
        res t_parsed_schedule;
    begin
        select to_number(column_value) bulk collect into res
        from xmltable(regexp_substr(sched, '[^;]+', 1, chunk));
        return res;
    end parse_schedule;

begin
    if p_start_date is null or p_schedule is null then
        return null;
    end if;

    v_minutes  := parse_schedule(p_schedule, 1);
    v_hours    := parse_schedule(p_schedule, 2);
    v_weekdays := parse_schedule(p_schedule, 3);
    v_days     := parse_schedule(p_schedule, 4);
    v_months   := parse_schedule(p_schedule, 5);

    for i in 1 .. v_weekdays.count loop
        v_possible_weekdays(v_weekdays(i)) := true;
    end loop;

    <<main_loop>>
    loop
        -- calculate a candidate day for a current set of values
        v_next_date := trunc(p_start_date, 'yyyy')
            + numtoyminterval(v_current_year, 'year')
            + numtoyminterval(v_months(v_current_month) - 1, 'month')
            + numtodsinterval(v_days(v_current_day) - 1, 'day')
            + numtodsinterval(v_hours(v_current_hour), 'hour')
            + numtodsinterval(v_minutes(v_current_minute), 'minute')
        ;

        exit main_loop when 1 = 1
            and v_next_date > p_start_date

            -- feb 1 + 29 doesn't mean we're looking for mar. 1
            and extract(day from v_next_date) = v_days(v_current_day)

            -- nls_territory dependent. will fix later.
            and v_possible_weekdays.exists(to_char(v_next_date, 'd'))
        ;

        v_current_minute := v_current_minute + 1;

        if v_current_minute > v_minutes.count then
            v_current_minute := 1;
            v_current_hour := v_current_hour + 1;
        end if;

        if v_current_hour > v_hours.count then
            v_current_hour := 1;
            v_current_day := v_current_day + 1;
        end if;

        if v_current_day > v_days.count then
            v_current_day := 1;
            v_current_month := v_current_month + 1;
        end if;

        if v_current_month > v_months.count then
            v_current_month := 1;
            v_current_year := v_current_year + 1;
        end if;

    end loop main_loop;

    return v_next_date;

end next_date;
/

setup.sql

-- https://t.me/oracle_dbd/388
cl scr 

set echo off
set lines 200 
set serverout on 
set time off
set timi off

col "LINE/COL" for a10
col "ERROR" for a100

alter session set plsql_optimize_level = 3;

@@assert.sql
@@next_date.sql

sho err procedure assert 
sho err function next_date

set echo on 
set timi on 

exec assert(to_date('2036.02.29 12:00','yyyy.mm.dd hh24:mi') = next_date(to_date('2010.12.28 23:36','yyyy.mm.dd hh24:mi'), '0,45;12;6;29;2'))
exec assert(to_date('2012.02.29 12:00','yyyy.mm.dd hh24:mi') = next_date(to_date('2010.12.28 23:36','yyyy.mm.dd hh24:mi'), '0,45;12;1,2,3,4,5,6,7;29;2'))
exec assert(to_date('2010.07.18 12:00','yyyy.mm.dd hh24:mi') = next_date(to_date('2010.07.09 23:36','yyyy.mm.dd 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'))
exec assert(to_date('2011.01.03 12:00','yyyy.mm.dd hh24:mi') = next_date(to_date('2010.12.28 23:36','yyyy.mm.dd 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'))