| 标题 | Oracle数据库日期遍历功能的实现 |
| 范文 | 遍历开始日期到结束日期的每一天，若有查询某段日期下有什么业务或者事件发生时，可用到此函数。 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; |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。