Oracle数据库实现日期遍历功能

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
 --第一天的日期转换
 select to_number(substr(firstDay,1,4))into firstYear from dual ;
 select to_number(substr(firstDay,6,2)) into firstMonth from dual;
 --第二天的日期转换
 select to_number(substr(lastDay,1,4)) into lastYear from dual;
 select to_number(substr(lastDay,6,2)) into lastMonth from dual;
--1 第一个日期早于第二个日期
 if to_number(to_date(firstDay,'yyyy-mm-dd')-to_date(lastDay,'yyyy-mm-dd')) <0 then
 dbms_output.put_line('第一个日期早于第二个日期！'); 
 end if;
--2 第一个日期晚于第二个日期 
--------相同年份
 if firstYear = lastYear then
 v_date.year := to_char(firstYear);
 --------相同月份
 if firstMonth = lastMonth then
 v_date.month := to_char(firstMonth); 
 -- 天数差
 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); 
 --满月天数差
 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;
 --最后一个月的遍历
 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
 ---------------------------------------------- 
 --第一个月
 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; 
 ---------------------------------------------------------------
 --中间所有月数的日期添加
 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
 --月数循环
 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));
 --天数循环
 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; 
 -----------------------------------------------------------------------------------
 --最后一个月 
 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;