mysql存储过程使用递归
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:
[sql] view plain copy
- SET @@max_sp_recursion_depth = 100;
下面是mysql递归调用的源代码:
[sql] view plain copy
- 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);