mysql 游标不终止_mysql 游标问题，只走两步

DELIMITER$$USE`amwellcustomline`$$DROPFUNCTIONIFEXISTS`f_changeIdsToValues`$$CREATEDEFINER=`root`@`%`FUNCTION`f_changeIdsToValues`(idsVARCHAR(50))RETURNSVARCHAR(500)CHARS...

DELIMITER $$

USE `amwellcustomline`$$

DROP FUNCTION IF EXISTS `f_changeIdsToValues`$$

CREATE DEFINER=`root`@`%` FUNCTION `f_changeIdsToValues`(ids VARCHAR(50)) RETURNS VARCHAR(500) CHARSET utf8

BEGIN

DECLARE stationsName VARCHAR(500) DEFAULT ''; -- 站台名称集合

DECLAREv_stationsName VARCHAR(50) DEFAULT ''; -- 站点名字

-- 定义游标结束标识

DECLARE STOP INT DEFAULT 0;

DECLARE cur CURSOR FOR (SELECT stationName FROM line_station_info WHERE POSITION(CONCAT(stationinfoid) IN ids)!=0 ORDER BY FIELD(stationinfoid,ids)) ;

-- DECLARE cur CURSOR FOR (SELECT stationName FROM line_station_info WHERE stationinfoid IN ('140820153535677000','140820153535742001','140820153535784002')) ;

-- 声明游标的异常处理，设置一个终止标记

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP = 1;

-- 打开游标

OPEN cur;

FETCH cur INTO v_stationsName;

WHILE STOP <> 1 DO

SET v_stationsName = CONCAT(v_stationsName ,"-") ;

SET stationsName = CONCAT(stationsName,v_stationsName) ;

-- 获取下一行数据。

FETCH cur INTO v_stationsName;

END WHILE;

-- 关闭游标

CLOSE cur;

RETURN stationsName;

END$$

DELIMITER ;

如果我把ids传过来，不管传多少个，只会有两条结果，如果直接写死(上面有注释)，就运行正常，求高手解答..不好意思，没份数了

展开