Oracle PLSQL 重复模式 RFC 2445 [英] Oracle PLSQL Recurrence Pattern RFC 2445

问题描述

我有一个必要条件，我需要使用 PLSQL 将 RFC 2445 循环模式转换为 Dates.

I have a requisite on which I need to convert a RFC 2445 Recurrence Pattern to Dates using PLSQL.

示例:

RRULE = FREQ=DAILY;INTERVAL=5;COUNT=10

根据该规则，我需要编写一个表格，其中包含该模式接下来出现的 10 次.类似于下面的图像，将开始日期视为 1/1/2019 12:00:00 AM:

From that rule, I need to write a table with the next 10 occurrences of that pattern. Something like the image bellow, considering start date as 1/1/2019 12:00:00 AM:

Oracle 是否提供任何允许我执行此操作的 PLSQL 包?如果没有，有人知道任何 PLSQL 项目计划吗?

Does Oracle provides any PLSQL Package that allows me to do this? If doesn't, does anybody knows any PLSQL project initiative for this?

Ps:这与 Oracle 在作业计划中使用的模式完全相同.

Ps: this is the same exactly pattern that Oracle uses on Job Schedules.

推荐答案

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING 可能能够做到这一点.

包支持的语法似乎与 RFC 2445 相似，但并不完全相同.下面的 PL/SQL 块根据日历字符串打印出日期.有一些复杂性，例如解析 COUNT=10 以确定重复计算的次数.

The syntax supported by the package seems similar to RFC 2445, but not identical. The below PL/SQL block prints out the dates based on a calendar string. There are some complications, such as parsing out the COUNT=10 to determine how many times to repeat the calculation.

declare
    --Test different calendar strings and start dates.
    --p_calendar_string varchar2(4000) := 'FREQ=DAILY;INTERVAL=5;';
    p_calendar_string varchar2(4000) := 'FREQ=DAILY;INTERVAL=5;COUNT=10';
    p_start_date date := timestamp '2019-01-01 00:00:00';

    v_next_run_date date;
    v_count number;
    --Find the COUNT and remove it rom the calendar string, if it exists.
    procedure get_and_remove_count(p_calendar_string in out varchar2, p_count out number) is
    begin
        if lower(p_calendar_string) like '%count%' then
            p_count := to_number(regexp_substr(p_calendar_string, 'COUNT=([0-9]+)', 1, 1, null, 1));
            p_calendar_string := regexp_replace(p_calendar_string, 'COUNT=[0-9]+;?');
        else
            p_count := 1;
        end if;
    end;
begin
    get_and_remove_count(p_calendar_string, v_count);

    --TEST
    --dbms_output.put_line('String: '||p_calendar_string||', count: '||v_count);

    --Start with the original date.
    v_next_run_date := p_start_date-1/24/60/60;

    --Loop through the COUNT and display all dates.
    for i in 1 .. v_count loop

        dbms_scheduler.evaluate_calendar_string
        (
            calendar_string   => p_calendar_string,
            start_date        => p_start_date,
            return_date_after => v_next_run_date,
            next_run_date     => v_next_run_date
        );

        dbms_output.put_line(to_char(v_next_run_date, 'mm/dd/yyyy hh:mi:ss am'));
    end loop;
end;
/

输出:

01/01/2019 12:00:00 am
01/06/2019 12:00:00 am
01/11/2019 12:00:00 am
01/16/2019 12:00:00 am
01/21/2019 12:00:00 am
01/26/2019 12:00:00 am
01/31/2019 12:00:00 am
02/05/2019 12:00:00 am
02/10/2019 12:00:00 am
02/15/2019 12:00:00 am

这篇关于Oracle PLSQL 重复模式 RFC 2445的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！