mysql游标使用

今天在工作中遇到要需要循环把一个表中的数据添加到另外一个表中，并且需要比较是否有重复数据，如果有重复数据则进行更新，否则进行插入。

琢磨着需要用游标来解决。由于之前一直觉得游标是个复杂的东西，所以，一直不甚了解。今天了解了一下，发现，嘿，很简单啊。

以前自己碰到这样的总是用临时表来解决，现在发现，其实很多地方用游标会简单很多。

游标是用于在存储过程中迭代SELECT查询出的数据

看个例子基本就差不多会了

将代码附上方便以后查找

DELIMITER $$

USE `tistone`$$

DROP PROCEDURE IF EXISTS `USP_Ass_AddLinkStatus`$$

CREATE DEFINER=`webdev`@`%` PROCEDURE `USP_Ass_AddLinkStatus`(
$startTime DATETIME
)
BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE v_linkid INT;
DECLARE v_linkusage FLOAT;
DECLARE v_seriousCnt INT;
DECLARE v_mdeiumCnt INT;
DECLARE v_commonCnt INT;

/*游标声明*/
DECLARE cur1 CURSOR FOR
SELECT a.linkid,SUM(a.bytein+a.byteout)/(30*1000*1000) AS linkusage,b.seriousCnt,b.mdeiumCnt,b.commonCnt FROM `tbl_ori_networkinfo` a
LEFT JOIN 
(SELECT linkid,SUM(IF(alarmlevel=3,1,0)) AS seriousCnt,
	SUM(IF(alarmlevel=2,1,0)) AS mdeiumCnt,
	SUM(IF(alarmlevel=1,1,0)) AS commonCnt
	FROM `tbl_ori_trafficalarm_bak` 
	WHERE UNIX_TIMESTAMP(RecordTime) >= UNIX_TIMESTAMP(DATE($startTime))
	AND UNIX_TIMESTAMP(RecordTime) < UNIX_TIMESTAMP(DATE($startTime)) + 86400
	GROUP BY linkid) b 
ON a.linkid = b.linkid
	WHERE a.RecordTime BETWEEN ($startTime - INTERVAL 30 SECOND) AND $startTime 
	GROUP BY a.linkid;
	

/*异常处理*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur1;

/*开始循环*/
read_loop:LOOP
  FETCH cur1 INTO v_linkid,v_linkusage,v_seriousCnt,v_mdeiumCnt,v_commonCnt;
  IF done THEN
	LEAVE read_loop;
  END IF;
  
  INSERT INTO tbl_ass_linkstatus(linkID,linkStatus,linkUsage,seriousCnt,mdeiumCnt,commonCnt,createTime,modifyTime)
  VALUES(v_linkid,1,v_linkusage,v_seriousCnt,v_mdeiumCnt,v_commonCnt,NOW(),NOW())
  ON DUPLICATE KEY UPDATE 
  linkStatus=1,linkUsage=v_linkusage,seriousCnt=v_seriousCnt,mdeiumCnt=v_mdeiumCnt,commonCnt=v_commonCnt,modifyTime=NOW();
END LOOP;

/*关闭游标*/
CLOSE cur1;
END$$

DELIMITER ;