oracle 自然周跨年度 一周分成两部分

CREATE OR REPLACE FUNCTION UDF_WEEKOFYEAR  ( V_DATE    IN    date ,formate   IN   VARCHAR2 )  RETURN char
AS
v_ret char(6);
v_ret1 char(2);
v_ret3 NUMBER;
BEGIN
 
 v_ret3 := TO_CHAR (v_date, 'iw');  
 IF     v_ret3 = 1 AND TO_CHAR (v_date, 'mm') = '12' then --判断是否是最后一个月 周次算成下年了第一周

  SELECT  TO_CHAR(DECODE(SIGN((V_DATE
                            +TO_NUMBER(DECODE(TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'),'1','8',
                            TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'))) - 2)
                             -LAST_DAY(V_DATE)),1,LAST_DAY(V_DATE),
                      (V_DATE
                      +TO_NUMBER(DECODE(TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'), '1','8',
                      TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'))) - 2)),'WW') as week
  into v_ret1 FROM DUAL;
  return to_char(v_date,'yyyy')||v_ret1;
 end if;
    
  IF     v_ret3 = 53 AND TO_CHAR (v_date, 'mm') = '01'  then--判断是否是第一个月 周次算成去年了最后一周 那么拆分成两个周
 
  SELECT  TO_CHAR(DECODE(SIGN((V_DATE
                            +TO_NUMBER(DECODE(TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'),'1','8',
                            TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'))) - 2)
                             -LAST_DAY(V_DATE)),1,LAST_DAY(V_DATE),
                      (V_DATE
                      +TO_NUMBER(DECODE(TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'), '1','8',
                      TO_CHAR(TRUNC(V_DATE,'YYYY'),'D'))) - 2)),'WW') as week
  into v_ret1 FROM DUAL;
     return to_char(v_date,'yyyy')||v_ret1; 
  end if;
 
 if to_char(trunc(v_date,'yyyy'),'iw') = 53 and TO_CHAR (v_date, 'mm') = '01'  then --判断是否是第一个月 周次算成去年了最后一周
      v_ret := TO_CHAR (v_date, formate) + 1;                                      --以后的周次向后推一
         RETURN v_ret;
 end if;
        
  v_ret := TO_CHAR (v_date, formate);  --正常情况下按自然周处理
  RETURN v_ret;              

END;