Arborescence en mode intervallaire : procédures génériques d’insertion et de suppression

Voici deux procédures pour insérer et supprimer dans différents modes (frère, père….) des éléments dans une arborescence modélisée par intervalle.

Pour le mode intervallaire de gestion des arborescences, voir cet article : http://sqlpro.developpez.com/cours/arborescence/
Dans ces procédures nous avons supposé que les noms des tables et les noms des colonnes respectait la norme que nous nous sommes fixés, c’est à dire :

  • Le nom de toute table se termine par un trigramme précédé par un blanc souligné.
  • Le trigramme de la table est repris en préfixe pour toute colonne et un blanc souligné le suit.
  • Les colonnes borne droite et gauche se nomment BG et BD, précédées par leur trigramme et un blanc souligné.

Insertion générique :


CREATE PROCEDURE dbo.P_I_TREE_GENERIC
       @SCHEMA    VARCHAR(128) = 'dbo',  -- nom du schema de la table (si NULL => dbo).  
       @TABLE     VARCHAR(128),          -- nom de la table
       @KEYCOL    VARCHAR(130),          -- nom de la colonne clef dans la table
       @NIVCOL    VARCHAR(130),          -- nom de la colonne niveau dans la table
       @MODE      CHAR(2),               -- mode d'insertion (FA : Fils Ainé, FC : Fils Cadet, GF : Grand frère, PF : Petit Frère, P : Père)
       @PARENTID  INT,                   -- valeur de l'ID parent
       @DATACOLS  VARCHAR(8000),         -- liste des colonnes de données
       @DATAVALS  VARCHAR(max),          -- valeur des données correspondantes à la liste  
       @NEW_ID    INT OUTPUT             -- id en retour d'insertion
AS
/******************************************************************************
*   Frédéric Brouard  -  SQL SPOT  -  http://www.sqlspot.com  -  2009-10-30   *
*******************************************************************************
* Insertion dans un arbre modélisé par intervalle.                            *
* Procédure générique utlisant du code SQL dynamique                          *
* NOTA : susceptible d'injection de code SQL => vérifier les paramètres au    *
*        niveau de l'interface                                                *
*******************************************************************************
* Références : http://sqlpro.developpez.com/cours/arborescence/               *
******************************************************************************/
 
SET NOCOUNT ON;
 
