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 ;