Transact-SQL : le langage procédural

Le SQL procédural

SQL Server est un serveur de base de données relationnelle et à ce titre, il fournit tous les éléments pour stocker de façon structurée les données mais aussi les outils nécessaires pour travailler avec les données au travers de SQL. Avec le Transact-SQL, il est également possible de définir des traitements procéduraux directement dans la base de données. Ces traitements vont pouvoir être utilisables par tous les utilisateurs de la base sous réserve qu’ils possèdent les privilèges nécessaires. Il est possible de conserver la définition de ces traitements et de les rendre paramétrables par l’intermédiaire de la création de procédures et de fonctions.

Des traitements procéduraux pourront également être mis en place pour définir des contraintes d’intégrité complexes, il s’agira alors de déclencheurs de base de données (TRIGGER).

Le Transact-SQL est un langage procédural qui intègre complètement et nativement le langage SQL. Il est ainsi possible de tirer parti des deux langages. Par exemple, il va être très facile de définir une variable en Transact-SQL puis d’inclure cette variable dans une requête SQL. Lors de l’exécution de la requête, c’est la valeur contenue par la variable qui est prise en compte.

Le Transact-SQL n’a pas pour objectif de se substituer aux requêtes SQL mais plutôt de permettre un complément par rapport aux tâches réalisables en SQL.

À l’aide du Transact-SQL, il va être possible de définir des procédures et des fonctions dans la base de données. Les déclencheurs de base de données vont permettre la mise en place au niveau de la base de règles métiers complexes.

DECLARE @nom_variable...

Les procédures stockées

Les procédures stockées (Stored Procedures) sont des objets correspondant à un ensemble d’instructions du DML, pouvant être exécutées par simple appel de leur nom ou par l’instruction EXECUTE (ou EXEC). Ce sont de véritables programmes pouvant recevoir des paramètres, renvoyer des valeurs, être exécutés à distance, ayant leurs propres droits d’accès (privilège EXECUTE). De plus, les procédures stockées sont stockées dans le cache mémoire sous forme compilée lors de leur première exécution, ce qui accroît les performances (pour les exécutions suivantes !). Les procédures stockées sont une des bonnes pratiques à adopter pour écoconcevoir son application. Les procédures stockées peuvent éventuellement être temporaires, c’est-à-dire créées pour une session (locale) ou plusieurs sessions (globale) du user.

Pour SQL Server, une procédure stockée peut être définie comme une suite d’instructions Transact-SQL, stockée dans la base de données et parfaitement identifiée par son nom. Pour permettre à cette suite d’instructions de s’adapter au plus grand nombre de cas, certaines valeurs du script sont paramétrables lors de l’appel de la procédure. Comme toute suite d’instructions Transact-SQL, il est possible par exemple de trouver une instruction SELECT. L’exécution de la procédure déclenchera l’exécution...

Les fonctions stockées

SQL Server prend en charge trois types de fonctions utilisateur : les fonctions scalaires, les fonctions tables en ligne et les fonctions tables multi-instructions.

Une fonction accepte entre 0 et 1024 paramètres d’entrée et retourne, soit une valeur scalaire, soit une table.

Les paramètres se déclarent de la même manière que pour une procédure. La seule différence est que les paramètres ne peuvent être des paramètres en sortie (OUT ou OUTPUT).

Les fonctions de type scalaire retournent, à l’aide du mot-clé RETURN, une valeur scalaire. Bien sûr, les données de type timestamp, d’un type de données défini par l’utilisateur et d’un type table ou cursor ne peuvent être renvoyées. Il en est de même pour les types de données text, ntext et image.

Les fonctions tables retournent comme résultat une table. Elles ne contiennent pas de corps et la table est le résultat d’une commande SELECT unique.

Si la fonction est composée de plusieurs instructions, alors les instructions sont encadrées par les mots-clés BEGIN et END.

Les fonctions disposent d’un champ d’action limité et elles ne peuvent en aucun cas modifier leur environnement d’exécution. Depuis une fonction, il n’est donc pas possible de modifier le contenu d’une table de la base de données. À l’intérieur d’une fonction, les seules actions possibles sont celles qui vont modifier les objets locaux à la fonction.

La fonction est créée à l’aide de l’instruction CREATE FUNCTION. La clause WITH SCHEMABINDING permet de lier la fonction à tous les objets auxquels elle fait référence. Dès lors, toute modification...

L’exécution des procédures et fonctions

1. L’appel

