IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Procédure récursive de suppression d'arborescence


Sujet :

Développement SQL Server

  1. #1
    Membre régulier
    Femme Profil pro
    Etudiante en Développement
    Inscrit en
    Avril 2012
    Messages
    106
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations professionnelles :
    Activité : Etudiante en Développement
    Secteur : Industrie

    Informations forums :
    Inscription : Avril 2012
    Messages : 106
    Points : 119
    Points
    119
    Par défaut Procédure récursive de suppression d'arborescence
    Bonjour,

    Voici mon problème:

    j'ai dans ma base de données une table qui crée une arborescence, je gère ce lien avec un champ idParent, qui contient null si la Famille est au sommet de l'arborescence, qui contient l'id de son supérieur dans le cas contraire.

    Voici le script de création de cette table :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TABLE Family (
    	id numeric IDENTITY,
    	shortName varchar(30),
    	fullName varchar(80),
    	[GroupofFamily.id] numeric,
    	[ColorofFamily.id] numeric,
    	idParent numeric,
    	PRIMARY KEY (id),
    	FOREIGN KEY ([GroupofFamily.id]) REFERENCES GroupofFamily(id),
    	FOREIGN KEY ([ColorofFamily.id]) REFERENCES ColorofFamily(id)
    )
    Cette famille a un lien avec avec deux autres tables, que j'ai initialisé avec des valeurs bidons pour mes tests.

    J'ai donc rempli cette table de cette manière :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    insert into Family (shortName, fullName, [GroupofFamily.id], [ColorofFamily.id], idParent) values ('1','1',1,1,null),
    ('1.1','1.1',1,1,1),
    ('1.2','1.2',1,1,1),
    ('1.3','1.3',1,1,1),
    ('1.2.1','1.2.1',1,1,3),
    ('1.2.2','1.2.2',1,1,3),
    ('1.2.3','1.2.3',1,1,3),
    ('2','2',1,1,null),
    ('1.2.3.1','1.2.3.1',1,1,7),
    ('1.2.3.2','1.2.3.2',1,1,7),
    ('1.2.3.1.1','1.2.3.1.1',1,1,9),
    ('1.2.3.1.2','1.2.3.1.2',1,1,9);
    ce qui donne :
    1
    1.1
    1.2
    1.2.1
    1.2.2
    1.2.3
    1.2.3.1
    1.2.3.1.1
    1.2.3.1.2
    1.2.3.2
    2
    J'ai tenté de créer une procédure pour supprimer proprement mon arborescence, sans laisser de branche orpheline :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    CREATE PROCEDURE Family_Delete @idToDelete numeric
    AS
    BEGIN
    	DECLARE @idBuf numeric
    	DECLARE @idToScan numeric
    	DECLARE id_cursor CURSOR LOCAL FOR (select id from Family where idParent = @idToDelete)
     
    	open id_cursor
    	fetch next from id_cursor into @idToScan
     
    	while @@FETCH_STATUS = 0
    	begin
    		set @idBuf = (select id from Family where idParent = @idToScan)
    		if @idBuf is null
    		begin
    			set @idBuf = (select idParent from Family where id = @idToScan)
    			delete from Family where id = @idToScan
    			if @idBuf is not null 
    			begin
    				execute Family_Delete @idBuf
    			end
    		end
    		else
    		begin
    			execute Family_Delete @idToScan
    		end
     
    		fetch next from id_cursor into @idToScan
    	end
    	delete from Family where id = @idToDelete
     
    	close id_cursor
    	DEALLOCATE id_cursor
    END
    go
    Problème :
    J'essaye de supprimer la branche 1.2 :
    lorsque mon arborescence dépasse le stade 1.2.3, cela m'affiche ce message d'erreur :
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    Dans les autres cas je n'ai pas d'erreur et la suppression se fait sans problème.

    Ce message concerne la ligne où je déclare mon curseur.
    Je ne comprend pas pourquoi j'ai ce message puisque je fais appel à Family_Delete uniquement par le biais de @idToScan ou idBuf.
    Or @idToScan ne contient qu'une seule valeur puisque que son contenu vient de mon curseur, et @idBuf ne peut contenir *normalement* qu'un résultat puisque la condition est "where id = ..." et que le champ id est l'identifiant de ma table (donc unique)

    Je ne vois pas où est mon erreur

    Voici l'état de ma table avant exécution :


    et après :

  2. #2
    Membre chevronné

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2007
    Messages
    1 216
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 40
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie Pharmaceutique

    Informations forums :
    Inscription : Août 2007
    Messages : 1 216
    Points : 1 758
    Points
    1 758
    Par défaut
    Bonjour,

    Quelle version de SQL Server utilisez vous ?

  3. #3
    Membre régulier
    Femme Profil pro
    Etudiante en Développement
    Inscrit en
    Avril 2012
    Messages
    106
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations professionnelles :
    Activité : Etudiante en Développement
    Secteur : Industrie

    Informations forums :
    Inscription : Avril 2012
    Messages : 106
    Points : 119
    Points
    119
    Par défaut
    je travaille sur SQL Server 2008,
    Microsoft SQL Server Management Studio v10.0.1600.22

  4. #4
    Membre régulier
    Femme Profil pro
    Etudiante en Développement
    Inscrit en
    Avril 2012
    Messages
    106
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations professionnelles :
    Activité : Etudiante en Développement
    Secteur : Industrie

    Informations forums :
    Inscription : Avril 2012
    Messages : 106
    Points : 119
    Points
    119
    Par défaut
    Après réfléxion avec un collègue, il y a un moyen qui marche sans problème et qui est beaucoup plus simple :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    CREATE PROCEDURE Family_Delete 
    @idToDelete numeric
    AS
    BEGIN
    	DECLARE @idToScan numeric
    	DECLARE id_cursor CURSOR LOCAL FOR select id from Family where idParent = @idToDelete
     
    	open id_cursor
    	fetch next from id_cursor into @idToScan
     
    	while @@FETCH_STATUS = 0
    	begin
    		delete from Family where id = @idToDelete
    		execute Family_Delete @idToScan
    		delete from Family where id = @idToScan
    		fetch next from id_cursor into @idToScan
    	end
    	delete from Family where id = @idToDelete
     
    	close id_cursor
    	DEALLOCATE id_cursor
    END
    go
    j'avais l'impression de "couper la branche sur laquelle je suis assise" si je ne récupérais pas l'idParent de la branche que je supprimais, mais en faisant comme ça ce n'est pas un problème

  5. #5
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

    Depuis SQL Server 2005, on peut faire cela en une seule requête avec une expression de table commune récursive :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    CREATE PROCEDURE Family_Delete 
    	@idToDelete numeric
    AS
    BEGIN
    	SET NOCOUNT ON	
     
    	;WITH
    		CTE AS
    		(
    				-- Recherche de la source de suppression
    				SELECT	id
    					, idParent
    					, 0 AS niveau
    				FROM	dbo.Family
    				WHERE	id = @idToDelete
    			UNION ALL
    				-- Recherche de tous les enfants par résursivité
    				SELECT		F.id
    						, F.idParent
    						, C.niveau + 1
    				FROM		CTE AS C
    				INNER JOIN	dbo.Family AS F
    							ON C.id = F.idParent
    		)
    	DELETE		FROM dbo.Family
    	FROM		dbo.Family AS F
    	INNER JOIN	CTE AS C
    				ON C.id = F.id
    END
    Ce sera forcément plus rapide qu'avec un curseur

    Vous pouvez lire l'article de SQLPro sur le sujet.
    En ce qui concerne les curseurs, vous pouvez lire le billet que j'ai publié là-dessus

    @++

  6. #6
    Membre régulier
    Femme Profil pro
    Etudiante en Développement
    Inscrit en
    Avril 2012
    Messages
    106
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations professionnelles :
    Activité : Etudiante en Développement
    Secteur : Industrie

    Informations forums :
    Inscription : Avril 2012
    Messages : 106
    Points : 119
    Points
    119
    Par défaut
    Merci beaucoup pour ces précisions ! Je débute en gestion de base de données et j'ignorais qu'on pouvait faire ça, c'est effectivement très pratique

  7. #7
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Merci

    Notez que vous pouvez supprimer la colonne niveau de la CTE

    @++

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Procédure récursive et pointeurs
    Par paulfr dans le forum Langage
    Réponses: 6
    Dernier message: 05/08/2008, 00h49
  2. Réponses: 2
    Dernier message: 29/05/2008, 11h53
  3. Procédure récursive nb max d'appel
    Par Fred29 dans le forum SQL
    Réponses: 1
    Dernier message: 08/05/2008, 09h53
  4. [VB] Organigramme d'une procédure récursive.
    Par jacma dans le forum VB 6 et antérieur
    Réponses: 3
    Dernier message: 06/11/2005, 11h53
  5. Procédure Récursives
    Par DocCoinCoin dans le forum Algorithmes et structures de données
    Réponses: 8
    Dernier message: 30/10/2002, 20h27

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo