|
創建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.zuiquanben.com】
服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
