mysql procedimiento almacenado que se llama recursivamente

Tengo la siguiente tabla:

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 

Ahora necesito un procedimiento almacenado en mysql que se llame recursivamente y devuelva la cantidad calculada. Por ejemplo, id 6 tiene 5 como padre que como 3 como padre que tiene 2 como padre. Entonces necesito calcular 4 * 2 * 10 * 3 (= 240) como resultado.

Soy bastante nuevo en los procedimientos almacenados y no los usaré muy a menudo en el futuro porque prefiero tener mi lógica comercial en el código de mi progtwig en lugar de en la base de datos. Pero en este caso no puedo evitarlo.

Tal vez un gurú de mysql (ese eres tú) pueda hackear una statement de trabajo en un par de segundos.

su trabajo solo en la versión mysql> = 5

la statement de procedimiento almacenado es esto,

puedes mejorar poco, pero esto funciona:

 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 ; 

la llamada es similar (es importante establecer esta variable):

 SET @@GLOBAL.max_sp_recursion_depth = 255; SET @@session.max_sp_recursion_depth = 255; CALL calctotal(6, @total); SELECT @total; 

Eche un vistazo a Gestión de datos jerárquicos en MySQL por Mike Hillyer.

Contiene ejemplos completamente trabajados sobre el manejo de datos jerárquicos.

¿Qué hay de evitar los procedimientos:

 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; 

¡Mira Fiddle!

¡Nota! esto no funcionará si parent_id>id la fila.

¡Aclamaciones!

 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 ;