汕头模板建站软件,什么网站可以做软件有哪些,辽宁建设工程信息网、,wordpress 只允许遍历开始日期到结束日期的每一天#xff0c;若有查询某段日期下有什么业务或者事件发生时#xff0c;可用到此函数。 Oracle SQL Developer create or replace type class_date as object( year varchar2(10), month varchar2(10), day varchar2(20))--定义所需要的日期类---…遍历开始日期到结束日期的每一天若有查询某段日期下有什么业务或者事件发生时可用到此函数。 Oracle SQL Developer create or replace type class_date as object( year varchar2(10), month varchar2(10), day varchar2(20))--定义所需要的日期类-------遍历开始日期到结束日期的每一天若有查询某段日期下有什么业务或者事件发生时可用到此函数。Oracle SQL Developercreate 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 pipelinedasfirstYear 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 thendbms_output.put_line(第一个日期早于第二个日期);end if;--2 第一个日期晚于第二个日期--------相同年份if firstYear lastYear thenv_date.year : to_char(firstYear);--------相同月份if firstMonth lastMonth thenv_date.month : to_char(firstMonth);-- 天数差totalDay : to_number(to_date(lastDay,yyyy-mm-dd)-to_date(firstDay,yyyy-mm-dd));if totalDay 0 thenv_date.day : firstDay;pipe row(class_date(v_date.year,v_date.month,v_date.day));elsif totalDay 0 thenfor dayId in to_number(substr(firstDay,9,2))..to_number(substr(lastDay,9,2)) loopv_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 loopv_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 loopv_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 loopv_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 loopv_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 loopv_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 loopv_date.day : to_char(dayId);pipe row(class_date(v_date.year,v_date.month,v_date.day));end loop;end if;end minusDay;本文原创发布php中文网转载请注明出处感谢您的尊重
