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 * * * * *
Salut,
Merci pour le partage de ces procédures ! N’ayant pas trouvé la procédure MOVE en générique, j’essaye de la modifier de sorte qu’elle le soit.