MYSQL写的一个定时执行的存储过程，仅供参考

ROP PROCEDURE IF EXISTS `proc__gl`;

DELIMITER // CREATE PROCEDURE `proc__gl` () BEGIN DECLARE waringcount int; DECLARE waringcountnoread int; DECLARE noticecount int; DECLARE noticecountnoread int; DECLARE gs_id int; DECLARE s int DEFAULT 0; DECLARE ceshi int DEFAULT 0;

//声明游标 DECLARE cursor_notic CURSOR FOR SELECT A1.waringcount AS temp_waringcount, A1.waringcountnoread AS temp_waringcountnoread, A1.noticecount AS temp_noticecount, A1.noticecountnoread AS temp_noticecountnoread, A1.gs_id AS temp_gs_id FROM ( SELECT a.gs_name, a.gs_id, SUM( CASE WHEN a.type = '预警信息' THEN 1 ELSE 0 END ) AS waringcount, SUM( CASE WHEN a.type = '预警信息' AND a.isread = 0 THEN 1 ELSE 0 END ) AS waringcountnoread, SUM( CASE WHEN a.type = '公告信息' THEN 1 ELSE 0 END ) AS noticecount, SUM( CASE WHEN a.type = '公告信息' AND a.isread = 0 THEN 1 ELSE 0 END ) AS noticecountnoread FROM t a GROUP BY a.gs_id ) A1 JOIN ( SELECT b.gsnameId FROM t1 b GROUP BY b.gsnameId ) A2 ON A1.gs_id = A2.gsnameId ; #设置一个终止标记 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = 1 ; OPEN cursor_notic; #获取游标当前指针的记录，读取一行数据并传给变量waringcount,等等 FETCH cursor_notic INTO waringcount, waringcountnoread, noticecount, noticecountnoread, gs_id; #开始循环，判断是否游标已经到达了最后作为循环条件 while s <> 1 DO SELECT zsgl.gsnameId into ceshi from zs_saas_yunyingjiankong_gl_kehuguanli zsgl WHERE zsgl.gsnameId=gs_id; UPDATE t2 zsgl SET zsgl.waringcount = waringcount, zsgl.waringIsreadno = waringcountnoread, zsgl.noticcount = noticecount, zsgl.noticIsreadno = noticecountnoread WHERE zsgl.gsnameId = gs_id;

SELECT ceshi; FETCH cursor_notic INTO waringcount, waringcountnoread, noticecount, noticecountnoread, gs_id; END WHILE; CLOSE cursor_notic;

END//

DELIMITER ;