mysql存储过程以递归方式调用自身 [英] mysql stored procedure that calls itself recursively

问题描述

我有下表:

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

现在，我需要mysql中的存储过程，该存储过程以递归方式调用自身并返回计算出的数量. 例如，id 6有5个父级，而3个父级又有2个父级. 因此，我需要计算结果4 * 2 * 10 * 3(= 240).

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.

我对存储过程还很陌生，将来我不会经常使用它们，因为我更喜欢在程序代码中而不是在数据库中使用业务逻辑.但是在这种情况下，我无法避免.

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.

也许mysql专家(就是您)可以在几秒钟内破解一条工作语句.

Maybe a mysql guru (that's you) can hack together a working statement in a couple of seconds.

推荐答案

仅在mysql版本> = 5中起作用

its work only in mysql version >= 5

存储过程声明是这个

您可以给它一点点改善，但是可以这样工作:

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;

这篇关于mysql存储过程以递归方式调用自身的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！