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

约定不等于承诺〃 2024-02-19 20:23 44阅读 0赞

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

[ibatis_mysql_OUT]: https://blog.csdn.net/shy_snow/article/details/70493026