Date

将日期按年，月，日 拆分成字段，并把日期修改成产能时段！

create or replace package body MES_DATE
is
 function get_Split_Date
(
       timetype   in  date
)
 return int
 is
       V_fulldate       varchar2(20);
       v_Year           varchar2(5);
       v_Quarter        varchar2(5);
       v_Month          int;
       v_WeekNum        int;
       v_DayNum         int;
       v_WeekName       varchar2(20);
       v_Time           varchar2(10);
       v_prodWeekNum    int;
       v_prodDay        varchar2(20);
       timedate         date;
      
begin
       v_fulldate:=to_char(timetype,'yyyy-mm-dd hh24');
       select to_date(v_fulldate,'yyyy-mm-dd hh24')
       into timedate from dual;
      
       select to_char(timedate,'YYYY')  into v_Year from dual;
       select to_char(timedate,'Q')  into v_Quarter from dual;
       select to_number(to_char(timedate,'MM')) into v_Month from dual;
       select to_number(to_char(timedate,'WW')) into v_WeekNum from dual;
       select to_number(to_char(timedate,'DDD')) into v_DayNum from dual;
       select to_char(timedate,'HH24')||':00' into v_Time from dual;
       select to_char(timedate,'day','NLS_DATE_LANGUAGE  =  American') into v_WeekName   from dual;
       select to_number(to_char(timedate-4,'WW')) into v_prodWeekNum   from dual;
       select to_char(timedate-8/24,'yyyy-mm-dd') into v_prodDay   from dual;
 
       insert into DimTime(fulldatealternatekey,calendaryear,calendarquarter,
       monthnumberofyear,weeknumberofyear,daynumberofyear,weekday,hourOfDay,
       productweekofyear,productdayofyear) values(v_fulldate,v_Year,v_Quarter,
       v_Month,v_WeekNum,v_DayNum,v_WeekName,v_Time,
       v_prodWeekNum,v_prodDay);
      
        return 1;
 EXCEPTION
     WHEN OTHERS THEN
           return 0;
      
end get_Split_Date;

procedure InsertIntoDimTime
(
      p_startdate          in date,
       p_enddate           in date,
       p_result            OUT INT
)
is
       v_startdate date;
       v_enddate date;
begin

       select to_date(to_char(p_startdate,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')
       into v_startdate from dual;
       select to_date(to_char(p_enddate,'yyyy-mm-dd hh24'),'yyyy-mm-dd hh24')
       into v_enddate from dual;
      
        p_result:=1;
       while(v_startdate <= v_enddate)
       loop
       p_result:=get_Split_Date(v_startdate);
       if(p_result=1)then
       v_startdate:=v_startdate+1/24;
       else
           exit;
       end if;
       end loop;
       commit;
EXCEPTION
     WHEN OTHERS THEN
          p_result:=0;
          BEGIN
            ROLLBACK;
          END;
      
end InsertIntoDimTime;
 
end MES_DATE;