Comment calculer des quantités hiérarchiques dans MySQL à l'aide d'une procédure stockée récursive ?

Procédure stockée récursive MySQL pour le calcul de quantités hiérarchiques

La tâche à accomplir consiste à créer une procédure stockée dans MySQL qui calcule de manière récursive la quantité calculée en fonction sur une relation parent-enfant au sein d'une table. Examinons la solution.

La déclaration de procédure stockée fournie calcule efficacement la quantité totale pour un nœud donné en utilisant la récursivité. Voici comment cela fonctionne :

1. Déclaration de procédure :

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

   Cela déclare une procédure stockée nommée calctotal qui prend un nombre entier en entrée et renvoie un total entier comme quantité calculée.

2. Initialisation des variables :

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

   Ces variables sont utilisées pour contenir des valeurs intermédiaires pendant le processus récursif. processus.

3. Récupérer l'ID parent et la quantité initiale :

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

   Cela récupère l'ID parent et la quantité initiale pour le numéro donné.

4. Traitement des appels récursifs :

   <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. Si l'ID parent est NULL, cela signifie que le nœud est un nœud racine, donc le total est égal à sa quantité.
   b. Sinon, il appelle récursivement calctotal avec l'ID parent pour calculer le total du parent. Ensuite, il multiplie le total de ce parent par la quantité du nœud actuel pour obtenir le total du nœud actuel.

5. Exemple d'appel de procédure :

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

   Pour utiliser la procédure stockée, vous devez définir la profondeur de récursion maximale à l'aide de ces paramètres de configuration. Vous pouvez ensuite appeler calctotal avec le numéro approprié. Le résultat est stocké dans la variable @total, qui peut être récupéré ultérieurement.

6. Flux récursif :

   • La procédure commence par calculer le total pour le nombre donné.
   • Si le nœud est un nœud racine, son total est égal à sa quantité.
   • Sinon, il calcule de manière récursive le total pour le nœud parent.
   • Enfin, il multiplie le total du parent par sa propre quantité pour obtenir le total du nœud.

Cette procédure stockée récursive fournit un moyen pratique de calculer des quantités hiérarchiques. en fonction des relations parent-enfant spécifiées dans la table de base de données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!