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

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

1.[代码][SQL]代码

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;