| 标题 | mysql存储过程，实现两个游标的循环 |
| 内容 | 今天用php开发网站，遇到一个需求。统计网站关键词的google流量，计算本周某个关键词对于上周的google流量的增长。goole流量统计是通过日志分析程序获取。本打算差值计算也用php实现，但是一想还要循环查询数据库，会造成数据库压力，于是乎编写了一个存储过程。 首先数据结构： CREATE TABLE `mobile_keywords_weeklog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `topdate` date DEFAULT NULL, `keywords_id` int(11) DEFAULT '0', `s_pv` int(11) DEFAULT '0', `s_gv` int(11) DEFAULT '0', `s_bv` int(11) DEFAULT '0', `d_value` int(11) DEFAULT '0' COMMENT '与上一周的差值', PRIMARY KEY (`id`), KEY `topdate` (`topdate`), KEY `keywords_id` (`keywords_id`), KEY `s_pv` (`s_gv`) ) ENGINE=MyISAM AUTO_INCREMENT=702 DEFAULT CHARSET=latin1 存储过程，实现了双游标的循环： DELIMITER $$ USE `brother_mobile`$$ DROP PROCEDURE IF EXISTS `pro_week_stat`$$ CREATE ` PROCEDURE `pro_week_stat`() top:BEGIN DECLARE done INT DEFAULT 0; DECLARE curr_week DATE; DECLARE last_week DATE; DECLARE a1,b1,c1 INT; DECLARE a2,b2,c2 INT; DECLARE d INT; DECLARE is_update INT DEFAULT 0; DECLARE all_week CURSOR FOR SELECT topdate FROM brother_mobile.mobile_keywords_weeklog GROUP BY topdate ORDER BY topdate DESC LIMIT 0,2; DECLARE cur1 CURSOR FOR SELECT id,keywords_id,s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = curr_week; DECLARE cur2 CURSOR FOR SELECT id,keywords_id,s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = last_week; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN all_week; FETCH all_week INTO curr_week; FETCH all_week INTO last_week; CLOSE all_week; IF IFNULL(curr_week,'')='' OR IFNULL(last_week,'')='' THEN LEAVE top; END IF; OPEN cur1; out_repeat:REPEAT FETCH cur1 INTO a1,b1,c1; BEGIN DECLARE done1 INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1; OPEN cur2; inner_repeat:REPEAT FETCH cur2 INTO a2,b2,c2; IF NOT done THEN SET is_update=0; IF b1 = b2 THEN SET d = c1-c2; SET is_update = 1; UPDATE brother_mobile.mobile_keywords_weeklog SET d_value = d WHERE id = a1; LEAVE inner_repeat; END IF; END IF; UNTIL done1 END REPEAT inner_repeat; CLOSE cur2; END; IF is_update <> 1 THEN UPDATE brother_mobile.mobile_keywords_weeklog SET d_value = c1 WHERE id = a1; END IF; UNTIL done END REPEAT out_repeat; CLOSE cur1; END$$ DELIMITER ; 其中存储过程中的具体语法可以查看mysql的帮助文档。 最后用call pro_week_stat()调用即可。 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。