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;
 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]