mysql 1337 游标嵌套_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 orcondition declaration after cursororhandler declaration”

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

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