实现功能为查找节点所有子节点同时更新子节点数据,用递归进行处理。测试时始终报错:
Error Code : 1456 Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine pro_app_rs_right_update···
树的深度不超过10,怎么可能深度出问题,于是百度了一下,网上给了解决方案,加上下面代码就ok:
SET @@max_sp_recursion_depth = 100;
下面是mysql递归调用的源代码:
DROP PROCEDURE IF EXISTS pro_app_rs_right_update_role;
DELIMITER//
CREATE PROCEDURE pro_app_rs_right_update_role(
IN uid VARCHAR(225),
IN nCount INT)
BEGIN
DECLARE id VARCHAR(225);
DECLARE nLevel INT;
DECLARE STOP INT DEFAULT 0;
# 声明游标,查找id和权限水平
DECLARE cur CURSOR FOR SELECT app_rs_right.id, app_rs_right.LEVEL FROM app_rs_right
WHERE app_rs_right.pid = uid;
# 声明游标的异常处理,设置一个终止标记
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOP = 1;
SET @@max_sp_recursion_depth = 100;
# 打开游标
OPEN cur;
# 读取一行数据到游标中
FETCH cur INTO id, nLevel;
# 判断是否已经到达最后一行数据
WHILE STOP <> 1 DO
CALL pro_app_rs_right_update_role(id, nCount);
UPDATE app_rs_right SET app_rs_right.level = app_rs_right.level + nCount
WHERE app_rs_right.id = id;
FETCH cur INTO id, nLevel;
END WHILE;
CLOSE cur;
END //
# select id, level from app_rs_right where pid = '402881f53fe18bbc013fe19eda7f0003';
# call pro_app_rs_right_update_role('402881f53fe18bbc013fe19eda7f0003', 10);