Procédure Stockée SQL : Automatiser et Optimiser les Requêtes

Tutoriels
SQL

Les procédures stockées SQL sont des ensembles d’instructions SQL sauvegardées et stockées dans une base de données. Elles peuvent être exécutées à la demande pour effectuer des tâches de manipulation et de validation des données, réduisant ainsi le besoin d’écrire du code SQL répétitif pour des opérations courantes. Les procédures stockées sont utiles dans la gestion des bases de données en favorisant l’efficacité et la réutilisabilité. De plus, elles soutiennent une sécurité et une maintenabilité améliorées des bases de données. Dans cet article, nous allons discuter de la manière de créer et d’exécuter des procédures stockées SQL, des cas d’utilisation courants et des meilleures pratiques.

Alors que nous commençons, je vous recommande vivement de suivre les cours Introduction à SQL et Apprendre SQL de DataCamp pour acquérir les connaissances fondamentales sur l’extraction et l’analyse de données à l’aide de SQL. De plus, le Fiche de révision des bases SQL, que vous pouvez télécharger, est une référence utile car il contient toutes les fonctions SQL les plus courantes.

Qu’est-ce qu’une procédure stockée en SQL?

Une procédure stockée en SQL est une collection d’instructions SQL sauvegardées et stockées dans la base de données. Le but de la procédure stockée SQL est d’effectuer une séquence d’opérations sur une base de données, telles que l’interrogation, l’insertion, la mise à jour ou la suppression de données.

Contrairement aux requêtes SQL ordinaires, exécutées en tant que commandes séparées, les procédures stockées encapsulent un ensemble d’instructions SQL, facilitant la réutilisation du code sans avoir à écrire les commandes SQL de manière répétée.

Les avantages des procédures stockées SQL incluent les suivants :

  • Réutilisabilité du code : Une fois qu’une procédure stockée est créée, elle peut être appelée autant de fois que nécessaire, éliminant la redondance dans le code SQL.
  • Performance améliorée : Les procédures stockées s’exécutent souvent plus rapidement car elles sont précompilées et stockées sur le serveur de base de données, réduisant la latence réseau et le temps de compilation.
  • Sécurité: Les procédures stockées peuvent améliorer la sécurité des données et le contrôle de l’accès aux données sensibles en accordant aux utilisateurs la permission d’exécuter une procédure stockée sans un accès direct aux tables.

Syntaxe de base et structure

La syntaxe pour créer une procédure stockée peut varier légèrement en fonction du système de base de données (par exemple, MySQL, SQL Server, Oracle). Voici un exemple général utilisant la syntaxe de SQL Server:

-- Créer une procédure stockée nommée NomDeLaProcédure
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- Les instructions SQL vont ici
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

Dans la syntaxe ci-dessus;

  • CRÉER UNE PROCÉDURE : Cette commande est utilisée pour définir une nouvelle procédure stockée.

  • NomDeLaProcédure : Le nom donné à la procédure stockée. Il doit être unique dans la base de données.

  • @Paramètre1, @Paramètre2: Les paramètres sont facultatifs; ils permettent à la procédure de recevoir des données en entrée. Chaque paramètre est défini avec un symbole @ et un type de données (par exemple, INT, VARCHAR(50)).

  • COMME DÉBUT…FIN : Les instructions SQL entre les balises DÉBUT et FIN forment le corps de la procédure, où la logique principale est exécutée. La procédure récupère des enregistrements d’une table en fonction de conditions spécifiques dans cet exemple.

Paramètres d’entrée et de sortie

Les paramètres d’entrée et de sortie vous permettent de transmettre des valeurs à une procédure stockée et d’en recevoir en retour.

Par exemple, si @Parameter1 est défini comme un paramètre d’entrée, il peut se voir attribuer n’importe quelle valeur lorsque la procédure est appelée, affectant la logique SQL ou la sortie. Dans l’exemple ci-dessous, le paramètre @UserID récupère des données spécifiques au UserID fourni.

