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;