How Can You Use Recursive Stored Procedures to Calculate Computed Quantities in MySQL?

Stored Procedure Recursion for Calculating Computed Quantities

Creating a recursive stored procedure in MySQL to determine the computed quantity of a given item requires understanding the table structure and recursive logic.

Problem Background

Given a table with columns 'id', 'parent_id', and 'quantity', the goal is to compute the total quantity of an item by recursively traversing through its parent-child relationships. For instance, item 6 has a computed quantity of 240 because its parents are 5 (quantity 4), 3 (quantity 2), and 2 (quantity 10).

Solution: Recursive Stored Procedure

<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 Invocation

To call the stored procedure and obtain the computed quantity for item 6:

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

CALL calctotal(6, @total);
SELECT @total;</code>

Procedure Breakdown

• Input and Output Parameters: IN number specifies the starting item, and OUT total returns the computed quantity.
• Recursion Logic: The parent ID and quantity for the given item are retrieved and stored in parent_ID and tmptotal. If there is no parent (i.e., the item is at the root), total is set to tmptotal. Otherwise, a recursive call is made to calctotal with parent_ID as the input, and the result is stored in tmptotal2. The computed total is then calculated as total = tmptotal2 * tmptotal.
• Procedure Invocation: Before calling the procedure, the maximum recursion depth must be adjusted to avoid an error due to excessive recursion. The actual call takes 6 as input, and the calculated total is stored in the variable @total, which can be retrieved using SELECT @total.

The above is the detailed content of How Can You Use Recursive Stored Procedures to Calculate Computed Quantities in MySQL?. For more information, please follow other related articles on the PHP Chinese website!