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