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