mysql中的游标使用案例

DELIMITER $$

DROP PROCEDURE IF EXISTS `curTest`$$

CREATE PROCEDURE curTest(IN _myId INT)
BEGIN
  DECLARE ids VARCHAR(20);
    -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
  -- 定义游标
  DECLARE cur CURSOR FOR SELECT GROUP_CONCAT(id) FROM detail WHERE splitFlag=1 AND myId=_myId GROUP BY code;
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 打开游标
  OPEN cur;
  updateLoop:LOOP
    FETCH cur INTO ids;
    -- 跳出循环
    IF done THEN
      LEAVE updateLoop;
    END IF;
    -- 业务开始
    UPDATE detail a,
    (SELECT nos,id FROM detail WHERE FIND_IN_SET(id,ids) ORDER BY LENGTH(nos) DESC LIMIT 0,1) b  
    SET a.nos= b.nos WHERE FIND_IN_SET(a.id,ids);
  
  END LOOP;
  CLOSE cur;  
END$$

DELIMITER ;

 

注意：

1.变量的定义不要和你的select的列的键同名！不然，fetch into 会失败！

2.变量定义顺序不对会报错 “Variable or condition declaration after cursor or handler declaration” 

报错参见：http://stackoverflow.com/questions/18780974/mysql-error-1337

 

参考：http://www.cnblogs.com/trying/p/3296793.html