4. Notion de Transact-SQL

4.1.      Introduction au langage

Cette partie très importante de ce dossier vise à présenter le langage de développement de SQL server : le Transact-SQL, plus communément appelé T-SQL.

Bien entendu il ne s’agit ici que d’une introduction, en effet le sujet et vaste et on pourrait aisément s’y attarder sur plus de 500 pages. D’autres se sont d’ailleurs déjà attelés à cette tâche.

Par contre je vais essayer de vous donner toutes les bases qui vont vous permettre de vous lancer dans l’écriture de programmes T-SQL.

Tout d’abord pourquoi un langage de développement au sein d’une base de donnée ?

Tout simplement parce qu’au fil du temps le SQL a su montrer ses limites, il était donc temps de s’orienter vers un langage plus complet, et surtout procédural.

Le T-SQL permet ainsi d’écrire des programmes complet, capable de gérer des variables de boucles et autres structures de contrôle. En plus de cela il permet d’interagir avec les fonctions complexes du système permettant d’écrire de puissants scripts de maintenances et d’administration.

Rentons maintenant dans le vif du sujet…

4.2.      Convention de nomenclature

Tout comme dans la plus part des langage il existe des conventions de nomenclature :

• Les variables : elle commence toutes par le caractère @, ex : @toto
• Les variables systèmes : elle commence par une double @, ex : @@identity
• Les curseurs : il ne sont pas précédés d’un @
• Les procédures stockées : commencent par sp_
• Les procédures étendues : commencent par xp
• Les indexes : commencent par IX_
• Les clés primaires : commencent par PK
• Les clés étrangères : commencent par FK
• Le T-SQL n’est pas case sensitive pour tout se qui concerne les noms de variables et d’objets
• Toutes les règles du SQL sont applicables
• La mise en commentaire d’une ligne se fait à l’aide de –-
• La mise en commentaire d’un paragraphe se fait à l’aide de /* */

4.3.       Les différents types et objets

-         Les variables :

Elles se déclarent de la manière suivante

Declare @toto type(taille)

Ex :

Declare @nom nvarchar(20)

         Il existe de nombreux types de donnée, voici la liste des plus couramment utilisés

• Numeric : un type générique pour les nombres
• Nvarchar : stocke les chaînes de texte de taille variable, avec en plus les codes du jeu de caractère local, on peut par exemple stocker des caractères japonais.
• Varchar : similaire au Nvarchar, mais n’implémente pas les jeux de caractère locaux, il occupe donc moins d’espace.
• Ntext : pour les chaînes de texte unicode de grange taille
• Datetime : pour les dates, attention il faut les présenter sous le format aaaa-mm-jj pour êtres sur que la date sera bien interprétée lors d’une insertion. Plusieurs fonctions permettent de modifier le format de la date pour l’affichage.
• Image : ce type permet de stocker des objets binaires de grande taille tel que des fichiers.
• Real : pour les nombres réels
• Ntext : pour les champs texte de grande taille
• Bit : un champ booléen.
• Sql_variant : ce nouveau type un peu spécial est un fourre-tout. On peut stocker n’importe quel type dedans. Mais attention pour récupérer la valeur que l’on a stockée dedans il faudra impérativement la convertir au format souhaité. A utiliser avec parciemonie.

-         Les variables systèmes :

Elles commencent toujours par un double @. Ce sont des variables initialisées par le système et qui renseignent sur son état. Par exemple @@version donne la version du logiciel.

-         Les opérateurs :

T-SQL dispose des fonctions arithmétiques classiques :

         - addition è +

         - Soustraction è -

         - Multiplication è *

         - Division è /

         - Modulo è %

         - comparaison è >=, !>, <=, !< , = ; !=

         - ET logique è &

         - OU logique è |

         - XOR è ^

L’opérateur de concaténation de chaîne est le ‘+’.

-         Les Curseurs :

Ces objets permettent de naviguer au sein d’un jeu d’enregistrement afin de pourvoir effectuer un traitement ligne par ligne. Nous verrons leur fonctionnement en détail un peu plus loin.

4.4.      Création d’une procédure stockée

Une procédure stockée est un programme T-SQL nommé qui peut prendre un ou plusieurs paramètres. Les procédures sont stockées dans la base et compilées lors de leur première exécution.

Voici comment se présente la syntaxe :

CREATE     PROCEDURE [base].[utilisateur].nom

         @parametre1 type,

         @parametre2 type

 AS

Code de la procédure.

Habituellement les déclarations de variable suivent immédiatement le ‘AS’, bien que cela ne soit pas obligatoire.

Il existe une variante des procédure stockées : les fonctions. Elles sont similaire au procédures à l’exception qu’elles renvoient une valeur au moyen d’un return, un peu comme les fonction en C.

4.5.      Affectation

L’affectation d’une valeur à une variable peut se faire de deux manières différentes.

-         Avec SET :

SET @maVariable = @monautrevariable

-         Avec un select :

Select @mavariable = macolonne from matable where macolonne = ‘toto”

Bien sur ceci ne marche que si le select ne renvoi qu’un seul enregistrement.

Si ce n’est pas le cas il faudra utiliser un curseur.

4.6.      Boucles et structures de contrôle