-- Créer une procédure pour récupérer des données pour un utilisateur spécifique par UserID
CREATE PROCEDURE GetUserData 
 -- Paramètre d'entrée : ID de l'utilisateur à récupérer
    @UserID INT 
AS
BEGIN
    -- Sélectionner toutes les colonnes de Users où UserID correspond au paramètre d'entrée
    SELECT * FROM Users WHERE UserID = @UserID;
END;

Les paramètres de sortie, définis par le mot-clé OUTPUT, permettent à une procédure stockée d’envoyer une valeur de retour à l’environnement appelant. Par exemple, si une procédure calcule une remise, elle pourrait utiliser un paramètre de sortie pour la renvoyer au programme appelant.

-- Créer une procédure pour calculer le prix après remise
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Sortie : prix final après remise
AS
BEGIN
    -- Calculer le prix final en appliquant le taux de remise au prix d'origine
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Pour appeler cette procédure, vous utiliseriez :

-- Déclarez une variable pour stocker le prix final après réduction
DECLARE @FinalPrice DECIMAL(10, 2);
-- Exécutez la procédure CalculateDiscount avec un prix de 100 et une réduction de 10%
-- Stockez la sortie dans la variable @FinalPrice
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Sélectionnez et affichez le prix final avec réduction
SELECT @FinalPrice AS FinalPrice;

Procédures stockées dans MySQL

Comme je l’avais mentionné, l’exécution d’une procédure stockée en SQL peut se faire de différentes manières selon le système de base de données et les outils utilisés.

Création de procédures stockées dans MySQL

Créer une procédure stockée dans MySQL implique de définir le nom de la procédure, les paramètres et les instructions SQL qui composent son corps. L’exemple suivant crée une procédure appelée GetEmployeeDetails qui prend EmployeeID comme paramètre d’entrée et récupère les détails de cet employé spécifique.

DELIMITER $
-- Créez une procédure pour récupérer les détails d'un employé spécifique par EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Sélectionnez toutes les colonnes de la table Employees où EmployeeID correspond au paramètre d'entrée
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

Exécution des procédures stockées dans MySQL

La manière la plus courante d’exécuter une procédure stockée est d’utiliser des commandes SQL. Dans MySQL, nous utilisons la commande CALL pour exécuter la procédure stockée.

CALL ProcedureName();

En utilisant la procédure définie GetEmployeeDetails, la requête d’exécution ressemblerait à ceci :

-- Exécutez la procédure stockée pour récupérer les détails de l'EmployeeID 101
CALL GetEmployeeDetails(101);

Procédures stockées dans SQL Server

SQL Server fournit une syntaxe et des commandes spécifiques pour créer, exécuter et gérer les procédures stockées. Cette approche facilite la construction de routines SQL efficaces et réutilisables qui peuvent gérer des tâches complexes avec peu de répétition.

Création de procédures stockées dans SQL Server

Créer une procédure stockée dans SQL Server implique de définir le nom de la procédure, les paramètres et les instructions SQL qui composent son corps. L’exemple suivant crée une procédure appelée GetEmployeeDetails qui prend @EmployeeID comme paramètre d’entrée et récupère les détails de cet employé spécifique.

-- Créer une procédure pour récupérer les détails d'un employé spécifique par EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Paramètre d'entrée : ID de l'employé à récupérer
AS
BEGIN
    -- Sélectionner toutes les colonnes de la table Employees où EmployeeID correspond au paramètre d'entrée
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Exécution des procédures stockées dans SQL Server

Dans SQL Server, la commande EXEC ou EXECUTE appelle une procédure stockée. L’exemple suivant montre comment exécuter la procédure stockée GetEmployeeDetails en utilisant des paramètres d’entrée spécifiques.

