DB2 et le SQL récursif

Jean-Alain Baeyens

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 : Commentez ♪

- Titre : DB2 et le SQL récursif

- Auteur : Jean-Alain Baeyens

- Parution : 11 juin 2007

- Licence : 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.

Sommaire

- I. Présentation de l'exemple

- II. La syntaxe

- III. Exemple : La liste des produits de base

- IV. La récursivité maitrisée

- V. Conclusion

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.

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ée 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 deuxième 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

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 nouvelles exécutions 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

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 tous 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éments, 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

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é. À 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

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

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 dû 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.