L’intérêt principal d’un langage tel que le T-SQL au sein d’une base de donnée est bien entendu l’apport de boucle et de structure de contrôle tel que notre bon vieux ‘‘if’’.

Voici celle que nous apporte SQL Server.

-         If…Else

    IF condition

         BEGIN

                   {code}

         END

     ELSE

         BEGIN

                   {code}

         END

-         CASE 

CASE

         WHEN condition THEN code

         WHEN condition2 THEN autre code

END

Fonctionne un peu comme le switch case en C.

-         Boucle While

WHILE condition

     Bloc de code

Il est possible de sortir prématurément d’une boucle WHILE à l’aide de l’instruction BREAK.

-         Boucle GOTO

Label :

Bloc de code

GOTO label

-         L’instruction WAITFOR

Cette Instruction stoppe l’exécution de la procédure pendant le temps imparti.

WAITFOR TIME ‘18h20’

4.7.      Les transactions

Les serveurs de base de donnée du calibre de SQL server sont très souvent utilisés dans des applications critiques pour l’entreprise.

Dans de telles conditions les opérations effectuées par le serveur doivent avoir un certain degré de fiabilité.

C’est pour cette raison que tous les serveurs de base de donnée d’entreprise intègre la notion de transaction.

Qu’est ce qu’une transaction ?

Une transaction est une suite d’opération qui doit s’exécuter dans son intégralité ou pas du tout. Prenons l’exemple d’un distributeur de banque. Lorsque quelqu’un retire de l’argent cela représente une transaction qui s’effectue en deux temps, tout d’abord le débit de la somme sur le compte du client puis la distribution de la somme. Il est impensable que l’une de ces deux opérations soit effectuée alors que la seconde a échouée. Dans le cadre d’une transaction ceci est impossible, les deux opération s’exécutent, ou aucune…

Voici comment se présente les transactions sous SQL server :

BEGIN TRANSACTION [nom][with Mark] è indique le début d’une transaction

COMMIT [TRANSACTION nom] è Valide définitivement la transaction

Plusieurs transactions peuvent êtres imbriquée, il est alors possible de connaître le niveau de la transaction à l’aide de la variable système @@trancount.

Il est également possible dans le cadre de transaction longue d’effectuer des points de sauvegarde avec la commande SAVE TRAN [nom], on peut ensuite revenir au point de sauvegarde à l’aide de l’instruction ROLLBACK [TRANSACTION nom].

4.8.      Fonctions usuelles

De très nombreuses fonctions sont intégrées de base dans SQL Server, comme ce document ne cherche pas à êtres un traité de T-SQL je ne les énumèrerai pas.

Les fonctions arithmétiques usuelles sont disponibles ainsi que les fonctions de traitement de chaîne, de dates et bien d’autre encore. La liste ainsi que le prototype de ces fonctions est disponible dans l’explorateur d’objet de l’analyseur de requête.

4.9.      Les curseurs

Les curseurs représentent une fonctionnalité très importante de T-SQL, le traitement des jeux d’enregistrement multi ligne.

Nous allons voir ensemble comment créer un curseur simple et l’utiliser.

Tout d’abord il faut déclarer le curseur :

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM Northwind.dbo.Employees

WHERE LastName like 'B%'

On déclare le curseur en lui spécifiant la requête à exécuter.

Il faut ensuite l’ouvrir.

OPEN Employee_Cursor

On peut maintenant parcourir les enregistrements du curseur.

FETCH NEXT FROM Employee_Cursor

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM Employee_Cursor

END

Le premier FETCH NEXT permet de se placer sur le premier enregistrement, la variable système @@FETCH_STATUS nous donne ensuite l’état du curseur, tant que celui-ci n’est pas à zéro nous n’avons pas atteint le dernier enregistrement et nous pouvons continuer à parcourir le curseur. Il est bien entendu possible de mettre le résultat dans des variables au moyen de FETCH NEXT.

Une fois le travail terminé il faut fermer et détruire le curseur.

CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

ATTENTION ! Si on oublie de détruire le curseur celui-ci reste actif jusqu'à la fin de la session. Un autre curseur portant le même nom ne pourra donc pas être crée tant que le premier n’aura pas été détruit.

N’utiliser les curseurs que lorsque c’est réellement indispensable, ils consomment beaucoup de ressources.

4.10. La gestion des erreurs

En cas d’erreur le système dispose d’une liste de message d’erreur par défaut, mais il est également possible d’implémenter sa propre gestion des messages d’erreur au moyen de RAISERROR.

Rien ne vaut un bon exemple d’utilisation.

BEGIN

   RAISERROR ('The level for job_id:%d should be between %d and %d.',

      16, 1, @@JOB_ID, @@MIN_LVL, @@MAX_LVL)

   ROLLBACK TRANSACTION

END

Le premier paramètre est la chaîne de texte qui va être renvoyée par raiserror.

Les %d sont assez similaires au concept du printf en C, lors de l’affichage du message ils sont remplacés par la valeur des variables constituées par les derniers paramètres.

Le second paramètre est la sévérité de l’erreur, en fonction de celui-ci l’erreur pourra être mentionnée dans les journaux d’erreur de SQL et de Windows. Attention un niveau compris entre 20 et 25 est considéré comme critique et provoque une déconnection immédiate du client.

Le dernier paramètre représente l’état d’appel de l’erreur, laisser ce paramètre à 1.

---