Oracle按年，按月，按季度，按周，按天分组

function time_conv_pro(sdate varchar2,edate varchar2,ident varchar2) return varchar2
as
v_month    varchar2(3);
v_date     varchar2(30);
begin
  if ident = 1 then                  --按年
    insert into prpt_data_t(c0)
    select to_char(add_months(to_date(substr(sdate, 1, 4), 'yyyy'), (rownum - 1) * 12), 'yyyy')  as dates  from dual
    connect by rownum <= ceil(months_between(to_date(substr(edate, 1, 4), 'yyyy'), to_date(substr(sdate, 1, 4),
    'yyyy')) / 12 +1);
    return 'yyyy';
  elsif ident = 2 then               --按季度
    for r in (select to_char(add_months(to_date(substr(sdate, 1, 7), 'yyyy-mm'), rownum - 1), 'yyyy-mm') as dates from dual
    connect by rownum <= ceil(months_between(to_date(substr(edate, 1, 7), 'yyyy-mm'), to_date(substr(sdate, 1, 7),
    'yyyy-mm'))+1)) loop
      v_month := substr(r.dates,6,7);
      v_date := substr(r.dates,0,4);
      if v_month in('01','02','03') then
        v_date := v_date||'-'||'1';
      elsif v_month in('04','05','06') then
        v_date := v_date||'-'||'2';
      elsif v_month in('07','08','09') then
        v_date := v_date||'-'||'3';
      elsif v_month in('10','11','12') then
        v_date := v_date||'-'||'4';
      end if;
      merge into prpt_data_t a
      using (select v_date as c0 from dual) b
      on (a.c0 = b.c0)
      when not matched then
        insert(c0) values(v_date);
    end loop;
    return 'yyyy-q';
  elsif ident = 3 then               --按月
    insert into prpt_data_t(c0)
    select to_char(add_months(to_date(substr(sdate, 1, 7), 'yyyy-mm'), rownum - 1), 'yyyy-mm') as dates from dual
    connect by rownum <= ceil(months_between(to_date(substr(edate, 1, 7), 'yyyy-mm'), to_date(substr(sdate, 1, 7),
    'yyyy-MM'))+1);
    return 'yyyy-mm';
  elsif ident = 4 then               --按周
    insert into prpt_data_t(c0)
    select to_char(to_date(substr(sdate, 1, 10), 'yyyy-mm-dd') + (rownum - 1) * 7, 'iw') as dates from dual
    connect by rownum <=trunc(to_date(substr(edate, 1, 10), 'yyyy-mm-dd') - to_date(substr(sdate, 1, 10),
    'yyyy-mm-dd')) / 7 + 1;
    return 'iw';
  else                               --默认按天
    insert into prpt_data_t(c0)
    select to_char(to_date(substr(sdate, 1, 10), 'yyyy-mm-dd') + rownum - 1, 'yyyy-mm-dd') as datas from dual
    connect by rownum <= trunc(to_date(substr(edate, 1, 10), 'yyyy-mm-dd') - to_date(substr(sdate, 1, 10), 'yyyy-mm-dd')) + 1;
    return 'yyyy-MM-dd';
  end if;
end;