MySQL 存储过程实例

[sql]  view plain copy

1. DELIMITER $  
2.   
3. DROP PROCEDURE IF EXISTS `stat` $  
4. CREATE DEFINER=`liulive`@`%` PROCEDURE `stat`(startDate VARCHAR(14), endDate VARCHAR(14))  
5. BEGIN  

[sql]  view plain copy

1. /*声明变量*/  

[sql]  view plain copy

1. DECLARE total INT DEFAULT 0;  
2. DECLARE dat DATE DEFAULT NULL;  
3. DECLARE quals INT DEFAULT 0;  
4. DECLARE scheds INT DEFAULT 0;  
5. DECLARE shows INT DEFAULT 0;  
6. DECLARE kepts INT DEFAULT 0;  
7. DECLARE accepts INT DEFAULT 0;  
8. DECLARE _time VARCHAR(14) DEFAULT NULL;  
9. DECLARE assess VARCHAR(40) DEFAULT NULL;  
10.   
11. DECLARE offic VARCHAR(60) DEFAULT NULL;  
12. DECLARE recrui VARCHAR(60) DEFAULT NULL;  
13. DECLARE ma VARCHAR(60) DEFAULT NULL;  
14.   
15. DECLARE CNT INT DEFAULT 0;  
16.   
17. DECLARE _cursor_flag TINYINT(1) DEFAULT 0;  
18. /*定义光标*/  
19. DECLARE totalCalls CURSOR FOR Select count(flow_step) as total, DATE(operate_date) as dat, office, recruit, main from v_student where DATE(operate_date) >= DATE(startDate) And DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);  
20. DECLARE qualCalls CURSOR FOR Select count(flow_step) as qual, DATE(operate_date) as dat, office, recruit, main  from v_student where flow_step <> 'NQ' And DATE(operate_date) >= DATE(startDate) and DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);  
21. DECLARE schedCalls CURSOR FOR Select count(flow_step) as sched, DATE(operate_date) as dat, office, recruit, main  from v_student where flow_step = 'Assessment Scheduled' And DATE(operate_date) >= DATE(startDate) and DATE(operate_date) <= DATE(endDate) group by DATE(operate_date), office, recruit, main order by DATE(operate_date);  
22.   
23. DECLARE totalSched CURSOR FOR Select count(flow_step) as sched, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main  from v_student where flow_step = 'Assessment Scheduled' And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);  
24. DECLARE showSched CURSOR FOR Select count(shows) as shows, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main  from v_student where shows is not null And flow_step = 'Assessment Scheduled' And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);  
25. DECLARE qualSched CURSOR FOR Select count(qual) as quals, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main  from v_student where qual is not null And flow_step = 'Assessment Scheduled' And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);  
26. DECLARE keptSched CURSOR FOR Select count(kept) as kepts, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main  from v_student where kept is not null And flow_step = 'Assessment Scheduled' And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);  
27. DECLARE acceptSched CURSOR FOR Select count(accept) as accepts, officeTime, DATE(vco_date) as dat, assessor, office, recruit, main  from v_student where accept is not null And flow_step = 'Assessment Scheduled' And DATE(vco_date) >= DATE(startDate) And DATE(vco_date) <= DATE(endDate) group by officeTime, DATE(vco_date), assessor, office, main order by DATE(vco_date);  
28. nbsp;  

[sql]  view plain copy

