Petit guide de Transact SQL (v2000)

Date de publication : 16/04/2004

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

Transact SQL, est une extension du dialecte SQL de SQL Server et constitue un langage procédural pour coder les procédures stockées et les triggers du SGBDR de Microsoft.
Le but de cet article est de présenter rapidement les concepts nécessaires à une bonne introduction à ce langage et ses possibilités.

1. Positionnement du langage

1.1. Historique

1.2. Utilisation

• dans les procédures stockées, pour les calculs portant sur des données internes à la base, sans considération de gestion d'IHM. Exemple pré calculs d'agrégats pour optimisation d'accès...
• dans les triggers, pour définir et étendre des règles portant sur le respect de l'intégrité des données. Exemple formatage de données, liens d'héritage exclusif entre tables filles...

1.3. Conclusion

2. Syntaxe

2.1. Identifiant

• ne peuvent dépasser 128 caractères.
• Ils doivent commencer par une lettre ou un "underscore".
• Les caractères spéciaux et le blanc ne sont pas admis.
• On se contentera d'utiliser les 37 caractères de base : ['A'..'Z', '0'..'9', '_']
• La casse n'a pas d'importance.

	SELECT CURRENT_TIMESTAMP as DateTime
	INTO #tempT
	
	SELECT * 
	FROM #tempT

	serveur.base_cible.utilisateur.objet

	SELECT * FROM MonServeur.MaBase.MonUser.MaTable

	base_cible..objet

	objet

2.2. Variables

bit, int, smallint, tinyint, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, timestamp, 
uniqueidentifier, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, image.

cursor

DECLARE @maChaine char(32)

SET @maChaine = 'toto'

• il n'existe pas de type "tableau" dans le langage Transact SQL. Cependant, une table temporaire suffit à un tel usage.
• l'ordre SQL SELECT peut aussi servir à assigner une ou plusieurs variable, mais dans ce dernier cas il faut veiller à ce que la réponse à la requête ne produise qu'une seule ligne

SELECT @NomTable = 'TABLES', @NombreLigne = count(*)
FROM INFORMATION_SCHEMA.TABLES

DECLARE @Colonne varchar(8000)
SET @Colonne = ''
SELECT @Colonne = @Colonne +COALESCE(TABLE_NAME + ', ', '')
FROM INFORMATION_SCHEMA.TABLES

2.3. Structures basiques

BEGIN 
...
END

IF [NOT] condition instruction [ELSE instruction]
IF [NOT] EXISTS(requête select) instruction [ELSE instruction]
WHILE condition instruction
GOTO etiquette
WAITFOR [DELAY | TIME] temps

BREAK 
CONTINUE

• il n'y a pas de THEN dans le IF du Transact SQL, ni de DO dans le WHILE
• une seule instruction est permise pour les structure IF et WHILE, sauf à mettre en place un bloc d'instructions à l'aide d'une structure BEGIN / END
• un branchement sur étiquette se précise avec en suffixant l'identifiant de l'étiquette avec un caractère deux-points ':'

/* recherche de tous les nombres premiers de 1 à 5000 */
/* version procédurale (itérations)                   */
-- création d'une table provisoire pour stockage des données
create table #n (n int)

declare @n integer, @i integer, @premier bit
set @n = 1
set nocount on
-- un nombre premier n'est divisible que par 1 et lui même
while @n < 5000
BEGIN
-- on pré suppose qu'il est premier
   set @premier = 1
   set @i = 2
   while @i < @n
   BEGIN
-- autrement dit, tout diviseur situé entre 2 et lui même moins un
-- fait que ce nombre n'est pas premier
      if (@n / @i) * @i = @n
         SET @premier = 0
      SET @i = @i + 1
   END
   if @premier = 1
      insert into #n VALUES (@n)
   SET @n = @n + 1
END

SELECT * FROM #n

/* recherche de tous les nombres premiers de 1 à 5000 */
/* version ensembliste (requêtes)                     */
 DECLARE @max int
 SET @max = 5000
 -- cet exemple utilise la logique ensembliste pour calculer tous les nombres entiers
 SET NOCOUNT ON
 -- table temporaire de stockage des entiers de 1 à 5000
 CREATE TABLE #n (n int)
 -- boucle d'insertion de 0 à 5000
 DECLARE @i int
 SET @i = 0 
 WHILE @i < @max
 BEGIN
    INSERT INTO #n VALUES (@i)
    SET @i = @i + 1
 END
 -- on prend tous les entiers de la table n moins les entiers de la table n pour
 -- lesquels le reste de la division entière (modulo) par un entier moindre donne 0
 -- NOTA l'opération MODULO se note % dans Transact SQL
 SELECT distinct n
 FROM #n
 WHERE n not in (SELECT distinct n1.n
                 FROM   #n n1
                        CROSS JOIN #n n2
                 WHERE n1.n % n2.n = 0
                   AND n2.n BETWEEN 2 AND n1.n - 1)
 ORDER BY n

