oracle 提取时间间隔时间,比较棘手的取间隔时间问题

最初由 atgc 发布

[B][PHP]

create table test2

(

beg_date varchar2(10),

end_date varchar2(10)

)

;

create or replace procedure test

(

date_beg      in varchar2,

date_end      in varchar2,

duration_type in varchar2

)

as

beg_date      date;

end_date      date;

beg_month     date;

end_month     date;

begin

if date_beg>date_end then

dbms_output.put_line('beginning date must be earlier than end date');

else

beg_date := to_date(date_beg,'yyyy-mm-dd');

end_date := to_date(date_end,'yyyy-mm-dd');

if duration_type='week' then

while beg_date+6<=end_date loop

if to_char(beg_date,'D')='2' then

while beg_date+6<=end_date loop

insert into test2 values(to_char(beg_date,'yyyy-mm-dd'),to_char(beg_date+6,'yyyy-mm-dd'));

beg_date := beg_date+7;

end loop;

end if;

beg_date := beg_date+1;

end loop;

else

if to_char(beg_date,'dd')='01' then

beg_month := beg_date;

else

beg_month := to_date(to_char(add_months(beg_date,1),'yyyy-mm')||'-01','yyyy-mm-dd');

end if;

if to_char(end_date,'yyyy-mm-dd')=to_char(last_day(end_date),'yyyy-mm-dd') then

end_month := end_date;

else

end_month := to_date(to_char(last_day(add_months(end_date,-1)),'yyyy-mm-dd'),'yyyy-mm-dd');

end if;

if duration_type='month' then

while to_char(beg_month,'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(beg_month),'yyyy-mm-dd'));

beg_month := add_months(beg_month,1);

end loop;

elsif duration_type='quarter' then

while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

if to_char(beg_month,'mmdd') in ('0101','0401','0701','1001') then

while to_char(add_months(beg_month,2),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,2)),'yyyy-mm-dd'));

beg_month := add_months(beg_month,3);

end loop;

end if;

beg_month := add_months(beg_month,1);

end loop;

elsif duration_type='semiyear' then

while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

if to_char(beg_month,'mmdd') in ('0101','0701') then

while to_char(add_months(beg_month,5),'yyyy-mm')<=to_char(end_month,'yyyy-mm') loop

insert into test2 values (to_char(beg_month,'yyyy-mm-dd'),to_char(last_day(add_months(beg_month,5)),'yyyy-mm-dd'));

beg_month := add_months(beg_month,6);

end loop;

end if;

beg_month := add_months(beg_month,1);

end loop;

end if;

end if;

end if;

commit;

end;

/

输入参数必须是如下格式

exec test('2007-08-01','2007-08-31','week');

exec test('2007-08-01','2007-10-01','month');

exec test('2007-06-01','2007-12-31','quarter');

exec test('2007-06-01','2009-11-30','semiyear');

SQL> select * from test2;

no rows selected

SQL> exec test('2007-08-01','2007-08-31','week');

PL/SQL procedure successfully completed.

SQL> select * from test2;

BEG_DATE   END_DATE

---------- ----------

2007-08-06 2007-08-12

2007-08-13 2007-08-19

2007-08-20 2007-08-26

SQL>

.

[/PHP] [/B]