写一个存储过程,结果老报错,提示说是死循环错误,
call core_getparentjob(10,1,@a) Error Code: 1456.
Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine core_getparentjob
怎么检查也不知道哪里出了问题。
不过执行这条语句,SET @@SESSION.max_sp_recursion_depth=25; 设置循环深度,至少不报错。
存储过程
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `core_getparentjob`(in jobId int, in xtype int, out tempJobId int)
BEGIN
DECLARE parentJobId int;
DECLARE tt int;
DECLARE tc int;
set parentJobId = 0;
-- Get the parent job id
select parentId into parentJobId from core_job ta where ta.ID_ = jobId;
-- Get the manager
if xtype = 1 then
set tc = 0;
select count(*) into tc from core_job ta where ta.ID_ = parentJobId and ta.isManager = 1;
if tc = 0 and parentJobId > 0 then
call core_getparentjob(parentJobId,1, tt);
set parentJobId = tt;
end if;
end if;
set tempJobId = parentJobId;
select parentJobId;
END
MySQL官方文档指出 函数无法用递归调用
存储过程可以用递归调用。但是,默认情况下,是禁用的。
还有其他一些参数需要调整。