歡迎技術交流。 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;