- 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;
- --该片段来自于http://www.codesnippet.cn/detail/190820135202.html
来源: http://www.codesnippet.cn/detail/190820135202.html