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;