Procédures stockées définies par l'utilisateur en SQL

Vous utiliserez Microsoft SQL Server comme base de données. Le concept présenté dans ce tutoriel est commun à la plupart des systèmes de gestion de bases de données relationnelles (SGBDR), même si la syntaxe varie d’un système à l’autre. L’idée générale s’applique toutefois à toutes les bases.

Une procédure stockée est un bloc d’instructions SQL dans un SGBDR, généralement écrit par un développeur, un administrateur de base de données ou un data analyst, et sauvegardé pour être réutilisé dans plusieurs programmes. Elle peut prendre différentes formes selon le SGBDR. Cependant, on retrouve deux types essentiels dans tout SGBDR :

• Procédure stockée définie par l’utilisateur
• Procédure stockée système

Une procédure stockée est un code précompilé, conservé en base (ou en cache) puis réutilisé. Disposer d’un code mis en cache facilite grandement la maintenance : vous évitez des modifications en multiples endroits et vous renforcez la sécurité.

Créer une table

Vous allez maintenant créer une table nommée table_Employees comme suit.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)

Cette table contient des informations sur les employés d’une entreprise et comporte les colonnes suivantes :

1. EmployeeId : clé primaire unique de type entier ; la valeur s’auto-incrémente.
2. EmployeeFirstName : prénom de l’employé, de type caractère, champ obligatoire.
3. EmployeeLast Name : nom de famille de l’employé, de type caractère, champ obligatoire.
4. EmployeeGender : genre de l’employé, également de type caractère.
5. EmployeeDepartmentId : identifiant du département de l’employé, de type entier.

Vous pouvez écrire une requête pour insérer des valeurs dans la table. La forme générale pour ajouter des lignes est :

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)

Après insertion des valeurs, la table table_Employees est prête.

Créer une procédure stockée définie par l’utilisateur

Une procédure stockée vous permet d’écrire une requête une fois, de lui donner un nom, puis de l’enregistrer pour l’exécuter autant de fois que nécessaire. Après création, vous verrez apparaître un dossier Programmability->Stored Procedure, avec le fichier nommé dbo.uprocGetEmployees.

La syntaxe de création d’une procédure stockée est :

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END

Vous pouvez créer une procédure stockée avec l’une des instructions suivantes.

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Évitez de nommer vos procédures avec le préfixe « sp_ ». Dans l’exemple, la procédure est créée sous le nom : uprocGetEmployees.

Exécuter la procédure stockée

Pour exécuter une procédure stockée, trois approches sont possibles :

• EXEC <>
• EXECUTE <>
• <>

Dans le programme ci-dessus, utilisez le nom de procédure uprocGetEmployees et lancez son exécution.

Procédure stockée avec paramètres

Une procédure stockée peut accepter un ou plusieurs paramètres. Il est souvent plus simple de comprendre le cas à plusieurs paramètres en premier.

La syntaxe de création d’une procédure stockée avec plusieurs paramètres est :

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END

Dans l’exemple, notez l’évolution de l’arborescence :
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Le principe est très proche des fonctions dans des langages comme Python, Java, etc. La procédure « uprocGetEmployeesGenderAndDepartment » accepte les paramètres @EmployeeGender et @EmployeeDepartmentId, qui sont « appelés » en leur transmettant des valeurs. Deux méthodes sont possibles, détaillées ci-dessous.

Les paramètres de ce programme sont @EmployeeGender et @EmployeeDepartmentId, et l’on passe des valeurs à chacun d’eux à l’appel.

Vous pouvez spécifier les valeurs de deux manières. L’une ou l’autre permet d’exécuter la procédure stockée avec plusieurs paramètres.

• En indiquant les noms des paramètres dans la requête et en passant les valeurs correspondantes :

• En respectant l’ordre des paramètres de la procédure stockée et en passant les valeurs dans cet ordre :

Modifier la procédure stockée

Vous pouvez modifier une procédure stockée avec la commande ALTER PROCEDURE.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END

Supprimer une procédure stockée

Vous pouvez supprimer rapidement des procédures stockées avec les commandes suivantes :

DROP PROC <> DROP PROCEDURE <>

Ajouter une couche de sécurité avec le chiffrement

Le cadenas indiqué sur l’illustration de gauche signale qu’une procédure stockée est chiffrée, garantissant que seules les personnes autorisées peuvent y accéder.

Conclusion

Vous venez d’acquérir les bases des procédures stockées définies par l’utilisateur. Les notions abordées sont, dans l’ensemble, transposables à tout SGBDR. Pour aller plus loin, consultez le cours Intermediate SQL Server sur DataCamp. Vous pouvez également approfondir avec ce tutoriel sur les Stored Procedures.