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


创建table：

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

创建触发器：

  1. create or replace trigger biufer_tbmeetmgr_CHANGETIME   
  2.  before insert or update or delete   
  3.   of CHANGETIME   
  4.   on tbmeetmgr   
  5.  for each row   
  6. begin   
  7.   -- 调用存储过程   
  8.   hzwmeetmgr;   
  9. end;  

存储过程：

  1. CREATE OR REPLACE PROCEDURE hzwmeetMgr is   
  2.     meetId number; --会议室编号   
  3.     strSta varchar2(30); --会议开始时间   
  4.     strEnd varchar2(30); --会议结束时间   
  5.     strCreate varchar2(30); --会议创建日期   
  6.     strOver varchar2(30); --会议终止日期(循环终止时期)   
  7.     strOverSql varchar2(30); --存入临时表中的终止日期   
  8.     varInt number; --会议创建日期到终止日期相差天数   
  9.     varSubTime varchar2(30);  --循环日期   
  10.     varMonths number;   
  11.     TYPE c_time IS REF CURSOR;  --创建游离标记   
  12.     vrec c_time;    
  13.     yearY varchar2(10);  --日期年部分   
  14.     monthsM varchar2(10);--日期月部分(终止入库时间)   
  15. begin   
  16.   delete tbmeetmgrinfo where 11=1 ;  --先清空表中数据   
  17.   commit;   
  18.   varInt := 0;   
  19.       --一次性会议记录   
  20.       for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi from tbmeetmgr   
  21.               where HUIYIMOSHI = 1 and to_date(stoptime,'yyyy-MM-dd') >=    
  22.               to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))   
  23.       loop   
  24.         strOver := varCode.Stoptime;   
  25.         strCreate := substr(varCode.Createtime,1,10);   
  26.         meetId := varCode.Huiyishi;   
  27.         strSta := varCode.Starttime;   
  28.         strEnd := varCode.Endtime;   
  29.         varSubTime := substr(varCode.Xunhuairiqi,3);   
  30.         --最后的0代表的是一次性会议、循环模式1为单周、3为每月、4为每季度    
  31.         insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOver,0);   
  32.         commit;   
  33.       end loop;   
  34.          
  35.       --单周循环   
  36.       for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi,(( to_date(stoptime,'yyyy-mm-dd') - next_day   
  37.               (to_date(substr(createtime, 0, 10),'yyyy-mm-dd')-1,3))/7) resultNum from tbmeetmgr   
  38.               where xunhuaimoshi = 1 and to_date(stoptime,'yyyy-MM-dd') >=    
  39.               to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))   
  40.       loop   
  41.         varInt := ceil(varCode.Resultnum);   
  42.         strOver := varCode.Stoptime;   
  43.         strCreate := substr(varCode.Createtime,1,10);   
  44.         meetId := varCode.Huiyishi;   
  45.         strSta := varCode.Starttime;   
  46.         strEnd := varCode.Endtime;   
  47.         varSubTime := substr(varCode.Xunhuairiqi,3);   
  48.         OPEN vrec for SELECT to_char(next_day(to_date(strCreate,'yyyy-mm-dd')-1,ceil(varSubTime)+1)+(rownum-1)*7 , 'yyyy-MM-dd')   
  49.         from dual connect by rownum<=varInt ;     
  50.           LOOP     
  51.             FETCH vrec INTO strOverSql;  --入库终止日期   
  52.             exit when vrec%notfound;   
  53.             --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql);   
  54.             insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,1);   
  55.             commit;   
  56.           end loop;   
  57.       end loop;   
  58.          
  59.       --每月循环   
  60.          
  61.       for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,   
  62.                      Xunhuairiqi,ceil(months_between(to_date(stoptime,'yyyy-mm-dd'),   
  63.                      to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))) months from tbmeetmgr    
  64.                      where xunhuaimoshi = 3 and to_date(stoptime,'yyyy-MM-dd') >=    
  65.                      to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))   
  66.           loop   
  67.             strOver := varCodeMonths.Stoptime;   
  68.             strCreate := substr(varCodeMonths.Createtime,1,10);   
  69.             meetId := varCodeMonths.Huiyishi;   
  70.             strSta := varCodeMonths.Starttime;   
  71.             strEnd := varCodeMonths.Endtime;   
  72.             varMonths := varCodeMonths.Months;   
  73.             varSubTime := substr(varCodeMonths.Xunhuairiqi,3,4); --取得开会时间(具体哪一日)   
  74.             yearY := substr(strCreate,1,8);   
  75.             monthsM := yearY||varSubTime ;   
  76.             OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd')   
  77.             from dual connect by rownum<= ceil(varMonths) ;   
  78.                LOOP     
  79.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  80.                 exit when vrec%notfound;   
  81.                 --dbms_output.put_line('月度时间:'||strOverSql);   
  82.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,3);   
  83.                 commit;   
  84.                end loop;   
  85.           end loop;   
  86.              
  87.       --每季度循环   
  88.       for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,   
  89.                      Xunhuairiqi,ceil((to_date(stoptime,'yyyy-mm-dd')-to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))/90) months    
  90.                      from tbmeetmgr where xunhuaimoshi = 4 and to_date(stoptime,'yyyy-MM-dd') >=    
  91.                      to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))   
  92.           loop   
  93.             strOver := varCodeMonths.Stoptime;   
  94.             strCreate := substr(varCodeMonths.Createtime,1,10);   
  95.             meetId := varCodeMonths.Huiyishi;   
  96.             strSta := varCodeMonths.Starttime;   
  97.             strEnd := varCodeMonths.Endtime;   
  98.             varMonths := varCodeMonths.Months;   
  99.             varSubTime := substr(varCodeMonths.Xunhuairiqi,3,7); --取得开会时间(具体哪一日)   
  100.             yearY := substr(strCreate,1,5);   
  101.             monthsM := yearY||varSubTime ;   
  102.             OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd')   
  103.             from dual connect by rownum<= ceil(varMonths) ;   
  104.                LOOP     
  105.                 FETCH vrec INTO strOverSql;  --入库终止日期   
  106.                 exit when vrec%notfound;   
  107.                 --dbms_output.put_line('季度循环日期:'||strOverSql);   
  108.                 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,4);   
  109.                 commit;   
  110.                end loop;   
  111.           end loop;   
  112.   
  113. end;  

相关内容