mysql递归

2015-07-20 06:08:56 128 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SuperCustomer 墨衍会员 · AI 创作全网分发关注 + 加入社区

MySql递归查询和Oracle递归查询 MySql的递归查询和 Oracle的递归查询阅读详情

依赖关系表：t_task_link(task_id int, task_pid int);
依赖关系为一个有向无环图，例如：

当添加依赖关系时需要检测是否有回路，下面是我写的存储过程，有的能能正确检测，有的却不行，希望高手指点

-- 检查任务依赖是否有回路

DROP PROCEDURE IF EXISTS check_loop;

CREATE PROCEDURE check_loop (in_task_id INT, in_task_pid INT, OUT out_status TINYINT)

BEGIN

	DECLARE _task_id INT;

	DECLARE done INT DEFAULT FALSE;

	DECLARE cur CURSOR FOR SELECT task_pid FROM t_task_link WHERE task_id = in_task_pid;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;



	OPEN cur;

	read_loop : LOOP

		FETCH cur INTO _task_id;

		IF in_task_id = _task_id THEN

			SET out_status = 1;

			SET done = TRUE;

		END IF;



		IF done THEN

			LEAVE read_loop;

		END IF;



		CALL check_loop (in_task_id, _task_id, @out_status);

	END LOOP;

	CLOSE cur;

END;



DROP PROCEDURE IF EXISTS exists_loop;

CREATE PROCEDURE exists_loop (in_task_id INT, in_task_pid INT)

BEGIN

	SET @@max_sp_recursion_depth = 255;

	CALL check_loop (in_task_id, in_task_pid, @out_status);

	SELECT IFNULL(@out_status, 0);

END;