亚洲免费不卡_在线视频精品_国产尤物精品_久久久久网址_久久精品91_欧美va天堂在线_狠狠入ady亚洲精品_亚洲午夜精品福利_国产精品草草_午夜精品久久99蜜桃的功能介绍

遍歷開始日期到結束日期的每一天，若有查詢某段日期下有什么業務或者事件發生時，可用到此函數。

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;

更多信息請查看IT技術專欄