How to Calculate Hierarchical Quantities in MySQL Using a Recursive Stored Procedure?

MySQL Recursive Stored Procedure for Computing Hierarchical Quantities

The task at hand involves creating a stored procedure in MySQL that recursively calculates the computed quantity based on a parent-child relationship within a table. Let's delve into the solution.

The provided stored procedure declaration effectively calculates the total quantity for a given node using recursion. Here's how it works:

1. Procedure Declaration:

   <code class="mysql">CREATE PROCEDURE calctotal(IN number INT, OUT total INT)
   BEGIN</code>

   This declares a stored procedure named calctotal that takes an integer number as input and returns an integer total as the calculated quantity.

2. Variable Initialization:

   <code class="mysql">DECLARE parent_ID INT DEFAULT NULL;
   DECLARE tmptotal INT DEFAULT 0;
   DECLARE tmptotal2 INT DEFAULT 0;</code>

   These variables are used to hold intermediate values during the recursive process.

3. Retrieve Parent ID and Initial Quantity:

   <code class="mysql">SELECT parentid FROM test WHERE id = number INTO parent_ID;
   SELECT quantity FROM test WHERE id = number INTO tmptotal;</code>

   This retrieves the parent ID and initial quantity for the given number.

4. Recursive Call Handling:

   <code class="mysql">IF parent_ID IS NULL
    THEN
    SET total = tmptotal;    -- The node is a root node, set total to its quantity.
   ELSE
    CALL calctotal(parent_ID, tmptotal2);    
    SET total = tmptotal2 * tmptotal;    -- Total for current node is product of parent's total and current node's quantity.
   END IF;</code>

   a. If the parent ID is NULL, it means the node is a root node, so the total is equal to its quantity.
   b. Otherwise, it recursively calls calctotal with the parent ID to compute the total for the parent. Then, it multiplies this parent's total by the current node's quantity to get the total for the current node.

5. Procedure Call Example:

   <code class="mysql">SET @@GLOBAL.max_sp_recursion_depth = 255;
   SET @@session.max_sp_recursion_depth = 255;
   
   CALL calctotal(6, @total);
   SELECT @total;</code>

   To use the stored procedure, you need to set the maximum recursion depth using these configuration settings. You can then call calctotal with the appropriate number. The result is stored in the @total variable, which can be subsequently retrieved.

6. Recursive Flow:

   • The procedure starts by calculating the total for the given number.
   • If the node is a root node, its total is equal to its quantity.
   • Otherwise, it recursively computes the total for the parent node.
   • Finally, it multiplies the parent's total by its own quantity to get the total for the node.

This recursive stored procedure provides a convenient way to compute hierarchical quantities based on the specified parent-child relationships in the database table.

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