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

遍历开始日期到结束日期的每一天，若有查询某段日期下有什么业务或者事件发生时，可用到此函数。 Oracle SQL Developer create or replace type class_date as object( year varchar2(10), month varchar2(10), day varchar2(20))--定义所需要的日期类-------

遍历开始日期到结束日期的每一天，若有查询某段日期下有什么业务或者事件发生时，可用到此函数。 Oracle SQL Developer

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                        
                          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