-- Exécuter la procédure GetEmployeeDetails avec EmployeeID défini sur 102
EXEC GetEmployeeDetails @EmployeeID = 102;

Vous pouvez également exécuter les paramètres de sortie en déclarant la variable dans la commande. Dans l’exemple suivant, @TotalSales est déclaré comme une variable pour recevoir la sortie de CalculateTotalSales.

-- Déclarer une variable pour stocker le montant total des ventes
DECLARE @TotalSales DECIMAL(10, 2);
-- Exécuter CalculateTotalSales pour SalespersonID 5, stocker le résultat dans @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Afficher le montant total des ventes
SELECT @TotalSales AS TotalSales;

Je recommande de suivre notre cours Introduction à SQL Server pour comprendre les différentes fonctionnalités de SQL Server pour interroger les données. Considérez également notre parcours professionnel complet Développeur SQL Server, qui vous dotera non seulement des compétences pour créer, mettre à jour et exécuter des procédures stockées, mais vous aidera également avec les fonctions d’agrégation, les jointures, l’insertion et la suppression de tables, et bien plus encore.

Utilisations courantes des procédures stockées

Les procédures stockées SQL sont utiles dans les scénarios où des tâches complexes répétitives sont requises. Voici quelques applications concrètes des procédures stockées dans la gestion des données et les opérations commerciales.

Validation des données et application de l’intégrité

Les procédures stockées peuvent être utilisées pour valider les données avant une mise à jour ou une insertion. Dans l’exemple ci-dessous, une procédure stockée vérifie qu’un e-mail de client est unique avant d’insérer un nouvel enregistrement dans la table Clients, garantissant ainsi la cohérence des données. Cela centralise la logique de validation au sein de la base de données, réduisant la redondance et garantissant une application uniforme à travers différentes applications.

-- Créer une procédure pour ajouter un nouveau client, en vérifiant les e-mails en double
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Vérifier si l'e-mail existe déjà dans la table Clients
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Générer une erreur si l'e-mail est déjà utilisé
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insérer les détails du nouveau client si l'e-mail est unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Traitement et reporting automatisés des données

Vous pouvez également utiliser des procédures stockées pour générer des rapports réguliers ou traiter de grands ensembles de données. Par exemple, une procédure stockée pourrait agréger les données de ventes quotidiennes d’une plateforme de commerce électronique et les stocker dans une table de reporting, facilitant ainsi l’accès aux analyses de ventes pour les équipes sans avoir à exécuter des requêtes complexes.

-- Créer une procédure pour générer un rapport de ventes quotidien
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insérer la date d'aujourd'hui et le total des ventes dans la table SalesReport
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Sélectionner la date actuelle et la somme des ventes pour aujourd'hui à partir de la table Sales
    SELECT CAST(GETDATE() AS DATE), SUM(SalesAmount) 
    FROM Sales 
    WHERE SaleDate = CAST(GETDATE() AS DATE);
END;

Gestion des transactions

En utilisant des procédures stockées, vous pouvez vous assurer que plusieurs opérations sont exécutées comme une seule transaction. Par exemple, dans un système bancaire, une procédure stockée peut gérer à la fois les actions de débit et de crédit dans un transfert de fonds, garantissant que les deux actions réussissent ou échouent ensemble.

-- Créer une procédure pour transférer des fonds entre comptes
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Démarrer une transaction pour assurer l'atomicité
    -- Déduire le montant spécifié du solde du compte de l'expéditeur
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Ajouter le montant spécifié au solde du compte du destinataire
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Vérifier les erreurs et annuler si nécessaire; sinon, valider la transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Annuler tous les changements en cas d'erreur
    ELSE
        COMMIT TRANSACTION;    -- Confirmer les changements s'il n'y a pas d'erreurs
END;

Contrôle d’accès et sécurité des données

