Oracle计算统计周函数

CREATE OR REPLACE FUNCTION GET_STAT_WEEK(
    DATE_IN IN DATE)
    RETURN INT
IS
    REAL_YEAR INT;
    STAT_WEEK INT;
    DAYS INT;
    WEEKS INT;
    FIRST_DAY DATE;
    NEXT_WEDNESDAY DATE;
BEGIN
    IF DATE_IN IS NULL THEN
        RETURN NULL;
    END IF;

    REAL_YEAR := TO_NUMBER(TO_CHAR(DATE_IN,'YYYY'));

    -- 一年的起始日期为去年的12月26日
    IF TO_CHAR(DATE_IN,'MMDD') < '1226' THEN
        FIRST_DAY := TO_DATE(TO_CHAR(REAL_YEAR-1)||'1226', 'YYMMDD');
    ELSE
        FIRST_DAY := TO_DATE(TO_CHAR(REAL_YEAR)||'1226', 'YYMMDD');
    END IF;

    NEXT_WEDNESDAY := FIRST_DAY;
    WHILE TO_CHAR(NEXT_WEDNESDAY,'D') <> '4' LOOP
        NEXT_WEDNESDAY := NEXT_WEDNESDAY + 1;
        --DBMS_OUTPUT.PUT_LINE(TO_CHAR(NEXT_WEDNESDAY,'YYYYMMDD'));
    END LOOP;

    -- ??????????
    IF TRUNC(DATE_IN) <= NEXT_WEDNESDAY THEN
        STAT_WEEK := 1;
    ELSE
        STAT_WEEK := 1;
        DAYS := TRUNC(DATE_IN) - NEXT_WEDNESDAY - 1;
        WEEKS := FLOOR(DAYS / 7);
        STAT_WEEK := STAT_WEEK + WEEKS + 1;
    END IF;
    RETURN STAT_WEEK;
END;