Oracle存储过程、触发器实现获取时间段内周、月、季度的具体时间

欢迎技术交流。 QQ：138986722

创建table：

create table tbmeetmgrinfo( id number primary key, /*主键，自动增加 */ huiyishi number, /*会议室编号 */ STARTTIME varchar2(30), /*会议开始时间 */ ENDTIME varchar2(30), /*会议结束时间 */ CREATETIME varchar2(30), /*会议创建日期 */ STOPTIME varchar2(30), /*会议起止日期*/ xunhuaimoshi number /*会议循环模式1为单周、3为每月、4为每季度 、0为一次性会议*/ ); --创建自动增长序列 create sequence tbmeetmgrinfo_tb_sequence minvalue 1 --最小值 maxvalue 9999999999999999999999999 --最大值 increment by 1 --增加量为1 start with 1 /* 从1开始 */ insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,2,'12:00','13:00','2011-05-10','2011-05-11',1) select * from tbmeetmgrinfo

 

创建触发器：

create or replace trigger biufer_tbmeetmgr_CHANGETIME before insert or update or delete of CHANGETIME on tbmeetmgr for each row begin -- 调用存储过程 hzwmeetmgr; end;

 

存储过程：

CREATE OR REPLACE PROCEDURE hzwmeetMgr is meetId number; --会议室编号 strSta varchar2(30); --会议开始时间 strEnd varchar2(30); --会议结束时间 strCreate varchar2(30); --会议创建日期 strOver varchar2(30); --会议终止日期(循环终止时期) strOverSql varchar2(30); --存入临时表中的终止日期 varInt number; --会议创建日期到终止日期相差天数 varSubTime varchar2(30); --循环日期 varMonths number; TYPE c_time IS REF CURSOR; --创建游离标记 vrec c_time; yearY varchar2(10); --日期年部分 monthsM varchar2(10);--日期月部分(终止入库时间) begin delete tbmeetmgrinfo where 1=1 ; --先清空表中数据 commit; varInt := 0; --一次性会议记录 for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi from tbmeetmgr where HUIYIMOSHI = 1 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCode.Stoptime; strCreate := substr(varCode.Createtime,1,10); meetId := varCode.Huiyishi; strSta := varCode.Starttime; strEnd := varCode.Endtime; varSubTime := substr(varCode.Xunhuairiqi,3); --最后的0代表的是一次性会议、循环模式1为单周、3为每月、4为每季度 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOver,0); commit; end loop; --单周循环 for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi,(( to_date(stoptime,'yyyy-mm-dd') - next_day (to_date(substr(createtime, 0, 10),'yyyy-mm-dd')-1,3))/7) resultNum from tbmeetmgr where xunhuaimoshi = 1 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop varInt := ceil(varCode.Resultnum); strOver := varCode.Stoptime; strCreate := substr(varCode.Createtime,1,10); meetId := varCode.Huiyishi; strSta := varCode.Starttime; strEnd := varCode.Endtime; varSubTime := substr(varCode.Xunhuairiqi,3); OPEN vrec for SELECT to_char(next_day(to_date(strCreate,'yyyy-mm-dd')-1,ceil(varSubTime)+1)+(rownum-1)*7 , 'yyyy-MM-dd') from dual connect by rownum<=varInt ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,1); commit; end loop; end loop; --每月循环 for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME, Xunhuairiqi,ceil(months_between(to_date(stoptime,'yyyy-mm-dd'), to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))) months from tbmeetmgr where xunhuaimoshi = 3 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCodeMonths.Stoptime; strCreate := substr(varCodeMonths.Createtime,1,10); meetId := varCodeMonths.Huiyishi; strSta := varCodeMonths.Starttime; strEnd := varCodeMonths.Endtime; varMonths := varCodeMonths.Months; varSubTime := substr(varCodeMonths.Xunhuairiqi,3,4); --取得开会时间(具体哪一日) yearY := substr(strCreate,1,8); monthsM := yearY||varSubTime ; OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varMonths) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,3); commit; end loop; end loop; --每季度循环 for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME, Xunhuairiqi,ceil((to_date(stoptime,'yyyy-mm-dd')-to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))/90) months from tbmeetmgr where xunhuaimoshi = 4 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCodeMonths.Stoptime; strCreate := substr(varCodeMonths.Createtime,1,10); meetId := varCodeMonths.Huiyishi; strSta := varCodeMonths.Starttime; strEnd := varCodeMonths.Endtime; varMonths := varCodeMonths.Months; varSubTime := substr(varCodeMonths.Xunhuairiqi,3,7); --取得开会时间(具体哪一日) yearY := substr(strCreate,1,5); monthsM := yearY||varSubTime ; OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varMonths) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('季度循环日期:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,4); commit; end loop; end loop; end;