Vous pouvez également utiliser des procédures stockées SQL pour contrôler l’accès aux données sensibles. Par exemple, une procédure stockée peut limiter l’accès direct à une table en permettant aux utilisateurs d’appeler une procédure qui ne récupère que les champs pertinents, tels que les soldes des comptes, sans les détails des transactions.

-- Créer une procédure pour récupérer le solde du compte, avec vérification d'autorisation
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Vérifier si le compte existe et est détenu par l'utilisateur spécifié
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- Si autorisé, sélectionner et retourner le solde du compte
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- Si non autorisé, générer une erreur
        THROW 50000, 'Unauthorized access.', 1;
END;

Migration de données et processus ETL

Les procédures stockées sont également utilisées pour charger, transformer et migrer des données entre les systèmes. Une procédure stockée peut automatiser l’extraction de données d’une base de données source, les transformer si nécessaire et les insérer dans une table cible, simplifiant ainsi l’intégration des données pour le reporting ou l’analyse.

CREATE PROCEDURE ETLProcess
AS
BEGIN
    -- Extraire
    INSERT INTO StagingTable
    SELECT * FROM SourceTable WHERE Condition;
    -- Transformer
    UPDATE StagingTable SET ColumnX = TransformationLogic(ColumnX);
    -- Charger
    INSERT INTO TargetTable
    SELECT * FROM StagingTable;
END;

Meilleures pratiques pour les procédures stockées

Écrire des procédures stockées efficaces et maintenables garantit que votre base de données fonctionne de manière optimale. Voici quelques conseils pour écrire des procédures stockées pour vos bases de données SQL.

  • Utilisez des conventions de nommage cohérentes : Pour rendre les procédures stockées faciles à identifier et à comprendre, utilisez un format de nommage cohérent et descriptif. Évitez également le préfixe sp_ réservé dans SQL Server pour les procédures système afin d’éviter les conflits potentiels et les problèmes de performance.

  • Implémenter la gestion des erreurs : Enveloppez les instructions SQL dans des blocs TRY...CATCH pour attraper et gérer les erreurs et maintenir l’intégrité des données.

  • Optimiser les performances : Minimisez l’utilisation des curseurs car ils peuvent être lents et gourmands en ressources. Essayez plutôt d’utiliser des opérations basées sur des ensembles, qui sont généralement plus efficaces. De plus, indexez les colonnes fréquemment utilisées et évitez les jointures complexes dans les grandes tables pour réduire la surcharge mémoire et améliorer l’efficacité.

  • Paramétrer les Procédures Stockées : Utilisez des paramètres au lieu de valeurs codées en dur pour vous permettre de passer des valeurs dynamiques dans votre procédure, la rendant ainsi plus flexible et réutilisable.

Consultez notre cours SQL Intermediaire pour en savoir plus sur l’utilisation des fonctions d’agrégation et des jointures pour filtrer les données. Essayez également nos parcours de compétences Fondamentaux de SQL Server et Fondamentaux de SQL pour améliorer vos compétences en jointures de tables et en analyse de données.

Conclusion

Les procédures stockées SQL améliorent la réutilisabilité du code et l’optimisation des performances dans la gestion de base de données. Les procédures stockées renforcent également la sécurité de la base de données en contrôlant l’accès et en assurant l’intégrité des données. En tant que praticien des données, je vous encourage à pratiquer la création et l’exécution de procédures stockées pour maîtriser les meilleures pratiques de gestion de bases de données.

Si vous êtes intéressé à devenir un analyste de données compétent, consultez notre parcours professionnel Associate Data Analyst in SQL pour apprendre les compétences nécessaires. Le cours Reporting in SQL est également approprié si vous souhaitez apprendre à créer des tableaux de bord professionnels en utilisant SQL. Enfin, je recommande d’obtenir la SQL Associate Certification pour démontrer votre maîtrise de l’utilisation de SQL pour l’analyse de données et vous démarquer parmi les autres professionnels des données.

Source:
https://www.datacamp.com/tutorial/sql-stored-procedure