Oracle数据库日期遍历功能的实现.pdfVIP

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