mysql stored procedure that calls itself recursively

Question

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.

Hecate

This question seems to be aiming for a similar solution: https://mcmap.net/q/1124141/-hierarchical-data-in-mysql. Basically, this is tricky in mysql! – Anchoress

Recursion in stored procedures is permitted but disabled by default. To enable recursion, set the max_sp_recursion_depth server system variable to a value greater than zero – Showcase

The statement "WITH RECURSIVE TABLE() AS" is not recognized from mysql? – Eventful

Answer 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;

Showcase

That 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??? – Hamitic

Stored functions cannot be recursive. its from the offfical site : dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html – Showcase

That'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. – Hamitic

Answer 2

Take a look at Managing Hierarchical Data in MySQL by Mike Hillyer.

It contains fully worked examples on dealing with hierarchical data.

Cab

+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. – Hamitic

You need to read further on because the article is not really about self joins but about nested sets which is a completely different thing. – Cab

Answer 3

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;

Check out Fiddle!

Note! this will not work if row's parent_id>id.

Cheers!

Neoplatonism

Answer 4

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 ;

Hankow