Oracle数据库实现日期遍历功能代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】 Oracle数据库实现日期遍历功能

作者:齐建鹏 / 发布于2013/8/20/ 572

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


	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;

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　日期遍历　

同语言下

可能有用的

齐建鹏贡献的其它代码(3)