SET @@max_sp_recursion_depth = 255是指可以遍历”255层“的所有节点吗,还是说总共只能执行255次存储过程
SET @@max_sp_recursion_depth = 255是指可以遍历”255层“的所有节点吗,还是说总共只能执行255次存储过程(如果是这样,那么即使是2级的树,1,2级分别有20个节点,都不够用啊)
比如:一个树结构有255个层级,每一层都有自己的好多子节点,一共有255个层级
CREATE PROCEDURE sp_createChildLst(IN rootId varchar(20),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT code_value FROM address where pid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
insert into tmpLst values (null,rootId,nDepth);
SET @@max_sp_recursion_depth = 255;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL sp_createChildLst(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END