MySQL存储过程递归调用(二)
or for select count(id) from tb_system_category where c_code like CONCAT(cCode,'%') and c_code != cCode;
declare continue handler for not found set finished = 1;
open cur;
fetch cur into count;
close cur;
if count is null then
return 0;
else
return count;
end if;
END
4. 从临时表中查出结果并组合成字符串。
[java]
CREATE FUNCTION category_generateResult(cRand varchar(50)) RETURNS varchar(4000) CHARSET utf8 www.2cto.com
BEGIN
-- 调用的函数或存储过程：无
-- 被调用于函数或存储过程：category_findCodesByParentCode
declare finished int default 0;
declare result varchar(20000) default '';
declare thisResult varchar(200) default '';
declare cur cursor for select c_result from tb_system_temp_category_categoryTree where c_rand = cRand;
declare continue handler for not found set finished = 1;
open cur;
fetch cur into thisResult;
while finished = 0 do
set result = concat(result, thisResult, ',');
fetch cur into thisResult;
end while;
close cur;
if result is null then
return result;
else
if RIGHT(result,1) = ',' then
set result = SUBSTR(result, 1, CHAR_LENGTH(result) - 1);
end if;
return result;
end if;
END
在MySQL中，不能够直接使用函数实现递归，在上例中，使用存储过程递归调用，将需要的值存储到临时表中，然后通过对临时表进行操作后取得结果。
作者 geloin