-- vérification des paramètres
IF @TABLE IS NULL  
BEGIN
   RAISERROR('Nom de table omis (paramètre @TABLE). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF @KEYCOL IS NULL  
BEGIN
   RAISERROR('Nom de la colonne clef omis (paramètre @KEYCOL). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF @NIVCOL IS NULL  
BEGIN
   RAISERROR('Nom de la colonne niveau omis (paramètre @NIVCOL). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF (@DATACOLS IS NULL AND @DATAVALS IS NOT NULL) OR (@DATACOLS IS NOT NULL AND @DATAVALS IS NULL)
BEGIN
   RAISERROR('Incohérence entre les paramètres @DATACOLS (liste des colonnes) et @DATAVALS (liste des valeurs). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.TABLES
               WHERE  TABLE_SCHEMA = @SCHEMA
                 AND  TABLE_NAME   = @TABLE)
BEGIN
   RAISERROR('Table inconnue (paramètres @SCHEMA et @TABLE, valeurs %s.%s). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @SCHEMA, @TABLE)
   RETURN;
END
IF NOT EXISTS(SELECT *
              FROM   INFORMATION_SCHEMA.COLUMNS
              WHERE  TABLE_SCHEMA = @SCHEMA
                AND  TABLE_NAME   = @TABLE
                AND  COLUMN_NAME  = @KEYCOL)
BEGIN
   RAISERROR('Colonne clef primaire inconnue (paramètre @KEYCOL, valeurs %s). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @KEYCOL)
   RETURN;
END
IF NOT EXISTS(SELECT *
              FROM   INFORMATION_SCHEMA.COLUMNS
              WHERE  TABLE_SCHEMA = @SCHEMA
                AND  TABLE_NAME   = @TABLE
                AND  COLUMN_NAME  = @NIVCOL)
BEGIN
   RAISERROR('Colonne niveau inconnue (paramètre @NIVCOL, valeurs %s). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @NIVCOL)
   RETURN;
END
 
 
-- variables locales
DECLARE @SQL NVARCHAR(max), @TRG CHAR(3), @T VARCHAR(257), @ERROR INT, @ROWCOUNT INT, @ROWCNT INT, @BGREF INT, @BDREF INT, @NIREF INT;
SELECT @TRG = SUBSTRING(@TABLE, LEN(@TABLE) - 2, 3), @T =  @SCHEMA +'.' + @TABLE, @MODE = UPPER(@MODE);
 
 
-------------------------------------------------------------------------------
-- cas particulier de l'insertion de la racine --------------------------------
-------------------------------------------------------------------------------
IF @MODE IS NULL AND @PARENTID IS NULL
BEGIN
 
   DECLARE @TOUT TABLE (I INT);
   
   -- démarrage transaction
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN TRANSACTION;
   
   SET @SQL = 'SET NOCOUNT ON;'
              + 'SET IDENTITY_INSERT '  + @T +' ON;'
              + 'INSERT INTO ' + @T  
              + ' (' + @KEYCOL +', ' + @NIVCOL +', '+ @TRG +'_BG, ' + @TRG +'_BD' + COALESCE(', '+  @DATACOLS, '') +') '
              + ' SELECT 0, 0, 1, 2' + COALESCE(', ' + @DATAVALS, '')  
              + ' WHERE NOT EXISTS( SELECT * FROM ' + @T +' );'
              + ' SELECT @@ROWCOUNT;'
              + 'SET IDENTITY_INSERT '  + @T +' OFF;'
   INSERT INTO @TOUT
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   SELECT @ROWCOUNT = I FROM @TOUT;
   IF @ROWCOUNT =0 GOTO LBL_ERROR_COUNT;  
   SET @NEW_ID = 0;
   GOTO LBL_OK;
   
END;
-------------------------------------------------------------------------------
 
-- ce n'est pas la racine, test du mode
IF @MODE NOT IN ('FA', 'FC', 'GF', 'PF', 'P ')
BEGIN
   RAISERROR('Mode inconnu (paramètres @MODE, valeurs %s). Les valeurs autorisées sont FA : Fils Ainé, FC : Fils Cadet, GF : Grand frère, PF : Petit Frère, P : Père. Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @MODE)
   RETURN;
END
 
-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
 
-------------------------------------------------------------------------------
-- insertion en mode FA (fils aîné) -------------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'FA'
BEGIN
   -- 1er étape : on décale les lignes
   SET @SQL = ' SET NOCOUNT ON; '
            + ' WITH T AS (SELECT ' + @TRG + '_BG AS C FROM ' + @T + ' '
            + '            WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) + ' ) '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG > (SELECT C FROM T) THEN ' + @TRG + '_BG + 2 ELSE ' + @TRG + '_BG END, '
            +           @TRG + '_BD = CASE WHEN ' + @TRG + '_BD > (SELECT C FROM T) THEN ' + @TRG + '_BD + 2 ELSE ' + @TRG + '_BD END; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   -- 2e étape on insère la ligne
   SET @SQL = ' INSERT INTO ' + @T + ' (' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL + COALESCE(', '+  @DATACOLS, '') +') '
            + ' SELECT T.' + @TRG + '_BG + 1, T.' + @TRG + '_BG + 2, T.' + @NIVCOL + ' + 1' + COALESCE(', ' + @DATAVALS, '')  
            + ' FROM ' + @T + ' AS T '
            + ' WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) +'; '
            + ' SELECT IDENT_CURRENT(''' + @T + ''');';
END;    
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
-- insertion en mode FC (fils cadet) ------------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'FC'
BEGIN
   -- 1er étape : on décale les lignes
   SET @SQL = ' SET NOCOUNT ON; '
            + ' WITH T AS (SELECT ' + @TRG + '_BD AS C FROM ' + @T + ' '
            + '            WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) + ' ) '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BD = CASE WHEN ' + @TRG + '_BD >= (SELECT C FROM T) THEN ' + @TRG + '_BD + 2 ELSE ' + @TRG + '_BD END, '
            +           @TRG + '_BG = CASE WHEN ' + @TRG + '_BG >= (SELECT C FROM T) THEN ' + @TRG + '_BG + 2 ELSE ' + @TRG + '_BG END; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   -- 2e étape on insère la ligne
   SET @SQL = ' INSERT INTO ' + @T + ' (' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL + COALESCE(', '+  @DATACOLS, '') + ') '
            + ' SELECT T.' + @TRG + '_BD - 2, T.' + @TRG + '_BD - 1, T.' + @NIVCOL + ' + 1' + COALESCE(', ' + @DATAVALS, '')  
            + ' FROM ' + @T + ' AS T '
            + ' WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) +'; '
            + ' SELECT IDENT_CURRENT(''' + @T + ''');';
END;    
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
-- insertion en mode GF (grand frère) -----------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'GF'
BEGIN
   -- 1er étape : on décale les lignes
   SET @SQL = ' SET NOCOUNT ON; '
            + ' WITH T AS (SELECT ' + @TRG + '_BG AS C FROM ' + @T + ' '
            + '            WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) + ' ) '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG >= (SELECT C FROM T) THEN ' + @TRG + '_BG + 2 ELSE ' + @TRG + '_BG END, '
            +           @TRG + '_BD = CASE WHEN ' + @TRG + '_BD >= (SELECT C FROM T) THEN ' + @TRG + '_BD + 2 ELSE ' + @TRG + '_BD END; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   -- 2e étape on insère la ligne
   SET @SQL = ' INSERT INTO ' + @T + ' (' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL + COALESCE(', '+  @DATACOLS, '') +') '
            + ' SELECT T.' + @TRG + '_BG - 2, T.' + @TRG + '_BG - 1, T.' + @NIVCOL + COALESCE(', ' + @DATAVALS, '')  
            + ' FROM ' + @T + ' AS T '
            + ' WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) +'; '
            + ' SELECT IDENT_CURRENT(''' + @T + ''');';
END;    
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
-- insertion en mode PF (petit frère) -----------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'PF'
BEGIN
   -- 1er étape : on décale les lignes
   SET @SQL = ' SET NOCOUNT ON; '
            + ' WITH T AS (SELECT ' + @TRG + '_BD AS C FROM ' + @T + ' '
            + '            WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) + ' ) '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG > (SELECT C FROM T) THEN ' + @TRG + '_BG + 2 ELSE ' + @TRG + '_BG END, '
            +           @TRG + '_BD = CASE WHEN ' + @TRG + '_BD > (SELECT C FROM T) THEN ' + @TRG + '_BD + 2 ELSE ' + @TRG + '_BD END; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
   -- 2e étape on insère la ligne
   SET @SQL = ' INSERT INTO ' + @T + ' (' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL + COALESCE(', '+  @DATACOLS, '') +') '
            + ' SELECT T.' + @TRG + '_BD + 1, T.' + @TRG + '_BD + 2, T.' + @NIVCOL + COALESCE(', ' + @DATAVALS, '')  
            + ' FROM ' + @T + ' AS T '
            + ' WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) +'; '
            + ' SELECT IDENT_CURRENT(''' + @T + ''');';
END;    
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
-- insertion en mode P (Père) -------------------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'P '
BEGIN
 
   -- récupération des bornes du père
   DECLARE @TDB TABLE (BG INT, BD INT, NV INT);
   SET @SQL = ' SET NOCOUNT ON;  SELECT ' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL
            + ' FROM ' + @T + ' WHERE ' + @KEYCOL + ' = ' + CAST(@PARENTID AS VARCHAR(32)) +';'
   INSERT INTO @TDB
   EXEC(@SQL);
   SELECT @BGREF =BG, @BDREF = BD, @NIREF = NV FROM @TDB;
   
   -- 1er étape : on décale les lignes à droite
   SET @SQL = ' SET NOCOUNT ON; '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG > ' + CAST(@BDREF AS VARCHAR(32)) + ' THEN ' + @TRG + '_BG + 2 ELSE ' + @TRG + '_BG END, '
            +           @TRG + '_BD = CASE WHEN ' + @TRG + '_BD > ' + CAST(@BDREF AS VARCHAR(32)) + ' THEN ' + @TRG + '_BD + 2 ELSE ' + @TRG + '_BD END; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
 
   -- 2e étape : on décale les lignes vers le bas
   SET @SQL = ' SET NOCOUNT ON; '
            + ' UPDATE ' + @T  
            + ' SET ' + @TRG + '_BG = ' + @TRG + '_BG + 1, '
            +           @TRG + '_BD = ' + @TRG + '_BD + 1, '
            +           @NIVCOL + ' = ' + @NIVCOL +' + 1 '
            + ' WHERE ' + @TRG + '_BG BETWEEN ' + CAST(@BGREF AS VARCHAR(32)) +  ' AND ' + CAST(@BDREF AS VARCHAR(32))  
            + ' AND   ' + @TRG + '_BD BETWEEN ' + CAST(@BGREF AS VARCHAR(32)) +  ' AND ' + CAST(@BDREF AS VARCHAR(32)) +'; '
   EXECUTE (@SQL);
   IF @@ERROR <> 0 GOTO LBL_ERROR;
 
   -- 3e étape on insère la ligne
   SET @SQL = ' SET NOCOUNT ON; '
            + ' INSERT INTO ' + @T + ' (' + @TRG + '_BG, ' + @TRG + '_BD, ' + @NIVCOL + COALESCE(', '+  @DATACOLS, '') +') '
            + ' VALUES (' + CAST(@BGREF AS VARCHAR(32)) + ' , ' + CAST(@BDREF + 2 AS VARCHAR(32)) + ', ' + CAST(@NIREF AS VARCHAR(32)) + COALESCE(', ' + @DATAVALS, '') + '); '
            + ' SELECT IDENT_CURRENT(''' + @T + ''');';
             
END;    
-------------------------------------------------------------------------------
 
INSERT INTO @TOUT
EXECUTE (@SQL);
IF @@ERROR <> 0 GOTO LBL_ERROR;
SELECT @NEW_ID = I FROM @TOUT;
 
-- succès
LBL_OK:
IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GOTO LBL_RESUME;
 
-- erreur SQL
LBL_ERROR:
IF @@TRANCOUNT > 1
   COMMIT TRANSACTION;
ELSE
   IF @@TRANCOUNT = 1
      ROLLBACK TRANSACTION;
RAISERROR('Erreur SQL N°%d dans la procédure : dbo.P_I_TREE_GENERIC .', 16, 1, @ERROR);
 
-- erreur aucune insertion
LBL_ERROR_COUNT:
IF @@TRANCOUNT > 1
   COMMIT TRANSACTION;
ELSE
   IF @@TRANCOUNT = 1
      ROLLBACK TRANSACTION;
RAISERROR('Erreur fonctionnelle dans la procédure : dbo.P_I_TREE_GENERIC . Aucune ligne insérée', 16, 1);
 
-- sortie de transaction
LBL_RESUME:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
GO

Suppression générique :


CREATE PROCEDURE dbo.P_D_TREE_GENERIC
       @SCHEMA    VARCHAR(128) = 'dbo',  -- nom du schema de la table (si NULL => dbo).  
       @TABLE     VARCHAR(128),          -- nom de la table
       @KEYCOL    VARCHAR(130),          -- nom de la colonne clef dans la table
       @NIVCOL    VARCHAR(130),          -- nom de la colonne niveau dans la table
       @MODE      CHAR(1),               -- mode de suppression (A : Arbre, E : élément)
       @TARGETID  INT                    -- valeur de l'ID de la cible de la suppression
AS
/******************************************************************************  
*   Frédéric Brouard  -  SQL SPOT  -  http://www.sqlspot.com  -  2009-10-30   *  
*******************************************************************************  
* Suppression dans un arbre modélisé par intervalle.                          *  
* Procédure générique utlisant du code SQL dynamique                          *  
* NOTA : susceptible d'injection de code SQL => vérifier les paramètres au    *  
*        niveau de l'interface                                                *  
*******************************************************************************  
* Références : http://sqlpro.developpez.com/cours/arborescence/               *  
******************************************************************************/  
 
SET NOCOUNT ON;
 
-- vérification des paramètres
IF @TABLE IS NULL  
BEGIN
   RAISERROR('Nom de table omis (paramètre @TABLE). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF @KEYCOL IS NULL  
BEGIN
   RAISERROR('Nom de la colonne clef omis (paramètre @KEYCOL).. Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF @NIVCOL IS NULL  
BEGIN
   RAISERROR('Nom de la colonne niveau omis (paramètre @NIVCOL). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1)
   RETURN;
END
IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.TABLES
               WHERE  TABLE_SCHEMA = @SCHEMA
                 AND  TABLE_NAME   = @TABLE)
BEGIN
   RAISERROR('Table inconnue (paramètres @SCHEMA et @TABLE, valeurs %s.%s). Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @SCHEMA, @TABLE)
   RETURN;
END
IF NOT EXISTS(SELECT *  
              FROM   INFORMATION_SCHEMA.COLUMNS  
              WHERE  TABLE_SCHEMA = @SCHEMA  
                AND  TABLE_NAME   = @TABLE  
                AND  COLUMN_NAME  = @KEYCOL)  
BEGIN  
   RAISERROR('Colonne clef primaire inconnue (paramètre @KEYCOL, valeurs %s). Suppression dans un arbre via la procédure générique : dbo.P_D_TREE_GENERIC . ', 16, 1, @KEYCOL)  
   RETURN;  
END  
IF NOT EXISTS(SELECT *  
              FROM   INFORMATION_SCHEMA.COLUMNS  
              WHERE  TABLE_SCHEMA = @SCHEMA  
                AND  TABLE_NAME   = @TABLE  
                AND  COLUMN_NAME  = @NIVCOL)  
BEGIN  
   RAISERROR('Colonne niveau inconnue (paramètre @NIVCOL, valeurs %s). Suppression dans un arbre via la procédure générique : dbo.P_D_TREE_GENERIC . ', 16, 1, @NIVCOL)  
   RETURN;  
END  
 
SET @MODE = UPPER(@MODE);
 
-- test du mode
IF @MODE NOT IN ('A', 'E')
BEGIN
   RAISERROR('Mode inconnu (paramètres @MODE, valeurs %s). Les valeurs autorisées sont FA : Fils Ainé, FC : Fils Cadet, GF : Grand frère, PF : Petit Frère, P : Père. Insertion dans un arbre via la procédure générique : dbo.P_I_TREE_GENERIC . ', 16, 1, @MODE)
   RETURN;
END
 
-- variables locales
DECLARE @SQL NVARCHAR(max), @TRG CHAR(3), @T VARCHAR(257), @ERROR INT, @ROWCOUNT INT, @ROWCNT INT, @BDP INT, @BGP INT, @DELTA INT;
SELECT @TRG = SUBSTRING(@TABLE, LEN(@TABLE) - 2, 3), @T =  @SCHEMA +'.' + @TABLE, @MODE = UPPER(@MODE);
DECLARE @TIN TABLE (G INT, D INT, DELTA AS D - G);
DECLARE @TOUT TABLE (I INT);
 
-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
 
-- récupération des bornes droites et gauches de l'élément cible
SET @SQL = ' SELECT ' + @TRG + '_BG, ' + @TRG + '_BD '
         + ' FROM   ' + @T
         + ' WHERE ' + @KEYCOL + ' = ' + CAST(@TARGETID AS VARCHAR(32));
 
INSERT INTO @TIN (G, D)
EXECUTE (@SQL);
IF @@ERROR <> 0 GOTO LBL_ERROR;
IF NOT EXISTS(SELECT * FROM @TIN) GOTO LBL_ERROR_COUNT;
SELECT @BGP = G, @BDP = D, @DELTA = DELTA FROM @TIN;
 
-------------------------------------------------------------------------------
-- suppression de tout l'arbre : mode A ---------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'A'
BEGIN
   -- 1ere phase : suppression de tous les éléments :
   SET @SQL = ' DELETE FROM ' + @T
            + ' WHERE ' + @TRG + '_BG >= ' + CAST(@BGP AS VARCHAR(32))
            + ' AND   ' + @TRG + '_BD <= ' + CAST(@BDP AS VARCHAR(32)) + '; '
             
   EXECUTE (@SQL);
   SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
   IF @ERROR <> 0 GOTO LBL_ERROR;
   IF @ROWCOUNT = 0 GOTO LBL_ERROR_COUNT;
   -- 2e phase, décalage des bornes
   SET @SQL = ' UPDATE ' + @T
            + ' SET    ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG > ' + CAST(@BDP AS VARCHAR(32)) + ' THEN ' + @TRG + '_BG - ' + CAST(@DELTA AS VARCHAR(32)) + ' -1 ELSE ' + @TRG + '_BG END, '
            +              @TRG + '_BD = CASE WHEN ' + @TRG + '_BD > ' + CAST(@BDP AS VARCHAR(32)) + ' THEN ' + @TRG + '_BD - ' + CAST(@DELTA AS VARCHAR(32)) + ' -1 ELSE ' + @TRG + '_BD END; '  
END
-------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
-- suppression de l'élément seul : mode E -------------------------------------
-------------------------------------------------------------------------------
IF @MODE = 'E'
BEGIN
 
   -- 1ere phase : suppression de l'élément seul :
   SET @SQL = ' DELETE FROM ' + @T
            + ' WHERE ' + @KEYCOL + ' = ' + CAST(@TARGETID AS VARCHAR(32)) + '; '      
   EXECUTE (@SQL);
   SELECT @ERROR = @@ERROR, @ROWCOUNT = @@ROWCOUNT;
   IF @ERROR <> 0 GOTO LBL_ERROR;
   IF @ROWCOUNT = 0 GOTO LBL_ERROR_COUNT;
   -- 2e phase, décalage des bornes et niveau de l'arbre sous l'élément supprimé
    SET @SQL = ' UPDATE ' + @T
             + ' SET ' + @TRG + '_BG = ' + @TRG + '_BG - 1, '
             +           @TRG + '_BD = ' + @TRG + '_BD - 1, '
             +           @NIVCOL + ' = ' + @NIVCOL + ' - 1 '
             + ' WHERE ' + @TRG + '_BG > ' + CAST(@BGP AS VARCHAR(32))
             + ' AND '   + @TRG + '_BD < ' + CAST(@BDP AS VARCHAR(32)) + '; '
   EXECUTE (@SQL);
   IF @ERROR <> 0 GOTO LBL_ERROR;
   -- 3e phase : décalage des bornes des éléments à droite
   SET @SQL = ' UPDATE ' + @T
            + ' SET      ' + @TRG + '_BG = CASE WHEN ' + @TRG + '_BG > ' + CAST(@BDP AS VARCHAR(32)) + ' THEN ' + @TRG + '_BG -2 ELSE ' + @TRG + '_BG END, '
            +                @TRG + '_BD = CASE WHEN ' + @TRG + '_BD > ' + CAST(@BDP AS VARCHAR(32)) + ' THEN ' + @TRG + '_BD -2 ELSE ' + @TRG + '_BD END; '
END
-------------------------------------------------------------------------------
   
EXECUTE (@SQL);
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
-- succès
LBL_OK:
IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GOTO LBL_RESUME;
 
-- erreur SQL
LBL_ERROR:
IF @@TRANCOUNT > 1
   COMMIT TRANSACTION;
ELSE
   IF @@TRANCOUNT = 1
      ROLLBACK TRANSACTION;
RAISERROR('Erreur SQL N°%d dans la procédure : dbo.P_D_TREE_GENERIC .', 16, 1, @ERROR);
 
-- erreur aucune insertion
LBL_ERROR_COUNT:
IF @@TRANCOUNT > 1
   COMMIT TRANSACTION;
ELSE
   IF @@TRANCOUNT = 1
      ROLLBACK TRANSACTION;
RAISERROR('Erreur fonctionnelle dans la procédure : dbo.P_D_TREE_GENERIC . Aucune ligne supprimée', 16, 1);
 
-- sortie de transaction
LBL_RESUME:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
GO

Exemple de test :


CREATE TABLE T_ARBO_XYZ
(XYZ_ID      INT NOT NULL IDENTITY PRIMARY KEY,
 XYZ_BG      INT UNIQUE,
 XYZ_BD      INT UNIQUE,
 XYZ_LEVEL   INT CHECK (XYZ_LEVEL >= 0),
 XYZ_VAL     VARCHAR(8),
 CONSTRAINT  UK_XYZ_TBG_TBD CHECK (XYZ_BG < XYZ_BD));
GO
 
DELETE FROM T_ARBO_XYZ;
DBCC CHECKIDENT ('dbo.T_ARBO_XYZ', RESEED, 0);
GO
 
-- test insertion racine :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', NULL, NULL, 'XYZ_VAL', '''papa''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- test insertion fils :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', 'FA', 0, 'XYZ_VAL', '''filsA''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- test insertion fils ainé :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', 'FA', 0, 'XYZ_VAL', '''fils+''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- test insertion fils cadet :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', 'FC', 0, 'XYZ_VAL', '''fils-''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- test insertion grand frère :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', 'GF', 3, 'XYZ_VAL', '''GFdef-''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- test insertion petit frère :
DECLARE @OUT INT
EXECUTE dbo.P_I_TREE_GENERIC 'dbo', 'T_ARBO_XYZ', 'XYZ_ID', 'XYZ_LEVEL', 'PF', 3, 'XYZ_VAL', '''pfdef-''', @OUT OUTPUT;
SELECT @OUT
GO
 
-- visu :
SELECT SPACE(XYZ_LEVEL) + XYZ_VAL, *
FROM   T_ARBO_XYZ
ORDER  BY XYZ_BG
GO


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 * * * * *

Une réflexion au sujet de « Arborescence en mode intervallaire : procédures génériques d’insertion et de suppression »

Laisser un commentaire