1.   /* 定义光标结束标志 */  
2.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cursor_flag = -1;  
3.   
4.   DELETE FROM STAT_TEMP;  
5.   COMMIT;  
6.     
7.   OPEN totalCalls;  /*打开光标*/  
8.   LOOP_totalCalls:LOOP   /* 循环声明 */  
9.     FETCH totalCalls INTO total, dat, offic, recrui, ma;  
10.     IF _cursor_flag = -1 then  
11.       LEAVE LOOP_totalCalls;  
12.     END IF;  
13.     insert into STAT_TEMP(DATE_, TOTAL_CALLS, OFFICE, RECRUIT, MAIN) values(dat, total, offic, recrui, ma);  
14.   END LOOP;  
15.   COMMIT;  
16.   CLOSE totalCalls;   /* 关闭光标 */  
17.   SET _cursor_flag = 1;   /* 重置光标结束标志 */  
18.   
19.   OPEN qualCalls;  
20.   LOOP_qualCalls:LOOP  
21.     FETCH qualCalls INTO quals, dat, offic, recrui, ma;  
22.     if _cursor_flag = -1 then  
23.       LEAVE LOOP_qualCalls;  
24.     end if;  
25.     SELECT COUNT(*) INTO CNT FROM STAT_TEMP WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;  
26.     IF CNT > 0 THEN  
27.       UPDATE STAT_TEMP SET QUAL_CALLS=quals WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;  
28.       SET CNT = 0;  
29.     ELSE  
30.       insert into STAT_TEMP (DATE_, QUAL_CALLS, OFFICE, RECRUIT, MAIN) values(dat, quals, offic, recrui, ma);  
31.     END IF;  
32.   END LOOP;  
33.   COMMIT;  
34.   CLOSE qualCalls;  
35.   SET _cursor_flag = 1;  
36.   
37.   OPEN schedCalls;  
38.   LOOP_schedCalls:LOOP  
39.     FETCH schedCalls INTO scheds,dat,offic,recrui,ma;  
40.     if _cursor_flag = -1 then  
41.       LEAVE LOOP_schedCalls;  
42.     end if;  
43.     SELECT COUNT(*) INTO CNT FROM STAT_TEMP WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;  
44.     IF CNT > 0 THEN  
45.       UPDATE STAT_TEMP SET SCHED_CALLS=scheds WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma;  
46.       SET CNT = 0;  
47.     ELSE  
48.       insert into STAT_TEMP(DATE_, SCHED_CALLS, OFFICE, RECRUIT, MAIN) values(dat, scheds, offic, recrui, ma);  
49.     END IF;  
50.   END LOOP;  
51.   COMMIT;  
52.   CLOSE schedCalls;  
53.   SET _cursor_flag = 1;  
54.   
55.   OPEN totalSched;  
56.   LOOP_totalSched:LOOP  
57.     FETCH totalSched INTO scheds, _time, dat, assess, offic, recrui, ma;  
58.     if _cursor_flag = -1 then  
59.       LEAVE LOOP_totalSched;  
60.     end if;  
61.     select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
62.     if CNT > 0 then  
63.       update STAT_TEMP set SCHED=scheds WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
64.       SET CNT = 0;  
65.     else  
66.       insert into STAT_TEMP(DATE_, SCHED, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, scheds, assess, offic, recrui, ma);  
67.     end if;  
68.   END LOOP;  
69.   COMMIT;  
70.   CLOSE totalSched;  
71.   SET _cursor_flag = 1;  
72.   
73.   OPEN showSched;  
74.   LOOP_showSched:LOOP  
75.     FETCH showSched INTO shows, _time, dat, assess,offic,recrui,ma;  
76.     if _cursor_flag = -1 then  
77.       LEAVE LOOP_showSched;  
78.     end if;  
79.     select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
80.     if CNT > 0 then  
81.       update STAT_TEMP set SHOWS=shows WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
82.       SET CNT = 0;  
83.     else  
84.       insert into STAT_TEMP(DATE_, SHOWS, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, scheds, assess, offic, recrui, ma);  
85.     end if;  
86.   END LOOP;  
87.   COMMIT;  
88.   CLOSE showSched;  
89.   SET _cursor_flag = 1;  
90.   
91.   OPEN qualSched;  
92.   LOOP_qualSched:LOOP  
93.     FETCH qualSched INTO quals, _time, dat, assess,offic,recrui,ma;  
94.     if _cursor_flag = -1 then  
95.       LEAVE LOOP_qualSched;  
96.     end if;  
97.     select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
98.     if CNT > 0 then  
99.       update STAT_TEMP set QUAL=quals WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
100.       SET CNT = 0;  
101.     else  
102.       insert into STAT_TEMP(DATE_, QUAL, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, quals, assess, offic, recrui, ma);  
103.     end if;  
104.   END LOOP;  
105.   COMMIT;  
106.   CLOSE qualSched;  
107.   SET _cursor_flag = 1;  
108.   
109.   OPEN keptSched;  
110.   LOOP_keptSched:LOOP  
111.     FETCH keptSched INTO kepts, _time, dat, assess, offic, recrui, ma;  
112.     if _cursor_flag = -1 then  
113.       LEAVE LOOP_keptSched;  
114.     end if;  
115.     select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
116.     if CNT > 0 then  
117.       update STAT_TEMP set KEPT=kepts WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
118.       SET CNT = 0;  
119.     else  
120.       insert into STAT_TEMP(DATE_, KEPT, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, kepts, assess, offic, recrui, ma);  
121.     end if;  
122.   END LOOP;  
123.   COMMIT;  
124.   CLOSE keptSched;  
125.   SET _cursor_flag = 1;  
126.   
127.   OPEN acceptSched;  
128.   LOOP_acceptSched:LOOP  
129.     FETCH acceptSched INTO kepts, _time, dat, assess, offic, recrui, ma;  
130.     if _cursor_flag = -1 then  
131.       LEAVE LOOP_acceptSched;  
132.     end if;  
133.     select count(*) into CNT from STAT_TEMP where DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
134.     if CNT > 0 then  
135.       update STAT_TEMP set KEPT=kepts WHERE DATE_ = dat And OFFICE = offic And RECRUIT = recrui And MAIN = ma And ASSESSOR=assess;  
136.       SET CNT = 0;  
137.     else  
138.       insert into STAT_TEMP(DATE_, KEPT, ASSESSOR, OFFICE, RECRUIT, MAIN) values(dat, kepts, assess, offic, recrui, ma);  
139.     end if;  
140.   END LOOP;  
141.   COMMIT;  
142.   CLOSE acceptSched;  
143. END $  
144.   
145. DELIMITER ;