程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql存儲過程,實現兩個游標的循環

mysql存儲過程,實現兩個游標的循環

編輯:MySQL綜合教程

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 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()調用即可。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved