mysql stored procedure that calls itself recursively
Solution 1
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;
Solution 2
Take a look at Managing Hierarchical Data in MySQL by Mike Hillyer.
It contains fully worked examples on dealing with hierarchical data.
Jürgen Steinblock
Updated on February 27, 2020Comments
-
Jürgen Steinblock about 4 years
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.
-
Jürgen Steinblock almost 14 years+1 for the link, very interesting. I know how to get the data that I want with self joins, but I want to have this as a Stored Procedure (Function) because I really need this a couple of times in one query which will greatly reduce the redability if I reuse my code all over again.
-
bradgonesurfing almost 14 yearsYou need to read further on because the article is not really about self joins but about nested sets which is a completely different thing.
-
Jürgen Steinblock almost 14 yearsThat looks pretty good. I implemented this like a function and I always get the error "Recursive stored functions and triggers are not allowed" but my addison-wesley "MySQL 5" book claims that recursions will work for functions, too. Any final thoughts???
-
Haim Evgi almost 14 yearsStored functions cannot be recursive. its from the offfical site : dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html
-
Jürgen Steinblock almost 14 yearsThat's mean from the mysql team but I suppose they have their reasons for disallowing this. I hoped I could do
SELECT id, computed_quantity(id) FROM table
. Anyway, I was able to do this as a recursive procedure with your help but that required me some additional c# client side code to get the result I wanted.