DB2 et le SQL récursif

Cet article a pour vocation d'expliquer le fonctionnement du SQL récursif. Ce tutoriel est basé sur DB2 mais il est globalement applicable à d'autres gestionnaires de bases de données comme par exemple SQL Server. Cette technologie est à la fois simple et très puissante et permet d'éviter des procédures stockées complexes ou d'implémenter la récursivité au niveau du client qui entraine un important trafic de données sur le réseau.
Commentez ce tutoriel : 1 commentaire Donner une note à l'article (0)

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Présentation de l'exemple

Pour illustrer les possibilités du SQL récursif dans DB2, prenons l'exemple classique de la composition d'un produit fini. Le produit fini est composé de produits de base mais également de produits semi-finis. Un produit fini peut également être un produit semi-fini pour un autre produit.

Structure de la DB
Schéma des données

L'approche la plus simple pour obtenir des informations comme "Quels produits de base dois-je prendre pour réaliser complètement mon produit ?" ou "Quelle est la composition d'un produit et de chaque produit qui le compose ?" est évidemment la récursivité. Vous pouvez bien sûr la développer dans le programme client. Ce qui entrainera un important trafic entre le client et le serveur. Une possibilité souvent méconnue ou oubliée est l'utilisation du SQL récursif.

II. La syntaxe

L'instruction permettant de réaliser un "Select" récursif est divisé en quatre parties:

  • La première partie définit la table temporaire qui recevra le résultat tout d'abord du "Select" d'initialisation et ensuite des résultats successifs du "Select" de récursion.
  • La seconde est le "Select" d'initialisation.
  • La troisième est le "Select" qui sera exécuté de manière récursive.
  • La quatrième partie est le "Select" final qui est exécuté sur la table temporaire
La syntaxe du SQL récursif
Sélectionnez
WITH
	nomDeLaTableTemporaire (champ1, champ2, champ3, ...) AS
	(
		SELECT d'initialisation
 
		UNION ALL
 
		SELECT de récursion
	)
	SELECT de résultat

Concrètement, c'est comme si la table temporaire recevait le résultat du premier select. Ensuite, pour chaque ligne contenue dans cette table temporaire, le "Select" de récursion est exécuté. Le résultat est ajouté à la table temporaire et servira donc d'entrée pour autant de nouvelle exécution du "Select" que de lignes ajoutées et ainsi de suite. Au niveau moteur DB2, il est probable que cela ne se passe pas exactement de cette manière mais c'est l'idée générale.

III. Exemple: La liste des produits de base

Pour obtenir la liste des produits de base nécessaires à la réalisation du produit fini dont l'Id est 1, la commande SQL sera:

Commande SQL
Sélectionnez
WITH
   ProduitsDeBase (Id, Qte) AS
   (
       SELECT Racine.IdComposant, Racine.Qte 
           FROM Recettes Racine 
           WHERE Racine.IdCompose = 1 
 
       UNION ALL
 
       SELECT Enfant.IdComposant, Enfant.Qte * Parent.Qte 
           FROM ProduitsDeBase Parent, Recettes Enfant 
           WHERE Parent.Id = Enfant.IdCompose
   ) 
   SELECT ProduitsDeBase.Id, Sum(Qte), Produits.Nom 
       FROM ProduitsDeBase, Produits 
       WHERE ProduitsDeBase.Id = Produits.ID AND  Produits.Type = 0 
       GROUP BY ProduitsDeBase.Id, Produits.Nom 
       ORDER BY Produits.nom

Dans le "Select" de récursion, une jointure est réalisée sur la table parent (table temporaire) de manière à obtenir tout les composants d'un élément. Le type de produit sera nécessaire pour la sélection finale.

Notez que pour obtenir le nombre total d'élément, il est nécessaire de multiplier la quantité du composant par la quantité du composé.

Dans notre exemple, le produit fini 'A' concerné est composé de 2 produits 'B', 1 'C' et 1 'D'. Le produit 'C' est lui même composé de 2 'D' et 3 'E'. 'B' quant a lui est composé de 1 'E', 2 'F' et 2 'C'. Le résultat de la requête donne donc:

Résultat
Sélectionnez
ID          2           NOM
----------- ----------- ------------------------------
SQL0347W  The recursive common table expression "DB2ADMIN.PRODUITSDEBASE" may
contain an infinite loop.  SQLSTATE=01605
 
          4           6 D
          5          17 E
          6           4 F
 
  3 record(s) selected with 1 warning messages printed.

Notez le message d'avertissement. Nous verrons dans le chapitre suivant pourquoi ce message est affiché et comment éviter ce problème.

IV. La récursivité maitrisée

Imaginons que dans la recette, un produit fini contienne un autre produit fini qui lui même contient le premier. Soit A composé de B et B composé de A. Dans ce cas, la récursivité va entrer en boucle.

Pour éviter ce piège, vous devez limiter la profondeur de la récursivité en utilisant un compteur. Dans l'exemple, nous allons utiliser une colonne appelée "Niveau". Elle est initialisée à 0 et ensuite incrémentée de 1 à chaque appel récursif. Une condition sur cette colonne permet alors de bloquer la récursivité. A vous de choisir un nombre suffisamment grand pour que le traitement puisse être complet mais suffisamment petit pour éviter une boucle très pénalisante en temps en cas d'anomalie dans les données.

Commande SQL
Sélectionnez
WITH
   ProduitsDeBase (Niveau,Id, Qte) AS
   (
       SELECT 0, Racine.IdComposant, Racine.Qte 
           FROM Recettes Racine 
           WHERE Racine.IdCompose = 1 
 
       UNION ALL
 
       SELECT Parent.Niveau + 1, Enfant.IdComposant, Enfant.Qte * Parent.Qte 
           FROM ProduitsDeBase Parent, Recettes Enfant 
           WHERE Parent.Id = Enfant.IdCompose AND Parent.Niveau < 10
   ) 
   SELECT ProduitsDeBase.Id, Sum(Qte), Produits.Nom 
       FROM ProduitsDeBase, Produits 
       WHERE ProduitsDeBase.Id = Produits.ID AND  Produits.Type = 0 
       GROUP BY ProduitsDeBase.Id, Produits.Nom 
       ORDER BY Produits.nom
Résultat
Sélectionnez
ID          2           NOM
----------- ----------- ------------------------------
          4           6 D
          5          17 E
          6           4 F
 
  3 record(s) selected.

La profondeur maximum a, dans ce cas, été limitée à dix.

V. Conclusion

Souvent méconnu, le SQL récursif est un outil puissant et pratique qui permet d'économiser un trafic important entre le serveur et le client qui autrement aurait du assurer la récursivité par une succession de lecture. Toutefois, il est impératif de contrôler la récursion sous peine de voir le serveur entrer en boucle ou créer une table intermédiaire d'une taille astronomique.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2007 Jean-Alain Baeyens. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.