mysql存储过程 递归_mysql存储过程以递归方式调用自身

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.

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.

解决方案

its work only in mysql version >= 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;