2.4. Variable de retour

RETURN 1445

2.5. Variables "système"

2.6. Flags

CREATE TABLE T_CLIENT
(CLI_ID INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))

SET IDENTITY_INSERT T_CLIENT ON

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (325, 'DUPONT')
INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (987, 'MARTIN')

SET IDENTITY_INSERT T_CLIENT OFF

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (512, 'LEVY')
=> Serveur: Msg 544, Niveau 16, État 1, Ligne 1
Impossible d'insérer une valeur explicite dans la colonne identité de la table 'T_CLIENT' quand IDENTITY_INSERT 
est défini à OFF.

2.7. Batch et GO

• Certains ordres ne peuvent être passés simultanément dans le même lot. Par exemple la suppression d'un objet (DROP) et sa re création immédiatement après, (CREATE) est source de conflits.
• Le mot clef GO n'est valable que pour une exécution par lot (batch). Il n'est donc pas reconnu au sein d'une procédure stockée ni dans le code d'un trigger

2.8. Quelques fonctions de base

CREATE DATABASE NEW_MABASE
ON 
( NAME = 'NEW_MABASE_DB',
  FILENAME = 'C:\MaBase.DATA',
  SIZE = 100,
  MAXSIZE = 500,
  FILEGROWTH = 50 )
LOG ON
( NAME = 'NEW_MABASE_LOG',
  FILENAME = 'C:\MaBase.LOG',
  SIZE = 5,
  MAXSIZE = 25,
  FILEGROWTH = 5 )
GO

USE NEW_MABASE
GO

CREATE TABLE T_CLIENT
(CLI_ID  INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))
GO

DECLARE
       @TableName Varchar(128),     -- nom de la table passé en argument
       @SearchWord Varchar(32)      -- mot recherché

Declare @ColumnList varchar(1000)   -- liste des noms de colonnes dans 
                                    -- lesquels la recherche va s'effectuer 
Declare @SQL varchar(1200)          -- requête de recherche

-- obtention de la liste des colonnes pour la requête de recherche
SELECT @ColumnList = COALESCE(@ColumnList + ' + COALESCE(', 'COALESCE(') + column_name +', '''')'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = @TableName
  AND  DATA_TYPE LIKE '%char%'

PRINT 'INFO - @ColumnList value is : ' + @ColumnList -- juste pour voir !

-- assemblage du texte de la requête de recherche 
Set @SQL =    'SELECT * FROM '+ @TableName 
           + ' WHERE '        + @ColumnList 
           + ' LIKE ''%'      + @SearchWord +'%'''

PRINT 'INFO - @SQL value is : ' + @SQL               -- juste pour voir !

-- exécution de la requête de recherche
Exec (@SQL)

2.9. Commentaires

3. UDF : fonction utilisateur

3.1. Fonction renvoyant une valeur

CREATE  FUNCTION [ utilisateur. ] nom_fonction
    ( [ { @parametre1[AS] type [ = valeur_défaut ] } [ , @parametre2 ... ] ] )

RETURNS type_résultant

[ AS ]

BEGIN

    code

    RETURN valeur_résultante

END

CREATE FUNCTION FN_PAQUE (@AN INT)
      
RETURNS DATETIME

AS

BEGIN

-- Algorithme conçu par Claus Tøndering .
-- Copyright (C) 1998 by Claus Tondering.
-- E-mail: claus@tondering.dk.

   IF @AN IS NULL
      RETURN NULL

   DECLARE @G INT
   DECLARE @I INT
   DECLARE @J INT
   DECLARE @C INT
   DECLARE @H INT
   DECLARE @L INT
   DECLARE @JourPaque INT
   DECLARE @MoisPaque INT
   DECLARE @DimPaque DATETIME

   SET @G = @AN % 19
   SET @C = @AN / 100
   SET @H = (@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30
   SET @I = @H - (@H / 28) * (1 - (@H / 28) * (29 / (@H + 1)) * ((21 - @G) / 11))
   SET @J = (@AN + @AN / 4 + @I + 2 - @C + @C / 4) % 7

   SET @L = @I - @J
   SET @MoisPaque = 3 + (@L + 40) / 44
   SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)

   SET @DimPaque = CAST(CAST(@AN AS VARCHAR(4)) +
                   CASE
                      WHEN @MoisPaque < 10 THEN '0' + CAST(@MoisPaque AS CHAR(1))
                                           ELSE       CAST(@MoisPaque AS CHAR(2))
                   END +
                   CASE
                      WHEN @JourPaque < 10 THEN '0' + CAST(@JourPaque AS CHAR(1))
                                           ELSE       CAST(@JourPaque AS CHAR(2))
                   END
                   AS DATETIME)

   RETURN @DimPaque

