jesusjsc

Oracle 加工作日(非正常天数)函数
# Oracle 加工作日(非正常天数)函数 ``` CREATE FUNCTION F_ADD_WO...
扫描右侧二维码阅读全文
02
2019/05

Oracle 加工作日(非正常天数)函数

Oracle 加工作日(非正常天数)函数

CREATE FUNCTION F_ADD_WORKDAY(D_START IN DATE, N_WORKDAY IN NUMBER, EXT_WORKDAY IN VARCHAR2 DEFAULT '1') RETURN T_TAB_DATE is
    v_start DATE := TRUNC(D_START);
    v_workday NUMBER := N_WORKDAY;
    v_is_workdate NUMBER;

    v_return T_TAB_DATE;

    /*
    D_start : 开始时间
    N_WORKDAY: 工作日的天数
    EXT_WORKDAY: 扩展的工作日,如果为'1'则表示广义的工作日,即将工作日前的连续的非工作都看做工作日
    RETURN:   返回的工作日
     */
BEGIN
    WHILE v_workday > 0
    LOOP
        v_start := v_start + 1;

        SELECT IS_WORKDATE INTO v_is_workdate FROM O_DIM_WORK_DATE WHERE CALENDAR_DATE = v_start;
        IF v_is_workdate = 1 THEN
            v_workday := v_workday - 1;
        END IF;
    END LOOP;

    IF EXT_WORKDAY = '1' THEN
        SELECT
            CALENDAR_DATE
            BULK COLLECT INTO v_return
        FROM O_DIM_WORK_DATE T
        WHERE NEXT_SEQ_NUM IN (
            SELECT NEXT_SEQ_NUM FROM O_DIM_WORK_DATE T1 WHERE T1.CALENDAR_DATE = v_start
        );
    RETURN v_return;
    ELSE
        SELECT v_start
            BULK COLLECT INTO v_return
        FROM dual;
        RETURN v_return;
    END IF;
END;
Last modification:May 27th, 2019 at 06:18 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment