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