END

SELECT dbo.FN_PAQUE (2004) AS PAQUE_2004

PAQUE_2004                                            
-----------------------
2004-04-11 00:00:00.000

3.2. Fonction renvoyant une table

CREATE FUNCTION FN_JOUR_SEMAINE ()
RETURNS TABLE
AS
RETURN (SELECT 1 AS N, 'Lundi' AS JOUR
        UNION
        SELECT 2 AS N, 'Mardi' AS JOUR
        UNION
        SELECT 3 AS N, 'Mercredi' AS JOUR
        UNION
        SELECT 4 AS N, 'Jeudi' AS JOUR
        UNION
        SELECT 5 AS N, 'Vendredi' AS JOUR
        UNION
        SELECT 2 AS N, 'Samedi' AS JOUR
        UNION
        SELECT 2 AS N, 'Dimanche' AS JOUR)

SELECT *
FROM dbo.FN_JOUR_SEMAINE ()

N           JOUR    
----------- --------
1           Lundi
2           Dimanche
2           Mardi
2           Samedi
3           Mercredi
4           Jeudi
5           Vendredi

CREATE  FUNCTION [ utilisateur. ] nom_fonction
    ( [ { @parametre1[AS] type [ = valeur_défaut ] } [ , @parametre2 ... ] ] )

RETURNS type_résultant TABLE < definition_de_table >

[ AS ]

BEGIN
    code

    RETURN

END

CREATE FUNCTION FN_ENTIERS (@MAXINT integer, @NATUREL bit = 0)
       RETURNS @integers TABLE
               (N int PRIMARY KEY NOT NULL)
AS
BEGIN

   DECLARE @N INT
   DECLARE @T TABLE (N int)
   SET @N = 0

-- insertion des 10 premiers chiffres de 0 à 9
   WHILE @N < 10
   BEGIN
      INSERT INTO @T VALUES (@N)
      SET @N = @N + 1
   END
   SET @N = @N -1

-- si @N est supérieur à 9, alors supprimer les valeurs en trop
   IF @N > @MAXINT
      DELETE FROM @T WHERE N > @MAXINT
   ELSE
      INSERT INTO @T     
      SELECT DISTINCT 1 * T1.N + 10 * T2.N +  + 100 * T3.N + 1000  * T4.N
      FROM   @T AS T1
             CROSS JOIN @T AS T2
             CROSS JOIN @T AS T3
             CROSS JOIN @T AS T4
      WHERE  1 * T1.N + 10 * T2.N +  + 100 * T3.N + 1000  * T4.N BETWEEN 10 AND @MAXINT
     

-- s'il s'agit d'entiers naturels, supprimer le zéro  
   IF @NATUREL = 1
      DELETE FROM @T WHERE N = 0

-- insertion dans la variable de retour
   INSERT INTO @integers
   SELECT DISTINCT N FROM @T

   RETURN

END

SELECT *
FROM  dbo.FN_ENTIERS (13, 1)

N           
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE VIEW V_DATEHEURE_COURANTE
AS
SELECT CURRENT_TIMESTAMP AS DHC

CREATE FUNCTION FN_DELTA_MONTH (@MaDate DATETIME)
   RETURNS INT
AS
BEGIN
   DECLARE @N INT

   SELECT @N = DATEDIFF(MONTH, @MaDate, DHC)
   FROM   V_DATEHEURE_COURANTE

   RETURN @N
END

SELECT dbo.FN_DELTA_MONTH('20020101')

4. Procédures stockées

• étendre les possibilités des requêtes, par exemple lorsqu'un seul ordre INSERT ou SELECT ne suffit plus
• faciliter la gestion de transactions
• permettre une exécution plus rapide et plus optimisée de calculs complexes ne portant que sur des données de la base

• 128 Mo de code
• 1024 paramètres en argument (y compris curseurs)

4.1. Entête de procédure

CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
       @TableName  Varchar(128),      -- nom de la table passé en argument
       @SearchWord Varchar(32)        -- mot recherché
AS
...

4.2. Paramètres, variables de retour et ensemble de données

CREATE PROCEDURE SP_SYS_DB_TRANSACTION 
                 @TRANS_NUM INTEGER,  -- numéro de la transaction concernée
                 @OK BIT = 0 OUTPUT   -- retour 0 OK, 1 problème
AS
...

DECLARE @RetourOK bit
EXEC(SP_SYS_DB_TRANSACTION 127, @RetourOK OUTPUT)
SELECT @RetourOK

CREATE PROCEDURE SEARCH_TEXT @resultTable CURSOR VARYING OUTPUT
AS

CREATE PROCEDURE SP_LISTE_CLIENT
                 @CLI_ID_DEBUT INTEGER, @CLI_ID_FIN INTEGER
AS

SELECT '-- début de liste client --' 
UNION  ALL
SELECT CLI_NOM 
FROM   T_CLIENT
WHERE  CLI_ID BETWEEN @CLI_ID_DEBUT AND @CLI_ID_FIN
UNION  ALL
SELECT '--  fin de liste client  --'

4.3. Gestion des erreurs

CREATE PROCEDURE SP_DELETE_CLIENT_RECURSIVE
                 @CLI_ID INTEGER

 /*---------------------------------------------------\
| recherche d'une occurrence de mot dans n'importe     |
| quelle colonne de type caractères d'une table donnée | 
|----------------------------------------------------- |
| Frédéric BROUARD - COMMUNICATIC SA - 2001-12-17      |
 \-------------------------------------------------- */

CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
       @TableName Varchar(128),     -- nom de la table passé en argument
       @SearchWord Varchar(32)      -- mot recherché
AS 

IF @TableName IS NULL OR @SearchWord IS NULL
RAISERROR ('Paramètres NULL impossible à traiter', 16, 1)
IF @@ERROR <> 0 GOTO LBL_ERROR

-- test d'existence de la table
IF NOT EXISTS(SELECT *
              FROM   INFORMATION_SCHEMA.tables 
              WHERE  TABLE_NAME = @TableName)
RAISERROR ('Références de table inconnue %s', 16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

Declare @ColumnList varchar(1000)   -- liste des noms de colonnes dans 
                                    -- lesquels la recherche va s'effectuer 
Declare @SQL varchar(1200)          -- requête de recherche

-- obtention de la liste des colonnes pour la requête de recherche
SELECT @ColumnList = COALESCE(@ColumnList + ' + COALESCE(', 'COALESCE(') + column_name +', '''')'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = @TableName
  AND  DATA_TYPE LIKE '%char%'

IF @ColumnList IS NULL
RAISERROR ('Aucune colonne de recherche trouvé dans la table %s',
           16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

PRINT 'INFO - @ColumnList value is : ' + @ColumnList

-- assemblage du texte de la requête de recherche 
Set @SQL = 
   'SELECT * FROM '+ @TableName 
 + ' WHERE '       + @ColumnList 
 + ' LIKE ''%'     + @SearchWord +'%'''

PRINT 'INFO - @SQL value is : ' + @SQL

-- exécution de la requête de recherche
Exec (@SQL)

RETURN

LBL_ERROR:
PRINT 'ERREUR LORS DE L''EXÉCUTION DE LA PROCÉDURE STOCKÉE SP_SEARCH_STRING_ANYFIELD'

4.4. Procédures stockées prédéfinies

• de piloter le paramétrage du serveur et de la base de données (respect des normes SQL 2, maximum d'utilisateurs…) - exemple : sp_configure
• de définir les objets de la base (table, index, contraintes, vues…) - exemple : sp_addtype
• d'administrer le serveur, les bases, les accès (rôles, utilisateurs, login…) - exemple : sp_droplogin
• d'obtenir de l'aide et de la documentation (aide en ligne, explications) - exemple : sp_helpsort
• de gérer les agents (alertes et batch) - exemple : sp_add_job
• d'assurer les réplications (entre bases et serveurs) - exemple : sp_deleteMergeConflictRows
• de monitorer le fonctionnement de SQL Server (tracer l'exécution) - exemple : xp_sqlTrace
• ...

-- ajout du type TT_MONTH en entier obligatoire
SP_addType TT_MONTH, 'INTEGER', 'NOT NULL'

-- creation de la règle R_CHECK_MONTH vérifiant 
-- que la valeur soit comprise entre 1 et 12
CREATE RULE R_CHECK_MONTH AS @value BETWEEN 1 AND 12

-- liaison de la règle au type
SP_bindRule R_CHECK_MONTH, TT_MONTH

4.5. Verrouillage

• soit se débarrasser des contraintes de verrouillage, notamment pour faire du "dirty read" (lecture sale)
• soit poser des verrous plus contraignant pour s'assurer du traitement

SELECT *
FROM   T_CLIENT C WITH (NOLOCK)
       JOIN T_FACTURE F WITH (NOLOCK) 
            ON C.CLI_ID = F.CLI_ID

UPDATE T_CLIENT WITH (TABLOCK)
SET CLI_NOM = REPLACE(CLI_NOM, ' ', '-')

... WITH (PAGLOCK HOLDLOCK) ...

4.6. Gestion de transactions

BEGIN TRANSACTION

-- reste t-il de la place pour ce vol ?
IF NOT EXISTS(SELECT *
              FROM   T_VOL_AVION
              WHERE  VOL_REF = 'AF714'
                AND  VOL_PLACE_LIBRE > 3)
THEN
BEGIN
   ROLLBACK TRANSACTION
   RETURN
END

-- décompte des places vendues
UPDATE T_VOL_AVION
SET    VOL_PLACE_LIBRE  = VOL_PLACE_LIBRE - 3
WHERE  VOL_REF = 'AF714'
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
   GOTO ROLLBACK_ON_ERROR

-- génération d'une réservation
INSERT INTO T_RESERVATION (VOL_REF, CLI_ID, PLACES)
VALUES ('AF714', 123, 3)
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
   GOTO ROLLBACK_ON_ERROR

-- validation de l'ensemble
COMMIT TRANSACTION
RETURN

-- si branchement à l'étiquette d'erreur alors ROLLBACK
ROLLBACK_ON_ERROR:
   ROLLBACK TRANSACTION

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

CREATE TABLE T_DEVELOPPEMENT_DEV 
(DEV_ID           INTEGER IDENTITY (1, 1) NOT NULL ,  -- identifiant 
 DEV_NIVEAU       SMALLINT                NOT NULL ,  -- niveau du noeud
 DEV_LIBELLE      CHAR (32)               NOT NULL ,  -- libellé
 DEV_BORNE_GAUCHE INTEGER                 NOT NULL ,  -- borne gauche de l'arbre
 DEV_BORNE_DROITE INTEGER                 NOT NULL)   -- borne droite de l'arbre

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FILS_AINE
       @id_pere integer,
       @libelle varchar(32),
       @id_ins  integer OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion sous père dans l'arbre du développement
-- paramètres : id_pere, libelle

DECLARE @bg_pere integer 
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- démarrage transaction 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_FILS_AINE

-- vérification de l'existence du père
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   RETURN
END

-- recherche du bg_pere et calcul du niveau inférieur
SELECT @bg_pere = DEV_BORNE_GAUCHE, @niveau = DEV_NIVEAU + 1
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- décalage pour insertion borne droite
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2 
WHERE DEV_BORNE_DROITE > @bg_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

-- décalage pour insertion borne gauche
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE > @bg_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bg_pere + 1, @bg_pere + 2) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_FILS_IANE

SELECT @id_ins  -- renvoie de l'identifiant de l'élément inséré.

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FILS_CADET
       @id_pere integer,
       @libelle varchar(32),
       @id_ins  integer      OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion sous père dans l'arbre du développement
-- paramètres : id_pere, libelle

DECLARE @bd_pere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- on gère une transaction qui remanie les bornes de l'arbre.
-- il ne faut pas être 'dérangé' par d'autres utilisateurs concurrents pendant cette manoeuvre
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_FILS_CADET

-- vérification de l'existence du père
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   RETURN
END

-- recherche du bg_pere et calcul du niveau inférieur
SELECT @bd_pere = DEV_BORNE_DROITE, @niveau = DEV_NIVEAU + 1
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- décalage pour insertion borne droite
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2 
WHERE DEV_BORNE_DROITE >= @bd_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1
   RETURN
END

-- décalage pour insertion borne gauche
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE > @bd_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bd_pere , @bd_pere + 1)

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1 as ID
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_SON

SELECT @id_ins

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FRERE_DROIT
       @id_frere integer,
       @libelle  varchar(32),
       @id_ins   integer       OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion latérale à droite du frère
-- paramètres : id_frere, libelle

DECLARE @bd_frere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_RIGHT

-- vérifie de l'existence du frère
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_RIGHT
   RETURN
END

-- recherche du bd_frere et du niveau
SELECT @bd_frere = DEV_BORNE_DROITE, @niveau = DEV_NIVEAU 
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- décalage borne gauche pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE  DEV_BORNE_GAUCHE > @bd_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

-- décalage borne droite pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2
WHERE DEV_BORNE_DROITE > @bd_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bd_frere + 1, @bd_frere+2) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_RIGHT

