mysql存储过程使用递归

来源：互联网发布：开淘宝需要交保证金吗编辑：程序博客网时间：2024/06/01 10:04

实现功能为查找节点所有子节点同时更新子节点数据，用递归进行处理。测试时始终报错：

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)BEGINDECLARE 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);