How Can a Recursive Stored Procedure in MySQL Calculate Quantity Hierarchy?

Recursive Stored Procedure for Computing Quantity Hierarchy

In situations like the one presented, where hierarchical calculations are required, a recursive stored procedure in MySQL can provide an efficient solution.

For the given table, where the quantity column represents the quantity of an item and the parent_id column indicates its parent item, a stored procedure can be crafted to recursively calculate the total quantity for a given item, considering all its ancestors in the hierarchy.

Stored Procedure Declaration

The following MySQL stored procedure definition allows for recursive calling and calculates the total quantity:

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 ;

In this stored procedure, the number parameter represents the ID of the item for which the total quantity is to be calculated, and the total output parameter holds the computed result.

Calling the Stored Procedure

To utilize the stored procedure, it's crucial to set the following session variables for recursion depth:

SET @@GLOBAL.max_sp_recursion_depth = 255;
SET @@session.max_sp_recursion_depth = 255;

After setting these variables, the stored procedure can be invoked using the following statement:

CALL calctotal(6, @total);
SELECT @total;

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