mysql游标type_mysql游标和自定义函数实例

DELIMITER //

DROP FUNCTION IF EXISTS getTopicTypeNames//

CREATE FUNCTION getTopicTypeNames(delim VARCHAR(20),tid INT) RETURNS VARCHAR(255)

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE cnt INT DEFAULT 0;

DECLARE name_temp VARCHAR(255) DEFAULT '';

DECLARE result VARCHAR(255) DEFAULT '';

DECLARE typeNamesCursor CURSOR FOR SELECT tt.name FROM pcbest_topic_type tt,pcbest_topic_type_relation ttr WHERE ttr.topicTypeId = tt.typeId AND tt.flag=1 AND ttr.topicId=tid ORDER BY tt.level ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN typeNamesCursor;/*打开游标*/

REPEAT

FETCH typeNamesCursor INTO name_temp;

IF NOT done THEN

IF cnt=0 THEN

SET result=name_temp;

ELSE

SET result=CONCAT(result,delim,name_temp);/*拼接字名称*/

END IF;

SET cnt=cnt+1;

END IF;

UNTIL done END REPEAT;

CLOSE typeNamesCursor;/*关闭游标*/

RETURN result;

END//

DELIMITER ;

/* 调用 */

select getTopicTypeNames(';',t.topicId) from pcbest_topic t where t.topicId<2000;