ORACLE函数记录，自定义日历周获取周数

函数：自定义日历周获取周数
功能说明：计算某日期是一年的第几周，支持自定义日历周，将任意周内星期设为一周起始日。
参数说明：currentDate为目标日期，weekStandard为自定义一周开端。注：可将任意周日、周一、周二...设为一周开头，参数取值范围为1~7，周一用1表示，周二用2表示，以此类推。
具体sql如下：

CREATE OR REPLACE function GetWeekNum(currentDate IN date,weekStandard IN varchar2) return varchar2
as
    firstDayWeekNo NUMBER; --本年1号周几（以日历为基础，周一为第一天）
    fisrtWeekDayCount NUMBER; --本年第一周天数(以weekNo为标准)
    dayCount NUMBER; --总天数
    weekCount NUMBER; --总周数

begin
    if currentDate is null or weekStandard is null or weekStandard < 1 or weekStandard > 7
    then
        return null;
    end if;
    
    firstDayWeekNo := to_char(trunc(currentDate,'y'),'d','NLS_DATE_LANGUAGE=AMERICAN')-1;
    if firstDayWeekNo = 0
    then
        firstDayWeekNo := 7;
    end if;

    if weekStandard = firstDayWeekNo
    then
        if(currentDate = trunc(currentDate,'y'))
        then
            fisrtWeekDayCount := 1;
        else
            fisrtWeekDayCount := 7;
        end if;
    elsif weekStandard < firstDayWeekNo
    then
        fisrtWeekDayCount := 7-(firstDayWeekNo-weekStandard);
    elsif weekStandard > firstDayWeekNo
    then
        fisrtWeekDayCount :=  (weekStandard - firstDayWeekNo);
    end if;

    dayCount := to_char(currentDate,'ddd');
    weekCount :=  ceil((dayCount-fisrtWeekDayCount)/7)+1;

    return to_char(currentDate,'YYYY')||to_char(weekCount,'FM00');
end;