oracle触发创建时间,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;

本文出自：亿恩科技【www.enkj.com】