mysql游标和自定义函数实例
程序员文章站
2022-05-08 21:56:52
...
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;