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