隨手筆記

SELECT TO_CHAR(add_months(add_months(sysdate, -12), ROWNUM - 1), 'MON-yy') AS yearmonth
  FROM DUAL
CONNECT BY ROWNUM <=
           (SELECT months_between(sysdate, add_months(sysdate, -12))
              FROM dual);
 

Procedure ChkEffectDateOverlap(P_ACTIVE_DATE      IN VARCHAR2,
                                                      P_INACTIVE_DATE  IN VARCHAR2,
                                                      P_OUT                     OUT VARCHAR2) IS
  
    v_count number;
  
  BEGIN
  
    select count(*)
       into v_count 
      from Table A
     where 1=1
       and to_date(P_ACTIVE_DATE, 'MM/dd/yyyy') <= A.Inactive_Date
       and to_date(P_INACTIVE_DATE, 'MM/dd/yyyy') >= A.active_Date;
  
    if v_count > 0 then
    
      select 'Effective Date Overlap' into P_OUT from dual;
    
    else
    
      select 'Success' into P_OUT from dual;
    
    end if;
  
  Exception
    WHEN OTHERS THEN
      select 'Error' into P_OUT from dual;
  END;