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类型参数的存储过程并获取返回值