当前位置:Gxlcms > mysql > mysql存储过程,实现两个游标的循环

mysql存储过程,实现两个游标的循环

时间:2021-07-01 10:21:17 帮助过:59人阅读

今天用php开发网站,遇到一个需求。统计网站关键词的google流量,计算本周某个关键词对于上周的google流量的增长。goole流量统计是通过日志分析程序获

  今天用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 DEFINER=`root`@`%` 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()调用即可。

人气教程排行