MySQL 递归调用自身的mysql存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3438111/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql stored procedure that calls itself recursively
提问by Jürgen Steinblock
I have the following table:
我有下表：
id | parent_id | quantity
-------------------------
1 | null | 5
2 | null | 3
3 | 2 | 10
4 | 2 | 15
5 | 3 | 2
6 | 5 | 4
7 | 1 | 9
Now I need a stored procedure in mysql that calls itself recursively and returns the computed quantity.
For example the id 6 has 5 as a parent which as 3 as a parent which has 2 as a parent.
So I need to compute 4 * 2 * 10 * 3( = 240) as a result.
现在我需要 mysql 中的一个存储过程，它递归地调用自己并返回计算出的数量。例如，id 6 的父值为 5，父值为 3，父值为 2。所以我需要计算4 * 2 * 10 * 3（= 240）作为结果。
I am fairly new to stored procedures and I won't use them very often in the future because I prefer having my business logic in my program code rather then in the database. But in this case I can't avoid it.
我对存储过程还很陌生，将来我不会经常使用它们，因为我更喜欢在程序代码中而不是在数据库中包含我的业务逻辑。但在这种情况下，我无法避免。
Maybe a mysql guru (that's you) can hack together a working statement in a couple of seconds.
也许一个 mysql 大师（那就是你）可以在几秒钟内编写一个工作语句。
回答by Haim Evgi
its work only in mysql version >= 5
它只适用于 mysql 版本 >= 5
the stored procedure declaration is this,
存储过程声明是这样的，
you can give it little improve , but this working :
你可以给它一点改进，但这个工作：
DELIMITER $$
CREATE PROCEDURE calctotal(
IN number INT,
OUT total INT
)
BEGIN
DECLARE parent_ID INT DEFAULT NULL ;
DECLARE tmptotal INT DEFAULT 0;
DECLARE tmptotal2 INT DEFAULT 0;
SELECT parentid FROM test WHERE id = number INTO parent_ID;
SELECT quantity FROM test WHERE id = number INTO tmptotal;
IF parent_ID IS NULL
THEN
SET total = tmptotal;
ELSE
CALL calctotal(parent_ID, tmptotal2);
SET total = tmptotal2 * tmptotal;
END IF;
END$$
DELIMITER ;
the calling is like (its important to set this variable) :
调用就像（设置此变量很重要）：
SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;
CALL calctotal(6, @total);
SELECT @total;
回答by bradgonesurfing
Take a look at Managing Hierarchical Data in MySQLby Mike Hillyer.
看一看Mike Hillyer在 MySQL中管理分层数据。
It contains fully worked examples on dealing with hierarchical data.
它包含处理分层数据的完整示例。
回答by Victor.Palyvoda
How about avoiding procedures:
如何避免程序：
SELECT quantity from (
SELECT @rq:=parent_id as id, @val:=@val*quantity as quantity from (
select * from testTable order by -id limit 1000000 # 'limit' is required for MariaDB if we want to sort rows in subquery
) t # we have to inverse ids first in order to get this working...
join
( select @rq:= 6 /* example query */, @val:= 1 /* we are going to multiply values */) tmp
where id=@rq
) c where id is null;
Note! this will not work if row's parent_id>id.
笔记！如果行的parent_id>id.
Cheers!
干杯!
回答by ArunKumar
DELIMITER $$
CREATE DEFINER=`arun`@`%` PROCEDURE `recursivesubtree`( in iroot int(100) , in ilevel int(110) , in locid int(101) )
BEGIN
DECLARE irows,ichildid,iparentid,ichildcount,done INT DEFAULT 0;
DECLARE cname VARCHAR(64);
SET irows = ( SELECT COUNT(*) FROM account WHERE parent_id=iroot and location_id=locid );
IF ilevel = 0 THEN
DROP TEMPORARY TABLE IF EXISTS _descendants;
CREATE TEMPORARY TABLE _descendants (
childID INT, parentID INT, name VARCHAR(64), childcount INT, level INT
);
END IF;
IF irows > 0 THEN
BEGIN
DECLARE cur CURSOR FOR
SELECT
f.account_id,f.parent_id,f.account_name,
(SELECT COUNT(*) FROM account WHERE parent_id=t.account_id and location_id=locid ) AS childcount
FROM account t JOIN account f ON t.account_id=f.account_id
WHERE t.parent_id=iroot and t.location_id=locid
ORDER BY childcount<>0,t.account_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
WHILE NOT done DO
FETCH cur INTO ichildid,iparentid,cname,ichildcount;
IF NOT done THEN
INSERT INTO _descendants VALUES(ichildid,iparentid,cname,ichildcount,ilevel );
IF ichildcount > 0 THEN
CALL recursivesubtree( ichildid, ilevel + 1 );
END IF;
END IF;
END WHILE;
CLOSE cur;
END;
END IF;
IF ilevel = 0 THEN
-- Show result table headed by name that corresponds to iroot:
SET cname = (SELECT account_name FROM account WHERE account_id=iroot and location_id=locid );
SET @sql = CONCAT('SELECT CONCAT(REPEAT(CHAR(36),2*level),IF(childcount,UPPER(name),name))',
' AS ', CHAR(39),cname,CHAR(39),' FROM _descendants');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
END IF;
END$$
DELIMITER ;
