mysql存储过程迭代自己获取所有子孙

mysql函数无法迭代获取结果集，只能用存储过程的OUT类型输出参数来实现，迭代调用并select出OUT类型的参数值拼接起来。注意null和字符串拼接结果还是null要做ifnull处理。

DROP PROCEDURE F_GETSUBJOB;
CREATE PROCEDURE F_GETSUBJOB(P_ROOT_ID VARCHAR(255),P_TMP VARCHAR(4000),OUT o_retsult VARCHAR(4000))
begin
 DECLARE sub_ret VARCHAR(4000);
  -- 遍历数据结束标志
 DECLARE done INTEGER DEFAULT FALSE;
 DECLARE isexist INTEGER DEFAULT 0;
 DECLARE ID_JOB_TMP VARCHAR(255);
-- 定义游标
 DECLARE ALL_CURSOtable_entryJOB CURSOR FOR SELECT DISTINCT a.VALUE_STR FROM table_entry e,table_attr a WHERE  e.ID_JOBENTRY= a.ID_JOBENTRY and a.CODE='jobid' AND e.ID_JOB= P_ROOT_ID; 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 -- DECLARE EXIT HANDLER FOR SQLEXCEPTION ;
 SET @@max_sp_recursion_depth = 255; -- 设置最大迭代深度
BEGIN
-- 打开游标
OPEN ALL_CURSOtable_entryJOB;

-- 开始循环
  read_loop: LOOP
-- 从游标中取出一条
 FETCH ALL_CURSOtable_entryJOB INTO ID_JOB_TMP;
    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件
	select IFNULL(find_in_set(ID_JOB_TMP,o_retsult),0) INTO  isexist;
 IF isexist <= 0 THEN
 if o_retsult is not null THEN
  SELECT CONCAT(IFNULL(o_retsult,''),',',IFNULL(ID_JOB_TMP,'')) into o_retsult;
 else  
   SELECT IFNULL(ID_JOB_TMP,'') into o_retsult;
	 CALL F_GETSUBJOB(ID_JOB_TMP,o_retsult,@sub_ret); -- 迭代调用
	 select @sub_ret into sub_ret;
	 IF sub_ret is not null THEN
	 SELECT CONCAT(IFNULL(o_retsult,''),',',IFNULL(@sub_ret,'')) into o_retsult;
	 END IF;
 end if;
 END IF;
 
 END LOOP;
-- 关闭游标
CLOSE ALL_CURSOtable_entryJOB;
END;
end
;

查询：

CALL F_GETSUBJOB('440','',@xxx);
select @xxx;

java获取返回值参考我的另外一篇ibatis调用mysql带OUT类型参数的存储过程并获取返回值