How to Implement a Recursive Stored Procedure in MySQL for Calculating Quantity Hierarchy?

Recursive Stored Procedure for Calculating Quantity Hierarchy

In MySQL, creating a recursive stored procedure to compute quantities for nested hierarchical structures is a challenge that can be overcome using a carefully crafted procedure. Consider the given table, where each row represents an item with an associated quantity and potential parent.

Recursive Stored Procedure

To recursively traverse this tree-like structure and calculate the total quantity, the following stored procedure can be implemented:

<code class="mysql">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 ;</code>

Procedure Execution

To enable recursion, it's crucial to adjust MySQL settings:

<code class="mysql">SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;</code>

Once these settings are configured, the stored procedure can be invoked like this:

<code class="mysql">CALL calctotal(6, @total);
SELECT @total;</code>

Recursive Calculation Example

For the given sample data, where item 6 is a child of item 5, which is a child of item 3, and so on, the stored procedure will recursively calculate the total quantity as follows:

4 * 2 * 10 * 3 = 240

Therefore, the result of calling the procedure with number = 6 will be 240.

The above is the detailed content of How to Implement a Recursive Stored Procedure in MySQL for Calculating Quantity Hierarchy?. For more information, please follow other related articles on the PHP Chinese website!