L’instruction EXECUTE, parfois raccourcie en EXEC, permet de demander l’exécution de procédures stockées. Il s’agit de l’usage à privilégier de cette commande. Cependant, l’instruction EXECUTE accepte en paramètre une chaîne de caractères et procède à l’exécution de cette chaîne de caractère sur le moteur de base de données. Cette fonctionnalité permet de contourner un problème parfois rencontré lors de la conception de script Transcat SQL, de procédures ou de fonction qui est comment construire dynamiquement une requête. S’il est simple de valoriser les différents paramètres de la requête, cela n’est pas le cas pour construire la requête en fonction de la valeur ou de la présence de certains paramètres. L’instruction EXECUTE permet de construire une chaîne de caractères sous forme de chaîne de caractères puis de demander l’exécution de requête ainsi générée. Cette fonctionnalité présente, en revanche, l’inconvénient de faciliter les attaques par injection SQL. Pour éviter ce type de problème, il est nécessaire que le développeur n’exécute pas une requête ou bien un morceau de requête directement saisi par l’utilisateur sans pour autant faire les contrôles nécessaires pour s’assurer que ce code ne contient aucune attaque SQL.

Syntaxe

EXEC[UTE] ('chaîneDeCaractères')[;] 
EXEC[UTE] nomProcédure [[@nomParamètre=]valeur, ...] [;] 

Exemple

EXEC ('CREATE SEQUENCE sqTest;'); 
EXEC sp_help 'sqTest'; 

L’instruction EXECUTE permet également de présenter correctement les valeurs retournées par une procédure stockée ou bien une fonction. Il est alors nécessaire de compléter l’instruction avec la définition du RESULT SET si un seul jeu de valeurs est retourné ou bien des RESULT SETS si plusieurs jeux de données sont retournés, ce qui peut être le cas par une procédure.

Syntaxe

EXECUTE {nomProcédure|nomFonction} ...

Les déclencheurs

Un déclencheur de base de données permet d’exécuter automatiquement un ensemble d’instructions en réaction à l’exécution d’une autre instruction. Cela permet d’automatiser des traitements et d’effectuer des vérifications complexes. Par exemple, il est possible lors d’une instruction DELETE sur la table Clients d’insérer ce client dans la table ClientsArchives.

SQL Server propose deux types de déclencheurs : les déclencheurs du DML et ceux du DDL.

Les déclencheurs DML existent depuis longtemps dans SQL Server et sont présents dans de nombreuses bases de données. C’est ce type de déclencheur qui est détaillé ici.

Les déclencheurs du DDL reposent sur le même principe, à savoir associer l’exécution d’une procédure stockée à l’exécution d’une instruction. La particularité tient ici du fait que le déclencheur va être associé à une instruction du DDL, soit une commande CREATE, ALTER, DROP, GRANT, DENY, REVOKE et UPDATE STATISTICS. L’objectif de ces déclencheurs est de suivre l’évolution de la base pour réaliser au mieux les différentes tâches administratives.

C’est pour cette raison que ces déclencheurs ne sont pas présentés dans cet ouvrage. 

Un déclencheur DML, que nous nommerons dorénavant simplement déclencheur (ou trigger en anglais), peut être perçu comme un script Transact-SQL enregistré dans la base de données et dont l’exécution est associée à des évènements déclencheurs. Ces évènements déclencheurs sont ceux qui viennent modifier les données (INSERT, UPDATE ou DELETE) sur une table ou sur une vue.

Les déclencheurs permettent la mise en place de règles de gestion complexes qui font appel à des données présentes dans différentes tables ou bien à des calculs car il n’est pas possible de définir de telles règles de validation à l’aide de la contrainte CHECK. Les déclencheurs permettent aussi de supporter la dénormalisation des données...

Exercices

EXEC AjoutFiche 3, 'F50';  
EXEC AjoutFiche 4, 'F60', 'P10';  
EXEC AjoutFiche 5, 'F05', 'F62', 'F63'; 

2. Le montant d’une fiche

Écrivez une fonction MontantFiche qui retourne le montant d’une fiche de location dont le numéro est passé en paramètre.

Exemple d’appel

SELECT dbo.MontantFiche(1006) montant; 

Correction des exercices

GO  
CREATE OR ALTER PROCEDURE AjoutFiche(@noCli NUMERIC(6), @refart1 
CHAR(3), @refart2 CHAR(3) = NULL, @refart3 CHAR(3) = NULL) AS  
BEGIN  
       INSERT INTO Fiches(noCli) VALUES(@noCli);  
       DECLARE @noFic NUMERIC(6) = @@IDENTITY;  
       INSERT INTO LignesFic(noFic, noLig, refart) 
       VALUES(@noFic, 1, 
@refart1);  
       IF @refart2 IS NOT NULL  
             INSERT INTO LignesFic(noFic, noLig, refart)  
             VALUES(@noFic, 2, @refart2);  
       IF @refart3 IS NOT NULL  
             INSERT INTO LignesFic(noFic, noLig, refart)  
             VALUES(@noFic, 3, @refart3);  
END;  
GO 

GO  
CREATE OR ALTER FUNCTION MontantFiche(@noFic NUMERIC(6))  
RETURNS NUMERIC(6) AS  
BEGIN  
       DECLARE @montant NUMERIC(6);  
       SELECT @montant=montant  
       FROM MontantsFiches m  
       WHERE noFic = @noFic;  
       RETURN...