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