SELECT @id_ins

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FRERE_GAUCHE
       @id_frere integer,
       @libelle  varchar(32),
       @id_ins   integer OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion latérale à gauche du frère
-- paramètres : id_frere, libelle

DECLARE @bg_frere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_LEFT

-- vérification de l'existence du frère
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   RETURN
END

-- recherche du bg_frere
SELECT @bg_frere = DEV_BORNE_GAUCHE, @niveau = DEV_NIVEAU 
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- décalage borne gauche pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET   DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE >= @bg_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   SELECT -1
   RETURN
END

-- décalage borne droite pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2
WHERE DEV_BORNE_DROITE > @bg_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bg_frere, @bg_frere+1) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_LEFT

SELECT @id_ins

CREATE PROCEDURE SP_DEV_SUPPRESSION
       @id_element integer,
       @recursif   bit
AS

-- Frédéric BROUARD 20/11/2001
-- suppression d'un élément (@recursif = 0) ou d'un sous arbre (@recursif = 1)
-- paramètres : id_element, @recursif

DECLARE @OK integer
DECLARE @bg_element integer
DECLARE @bd_element integer
DECLARE @intervalle integer
 

SET NOCOUNT ON

-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION DELETE_TREE

-- vérifie de l'existence de l'élément
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_element

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION DELETE_TREE
   RETURN
END
 

-- recherche des bd_element et bd_element
SELECT @bd_element = DEV_BORNE_DROITE, @bg_element = DEV_BORNE_GAUCHE
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_element

IF @recursif = 0
BEGIN
-- suppression de l'élément 
   DELETE FROM T_DEVELOPPEMENT_DEV
   WHERE  DEV_ID = @id_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits et gauches des éléments du sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET DEV_BORNE_DROITE = DEV_BORNE_DROITE - 1,  DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - 1
   WHERE DEV_BORNE_GAUCHE > @bg_element AND DEV_BORNE_DROITE < @bd_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords gauches des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - 2
   WHERE  DEV_BORNE_GAUCHE > @bg_element 

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_DROITE = DEV_BORNE_DROITE - 2
   WHERE  DEV_BORNE_DROITE < @bg_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END
END

IF @recursif = 1
BEGIN
-- suppression des éléments du sous arbre
   DELETE FROM T_DEVELOPPEMENT_DEV
   WHERE  DEV_BORNE_GAUCHE >= @bg_element AND DEV_BORNE_DROITE <= @bd_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- calcul de l'intervalle de décallage
   SET @intervalle = @bd_element - @bg_element + 1

-- décalage des bords gauches des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - @intervalle
   WHERE  DEV_BORNE_GAUCHE > @bg_element 

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_DROITE = DEV_BORNE_DROITE - @intervalle
   WHERE  DEV_BORNE_DROITE < @bg_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

END

COMMIT TRANSACTION  DELETE_TREE

4.7. Les curseurs

-- déclaration des variables de colonnes pour le curseur
DECLARE @Col1 Type1, @Col2 Type2, @Col3, Type3...

-- declaration du curseur
DECLARE MyCursor CURSOR 
FOR 
   SELECT COL1, COL2, COL3 …
   FROM MyTable

-- ouverture du curseur
OPEN MyCursor

-- lecture du premier enregistrement
FETCH MyCursor INTO @Col1, @Col2, @Col3...

-- boucle de traitement
WHILE @@fetch_Status = 0
BEGIN
   traitement
-- lecture de l'enregistrement suivant
   FETCH MyCursor INTO @Col1, @Col2, @Col3...
END

-- fermeture du curseur
CLOSE myCursor

-- libération de la mémoire
DEALLOCATE myCursor

DECLARE nom_curseur [INSENSITIVE] [SCROLL] CURSOR 
FOR requête_select
FOR {READ ONLY | UPDATE [OF colonne1 [, colonne2...]]}]

DECLARE no