遍歷開始日期到結(jié)束日期的每一天，若有查詢某段日期下有什么業(yè)務(wù)或者事件發(fā)生時(shí)，可用到此函數(shù)。

create or replace type class_date as object

(

year varchar2(10),

month varchar2(10),

day varchar2(20)

)--定義所需要的日期類

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

create or replace type table_date is table of class_date--日期類返回table類型

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

create or replace function minusDay(firstDay in varchar2,lastDay in varchar2)

return table_date pipelined

as

firstYear number;

firstMonth number;

lastYear number;

lastMonth number;

totalDay number;

totalMonth number;

currentDay varchar2(40);

currentYear varchar2(40);

type tt is record(

day varchar2(20),

month varchar2(20),

year varchar2(20)

);

v_date tt;

begin

--第一天的日期轉(zhuǎn)換

select to_number(substr(firstDay,1,4))into firstYear from dual ;

select to_number(substr(firstDay,6,2)) into firstMonth from dual;

--第二天的日期轉(zhuǎn)換

select to_number(substr(lastDay,1,4)) into lastYear from dual;

select to_number(substr(lastDay,6,2)) into lastMonth from dual;

--1 第一個(gè)日期早于第二個(gè)日期

if to_number(to_date(firstDay,'yyyy-mm-dd')-to_date(lastDay,'yyyy-mm-dd')) <0 then

dbms_output.put_line('第一個(gè)日期早于第二個(gè)日期！');

end if;

--2 第一個(gè)日期晚于第二個(gè)日期

--------相同年份

if firstYear = lastYear then

v_date.year := to_char(firstYear);

--------相同月份

if firstMonth = lastMonth then

v_date.month := to_char(firstMonth);

-- 天數(shù)差

totalDay := to_number(to_date(lastDay,'yyyy-mm-dd')-to_date(firstDay,'yyyy-mm-dd'));

if totalDay = 0 then

v_date.day := firstDay;

pipe row(class_date(v_date.year,v_date.month,v_date.day));

elsif totalDay >0 then

for dayId in to_number(substr(firstDay,9,2))..to_number(substr(lastDay,9,2)) loop

v_date.day :=to_char(substr(firstDay,1,7)||'-'||to_char(dayId)) ;

pipe row(class_date(v_date.year,v_date.month,v_date.day));

dbms_output.put_line( v_date.day);

end loop;

end if;

--------不同月份

elsif firstMonth < lastMonth then

---月份差

--totalMonth := lastMonth - firstMonth;

for id in firstMonth..lastMonth-1 loop

v_date.month := to_char(id);

--滿月天數(shù)差

totalDay := to_number(last_day(to_date(firstYear||'-'||to_char(id)||'-'||'01','yyyy-mm-dd'))-to_date(firstYear||'-'||to_char(id)||'-'||'01','yyyy-mm-dd'))+1;

for dayId in 1..totalDay loop

v_date.day := substr(firstDay,1,7)||'-'||to_char(dayId) ;

pipe row(class_date(v_date.year,v_date.month,v_date.day));

dbms_output.put_line( v_date.day);

end loop;

end loop;

--最后一個(gè)月的遍歷

v_date.month := to_char(lastMonth);

-- totalDay :=to_date(lastMonth,'yyyy-mm-dd') -to_date(to_char(lastYear)||'-'||to_char(lastMonth)||'-01','yyyy-mm-dd');

totalDay := to_number(to_date(lastDay,'yyyy-mm-dd')-to_date(substr(lastDay,1,7)||'-01','yyyy-mm-dd'))+1;

for id in 1..totalDay loop

v_date.day := substr(lastDay,1,7)||'-'||to_char(id);

pipe row(class_date(v_date.year,v_date.month,v_date.day));

dbms_output.put_line( v_date.day);

end loop;

end if;

--------不同年份

elsif firstYear < lastYear then

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

--第一個(gè)月

v_date.year := to_char(firstYear);

v_date.month := substr(firstDay,6,2);

totalDay :=to_number(substr( to_char(last_day(to_date(firstDay,'yyyy-mm-dd')),'yyyy-mm-dd'),9,2));

for dayId in to_number(substr(firstDay,9,2)) ..totalDay loop

v_date.day := to_char(dayId);

pipe row(class_date(v_date.year,v_date.month,v_date.day));

end loop;

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

--中間所有月數(shù)的日期添加

totalMonth := to_number( months_between(to_date(lastDay,'yyyy-mm-dd'),to_date(firstDay,'yyyy-mm-dd')))-1;

currentDay := firstDay;

currentDay := substr(currentDay,1,8)||'01';

for monthId in 1..totalMonth loop

--月數(shù)循環(huán)

currentDay:=

to_char(add_months(to_date(currentDay,'yyyy-mm-dd'),1),'yyyy-mm-dd') ;

currentYear := substr(currentDay,1,4);

v_date.year := to_char(substr(trim(currentDay),1,4));

v_date.month := to_char(substr(trim(currentDay),6,2));

--天數(shù)循環(huán)

totalDay := to_number(last_day(to_date(currentDay,'yyyy-mm-dd'))-to_date(currentYear||'-'||substr(currentDay,6,2)||'-01','yyyy-mm-dd'))+1;

for dayId in 1 .. totalDay loop

v_date.day := to_char(dayId);

pipe row(class_date(v_date.year,v_date.month,v_date.day));

dbms_output.put_line( v_date.day);

end loop;

end loop;

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

--最后一個(gè)月

totalDay := to_number(substr(lastDay,9,2));

v_date.month := to_number(substr(lastDay,6,2));

for dayId in 1 .. totalDay loop

v_date.day := to_char(dayId);

pipe row(class_date(v_date.year,v_date.month,v_date.day));

end loop;

end if;

end minusDay;

更多信息請(qǐng)查看IT技術(shù)專欄