Dans cet article sur les arbres intervallaires, je n’ai pas mentionné comment déplacer un sous arbre modélisé par intervalle. Voici comment procéder…
Cette procédure exemple (sous forme de primitive) permet de déplacer un sous arbre en le faisant devenir un fils ainé ou cadet, un grand frère ou un petit frère et même un père par rapport à l’élément ciblé.
/******************************************************************************
-- création d'une procédure de déplacement d'un sous arbre
******************************************************************************/
CREATE PROCEDURE dbo.P_MOVE_TRE @id int, -- Ident de l'élément déplacé
@id_parent int, -- Ancêtre de destination
@mode char(2), -- FG : Grand Frère, PF : Petit Frère, FA : Fils ainé, FC : Fils cadet, P : Père, etc
@recurs bit -- La descendance est déplacé aussi
AS
/******************************************************************************
* PROCÉDURE DÉPLACEMENT D'UN SOUS ARBRE DANS UN ARBRE MODÉLISÉ PAR INTERVALLE *
*******************************************************************************
* Frédéric Brouard - SQLpro - http://www.sqlspot.com - 2004-06-10 *
*******************************************************************************
* PARAMÈTRES : *
* @id clef de l'élément à déplacer (racine du sous arbre) *
* @id_parent parent du point de déplacement (ancrage) *
* @mode parentèle au point d'ancrage. Valeurs possibles : *
* GF : Frère (grand) *
* PF : Frère (petit) *
* FA : Fils ainé *
* FC : Fils cadet *
* P : Père *
* @recurs si 1 déplacement de tout le sous arbre, *
* si 0 déplacement de l'élément seul *
******************************************************************************/
DECLARE @OK int,
@bdmax int, -- limite supérieure droite de l'arborescence
@deltab int, -- Delta de borne
@bgd int, -- Infos sur le déplacé
@bdd int,
@nivd int,
@bgp int, -- Infos sur le parent
@bdp int,
@nivp int
-- Mode silencieux
SET NOCOUNT ON;
-- contrôle d'exécution
IF @mode IS NULL
BEGIN
RAISERROR ('Déplacement impossible sans mode ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
IF @recurs IS NULL
BEGIN
RAISERROR ('Déplacement impossible sans type ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
IF @id IS NULL
BEGIN
RAISERROR ('Déplacement impossible sans précision de l''élément ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
IF @id_parent IS NULL
BEGIN
RAISERROR ('Déplacement impossible sans précision du destinataire ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
IF @id = @id_parent
BEGIN
RAISERROR ('Déplacement impossible , origine et destination identique ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
SET @mode = UPPER(@mode)
IF NOT( @mode = 'GF' OR @mode = 'PF' OR @mode = 'FC' OR @mode = 'FA' ) -- OR @mode = 'P' -- PBT: à vérifier !
BEGIN
RAISERROR ('Déplacement impossible, mode inconnu ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
RETURN
END
-- démarrage transaction -------------------
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION MOVE_NMC
-- L'élément existe toujours ?
SELECT @OK = count(*) FROM dbo.T_NOMENCLATURE_PIECES_NMC WHERE NMC_ID = @id
IF @OK IS NULL OR @OK = 0
BEGIN
RAISERROR ('Déplacement impossible, l''élément n''existe pas ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
GOTO LBL_ERROR
RETURN
END
-- Le parent existe toujours ?
SELECT @OK = count(*) FROM dbo.T_NOMENCLATURE_PIECES_NMC WHERE NMC_ID = @id_parent
IF @OK IS NULL OR @OK = 0
BEGIN
RAISERROR ('Déplacement impossible, le parent n''existe pas ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
GOTO LBL_ERROR
RETURN
END
-- On récupère la borne supérieure max de l'ensemble
SELECT @bdmax = max(NMC_BD) FROM dbo.T_NOMENCLATURE_PIECES_NMC
IF @bdmax IS NULL OR @bdmax = 0
BEGIN
RAISERROR ('Déplacement impossible, la borne BD est nulle ! (TABLE dbo.T_NOMENCLATURE_PIECES_NMC)', 16, 1)
GOTO LBL_ERROR
RETURN
END
-- On a un élément : on récupère ses caractéristiques
SELECT @bgd = NMC_BG, @bdd = NMC_BD, @nivd = NMC_NIVEAU
FROM dbo.T_NOMENCLATURE_PIECES_NMC
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
SET @deltab = @bdmax + 1 - @bgd
-- DEBUT de l'opération proprement dite...
-- Opération récursive ?
IF @recurs = 0
BEGIN
-- Seul l'élément doit être déplacé
-- déplacement de l'élément seul en fin de liste
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab,
NMC_BD = NMC_BD + @deltab
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- remontée de ses descendants dans la hiérarchie
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - 1,
NMC_BD = NMC_BD - 1,
NMC_NIVEAU = NMC_NIVEAU - 1
WHERE NMC_BG > @bgd AND NMC_BD < @bdd
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- rééquilibrage de l'arbre
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - 2
WHERE NMC_BG > @bdd AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
--
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD - 2
WHERE NMC_BD > @bdd AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Mise en place de l'élement deplacé
-- On récupère d'abord les caractéristiques du parent
SELECT @bgp = NMC_BG, @bdp = NMC_BD, @nivp = NMC_NIVEAU
FROM dbo.T_NOMENCLATURE_PIECES_NMC
WHERE NMC_ID = @id_parent
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Grand frère
IF @mode = 'GF'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + 2
WHERE NMC_BD >= @bdp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + 2
WHERE NMC_BG >= @bgp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = @bgp,
NMC_BD = @bgp + 1,
NMC_NIVEAU = @nivp
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Petit Frère
IF @mode = 'PF'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + 2
WHERE NMC_BD > @bdp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + 2
WHERE NMC_BG > @bdp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = @bdp + 1,
NMC_BD = @bdp + 2,
NMC_NIVEAU = @nivp
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Fils cadet
IF @mode = 'FC'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + 2
WHERE NMC_BD >= @bdp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + 2
WHERE NMC_BG > @bdp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = @bdp,
NMC_BD = @bdp + 1,
NMC_NIVEAU = @nivp + 1
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Fils ainé
IF @mode = 'FA'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + 2
WHERE NMC_BD > @bgp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + 2
WHERE NMC_BG > @bgp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = @bgp + 1,
NMC_BD = @bgp + 2,
NMC_NIVEAU = @nivp + 1
WHERE NMC_ID = @id
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
END
ELSE
BEGIN
-- L'élément et sa descendance doivent être déplacés
-- déplacement de l'élément et de sa descendance en fin de liste
-- Le niveau sera réactualisé ultérieurement
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab,
NMC_BD = NMC_BD + @deltab,
NMC_NIVEAU = NMC_NIVEAU - @nivd
WHERE NMC_BG >= @bgd AND NMC_BD <= @bdd
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- rétablissement des bornes
-- Calcul du Delta
SET @deltab = @bdd - @bgd + 1
--
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD - @deltab
WHERE NMC_BD > @bdd AND NMC_BG <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
--
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - @deltab
WHERE NMC_BG > @bdd AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- On récupère d'abord les caractéristiques du parent
SELECT @bgp = NMC_BG, @bdp = NMC_BD, @nivp = NMC_NIVEAU
FROM dbo.T_NOMENCLATURE_PIECES_NMC
WHERE NMC_ID = @id_parent
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Mise en place de l'élement deplacé
-- Grand frère
IF @mode = 'GF'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + @deltab
WHERE NMC_BD > @bgp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab
WHERE NMC_BG >= @bgp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Recadrage de la liste d'éléments déplacés
SET @deltab = @bdmax - @bgp + 1
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - @deltab,
NMC_BD = NMC_BD - @deltab,
NMC_NIVEAU = NMC_NIVEAU + @nivp
WHERE NMC_BG > @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Petit Frère
IF @mode = 'PF'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + @deltab
WHERE NMC_BD > @bdp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab
WHERE NMC_BG > @bdp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Recadrage de la liste d'éléments déplacés
SET @deltab = @bdmax - @bdp
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - @deltab,
NMC_BD = NMC_BD - @deltab,
NMC_NIVEAU = NMC_NIVEAU + @nivp
WHERE NMC_BG > @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Fils cadet
IF @mode = 'FC'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + @deltab
WHERE NMC_BD >= @bdp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab
WHERE NMC_BG > @bdp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Recadrage de la liste d'éléments déplacés
SET @deltab = @bdmax - @bdp + 1
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - @deltab,
NMC_BD = NMC_BD - @deltab,
NMC_NIVEAU = NMC_NIVEAU + @nivp + 1
WHERE NMC_BG > @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
-- Fils ainé
IF @mode = 'FA'
BEGIN
-- Limite sup.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BD = NMC_BD + @deltab
WHERE NMC_BD > @bgp AND NMC_BD <= @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Limite inf.
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG + @deltab
WHERE NMC_BG > @bgp AND NMC_BG < @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
-- Recadrage de la liste d'éléments déplacés
SET @deltab = @bdmax - @bgp
UPDATE dbo.T_NOMENCLATURE_PIECES_NMC
SET NMC_BG = NMC_BG - @deltab,
NMC_BD = NMC_BD - @deltab,
NMC_NIVEAU = NMC_NIVEAU + @nivp + 1
WHERE NMC_BG > @bdmax
IF @@ERROR <> 0
BEGIN
GOTO LBL_ERROR
RETURN
END
END
END
COMMIT TRANSACTION MOVE_NMC
GOTO LBL_FINAL
RETURN
LBL_ERROR:
ROLLBACK TRANSACTION MOVE_NMC
LBL_FINAL:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
Cette procédure opère dans la table décrite ci dessous :
CREATE TABLE dbo.dbo.T_NOMENCLATURE_PIECES_NMC
( NMC_ID T_N_ID IDENTITY NOT NULL PRIMARY KEY,
NMC_BG T_N_INT_POS NOT NULL ,
NMC_BD T_N_INT_POS NOT NULL ,
NMC_NIVEAU T_N_INT NOT NULL ,
...
)
Pour l’adapter à votre table spécifique, il suffit de remplacer les noms de la tables et des différentes colonnes, afin de l’adpater à votre cas.
Exemple d’éxécution :
EXEC dbo.P_MOVE_TRE 45, 108, 'FA', 1
Bon amusement !
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *
Super ! Vraiment bien le mode l’arborescence intervallaire dynamique sur l’insertion et la suppression !! Y a t-il une version de P_MOVE_TRE en dynamique ?