Procédure stockée SQL : Automatiser et optimiser les requêtes

Les procédures stockées SQL sont des ensembles d'instructions SQL enregistrées et stockées dans une base de données. Ils peuvent être exécutés à la demande pour effectuer des tâches de manipulation et de validation des données, ce qui réduit la nécessité d'écrire un code SQL répétitif pour les opérations courantes. Les procédures stockées sont utiles dans la gestion des bases de données car elles favorisent l'efficacité et la réutilisation. En outre, ils permettent d'améliorer la sécurité et la maintenabilité des bases de données. Dans cet article, nous verrons comment créer et exécuter des procédures stockées SQL, les cas d'utilisation courants et les meilleures pratiques.

Pour commencer, je vous recommande vivement de suivre les cours Introduction à SQL et Apprendre SQL de DataCamp pour acquérir les connaissances fondamentales de l'extraction et de l'analyse de données à l'aide de SQL. Par ailleurs, l' aide-mémoire SQL Basics, 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 un ensemble d'instructions SQL enregistrées et stockées dans la base de données. L'objectif 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 sous forme de commandes distinctes, les procédures stockées encapsulent un ensemble d'instructions SQL, ce qui facilite la réutilisation du code sans avoir à écrire des commandes SQL à plusieurs reprises.

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

• Réutilisation du code : Une fois qu'une procédure stockée est créée, elle peut être appelée autant de fois que nécessaire, ce qui élimine la redondance dans le code SQL.
• Amélioration des performances : 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, ce qui réduit la latence du réseau et le temps de compilation.
• La 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 autorisant les utilisateurs à exécuter une procédure stockée sans accès direct aux tableaux.

Syntaxe et structure de base

La syntaxe de création d'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). Vous trouverez ci-dessous un exemple général utilisant la syntaxe du serveur SQL :

-- Create a stored procedure named ProcedureName
CREATE PROCEDURE ProcedureName 
    @Parameter1 INT, 
    @Parameter2 VARCHAR(50)  
AS
BEGIN
    -- SQL statements go here
    SELECT * FROM TableName WHERE Column1 = @Parameter1 AND Column2 = @Parameter2;
END;

Dans la syntaxe ci-dessus ;

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

• Nom de la procédure : 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. Chaque paramètre est défini par un symbole @ et un type de données (par exemple, INT, VARCHAR(50)).

• COMME DÉBUT...FIN : Les instructions SQL contenues dans BEGIN et END forment le corps de la procédure, où la logique principale est exécutée. Dans cet exemple, la procédure extrait des enregistrements d'un tableau en fonction de conditions spécifiques.

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.

Par exemple, si @Parameter1 est défini comme un paramètre d'entrée, n'importe quelle valeur peut lui être attribuée lors de l'appel de la procédure, ce qui affecte la logique SQL ou la sortie. Dans l'exemple ci-dessous, le paramètre @UserID permet d'extraire des données spécifiques à l'adresse UserID.

-- Create a procedure to retrieve data for a specific user by UserID
CREATE PROCEDURE GetUserData 
 -- Input parameter: ID of the user to retrieve
    @UserID INT 
AS
BEGIN
    -- Select all columns from Users where UserID matches the input parameter
    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 de renvoyer une valeur à l'environnement d'appel. Par exemple, si une procédure calcule une remise, elle peut utiliser un paramètre de sortie pour la transmettre au programme appelant.

-- Create a procedure to calculate the discounted price
CREATE PROCEDURE CalculateDiscount 
    @Price DECIMAL(10, 2), 
    @DiscountRate DECIMAL(5, 2),  
    @FinalPrice DECIMAL(10, 2) OUTPUT -- Output: final price after discount
AS
BEGIN
    -- Calculate final price by applying the discount rate to the original price
    SET @FinalPrice = @Price * (1 - @DiscountRate);
END;

Pour appeler cette procédure, vous devez utiliser ;

-- Declare a variable to store the final price after discount
DECLARE @FinalPrice DECIMAL(10, 2);
-- Execute the CalculateDiscount procedure with a price of 100 and a 10% discount 
-- Store the output in the @FinalPrice variable
EXEC CalculateDiscount @Price = 100, @DiscountRate = 0.1, @FinalPrice = @FinalPrice OUTPUT;
-- Select and display the final discounted price
SELECT @FinalPrice AS FinalPrice;

Procédures stockées dans MySQL

Comme je l'ai mentionné, l'exécution d'une procédure stockée en SQL peut se faire de différentes manières en fonction du système de base de données et des outils utilisés.

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

La création d'une procédure stockée dans MySQL implique la définition du nom de la procédure, de ses paramètres et des instructions SQL qui constituent 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 $
-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails(IN EmployeeID INT)  
BEGIN  
    -- Select all columns from Employees where EmployeeID matches the input parameter  
    SELECT * FROM Employees WHERE EmployeeID = EmployeeID;  
END$
DELIMITER ;

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

La façon 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 GetEmployeeDetails définie, la requête d'exécution ressemblerait à ceci :

-- Execute the stored procedure to retrieve details for EmployeeID 101
CALL GetEmployeeDetails(101);

Procédures stockées dans le serveur SQL

SQL Server fournit une syntaxe et des commandes spécifiques pour la création, l'exécution et la gestion des procédures stockées. Cette approche facilite la création de routines SQL efficaces et réutilisables, capables de traiter des tâches complexes avec un minimum de répétition.

Création de procédures stockées dans le serveur SQL

La création d'une procédure stockée dans SQL Server implique la définition du nom de la procédure, de ses paramètres et des instructions SQL qui constituent 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.

-- Create a procedure to retrieve details for a specific employee by EmployeeID
CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID INT          -- Input parameter: ID of the employee to retrieve
AS
BEGIN
    -- Select all columns from Employees where EmployeeID matches the input parameter
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Exécution de procédures stockées dans le serveur SQL

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. 

-- Execute the GetEmployeeDetails procedure with the EmployeeIDset to 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 variable pour recevoir la sortie de CalculateTotalSales. 

-- Declare a variable to store the total sales amount
DECLARE @TotalSales DECIMAL(10, 2);
-- Execute CalculateTotalSales for SalespersonID 5, store the result in @TotalSales
EXEC CalculateTotalSales @SalespersonID = 5, @TotalSales = @TotalSales OUTPUT;
-- Display the total sales amount
SELECT @TotalSales AS TotalSales;

Je vous recommande de suivre notre cours Introduction à SQL Server pour comprendre les différentes fonctionnalités de SQL Server pour l'interrogation des données. Pensez également à notre cursus complet de développeur SQL Server, qui vous permettra non seulement de créer, de mettre à jour et d'exécuter des procédures stockées, mais aussi de vous familiariser avec les fonctions d'agrégation, la jonction, l'insertion et la suppression de tableaux, et bien d'autres choses 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 et répétitives sont nécessaires. Vous trouverez ci-dessous des applications concrètes des procédures stockées dans le cadre de la gestion des données et des opérations commerciales. 

Validation des données et respect de l'intégrité

Les procédures stockées peuvent être utilisées pour valider les données avant leur mise à jour ou leur insertion. Dans l'exemple ci-dessous, une procédure stockée vérifie que l'adresse électronique d'un client est unique avant d'insérer un nouvel enregistrement dans le tableau Customers, ce qui garantit la cohérence des données. La logique de validation est ainsi centralisée dans la base de données, ce qui réduit les redondances et garantit une application uniforme dans les différentes applications.

-- Create a procedure to add a new customer, checking for duplicate email
CREATE PROCEDURE AddCustomer
    @CustomerName VARCHAR(50), 
    @CustomerEmail VARCHAR(50)
AS
BEGIN
    -- Check if the email already exists in the Customers table
    IF EXISTS (SELECT 1 FROM Customers WHERE Email = @CustomerEmail)
        -- Throw an error if the email is already in use
        THROW 50000, 'Email already exists.', 1;
    ELSE
        -- Insert new customer details if email is unique
        INSERT INTO Customers (Name, Email) VALUES (@CustomerName, @CustomerEmail);
END;

Traitement automatisé des données et établissement de rapports

Vous pouvez également utiliser des procédures stockées pour générer des rapports réguliers ou traiter des ensembles de données volumineux. Par exemple, une procédure stockée pourrait agréger les données de vente quotidiennes d'une plateforme de commerce électronique et les stocker dans un tableau de reporting, ce qui permettrait aux équipes d'accéder plus facilement aux informations sur les ventes sans exécuter de requêtes complexes.

-- Create a procedure to generate a daily sales report
CREATE PROCEDURE GenerateDailySalesReport
AS
BEGIN
    -- Insert today's date and total sales into the SalesReport table
    INSERT INTO SalesReport (ReportDate, TotalSales)
    
    -- Select current date and sum of sales for today from Sales table
    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 en 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, en s'assurant que les deux actions réussissent ou échouent ensemble.

-- Create a procedure to transfer funds between accounts
CREATE PROCEDURE TransferFunds
    @SenderAccount INT,
    @ReceiverAccount INT,
    @Amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;  -- Start a transaction to ensure atomicity
    -- Deduct the specified amount from the sender's account balance
    UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @SenderAccount;
    -- Add the specified amount to the receiver's account balance
    UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ReceiverAccount;
    -- Check for errors and rollback if any occurred; otherwise, commit the transaction
    IF @@ERROR <> 0
        ROLLBACK TRANSACTION;  -- Undo all changes if an error occurred
    ELSE
        COMMIT TRANSACTION;    -- Confirm changes if no errors
END;

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

Vous pouvez également utiliser SQL stored pour contrôler l'accès aux données sensibles. Par exemple, une procédure stockée peut limiter l'accès direct aux tableaux 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.

-- Create a procedure to retrieve account balance, with authorization check
CREATE PROCEDURE GetAccountBalance
    @AccountID INT, 
    @UserID INT 
AS
BEGIN
    -- Check if the account exists and is owned by the specified user
    IF EXISTS (SELECT 1 FROM Accounts WHERE AccountID = @AccountID AND UserID = @UserID)
        -- If authorized, select and return the account balance
        SELECT Balance FROM Accounts WHERE AccountID = @AccountID;
    ELSE
        -- If unauthorized, throw an error
        THROW 50000, 'Unauthorized access.', 1;
END;

Migration des données et processus ETL

Les procédures stockées sont également utilisées pour charger, transformer et migrer des données entre 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 un tableau cible, ce qui simplifie l'intégration des données pour l'établissement de rapports ou l'analyse.

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

Meilleures pratiques pour les procédures stockées

La rédaction de procédures stockées efficaces et faciles à maintenir garantit le fonctionnement optimal de votre base de données. Vous trouverez ci-dessous des conseils pour l'écriture de procédures stockées pour vos bases de données SQL.

• Utilisez des conventions de dénomination cohérentes : Pour faciliter l'identification et la compréhension des procédures stockées, utilisez un format de dénomination cohérent et descriptif. Évitez également le préfixe sp_ dans SQL Server, réservé aux procédures système, afin d'éviter les conflits potentiels et les problèmes de performance.

• Mettre en œuvre la gestion des erreurs : Enveloppez les instructions SQL dans des blocs TRY...CATCH pour détecter et traiter les erreurs et maintenir l'intégrité des données. 

• Optimisez les performances : Réduisez au minimum l'utilisation des curseurs, qui 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. En outre, indexez les colonnes fréquemment utilisées et évitez les jointures complexes dans les tableaux de grande taille afin de réduire la surcharge de mémoire et d'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, ce qui la rend plus flexible et réutilisable.

Consultez notre cours SQL intermédiaire pour en savoir plus sur l'utilisation des fonctions agrégées et des jointures pour filtrer les données. Essayez également nos cursus de compétences SQL Server Fundamentals et SQL Fundamentals pour améliorer vos compétences en matière de tableaux de jointure et d'analyse de données.

Conclusion

Les procédures stockées SQL améliorent la réutilisation du code et l'optimisation des performances dans la gestion des bases de données. Les procédures stockées renforcent également la sécurité des bases de données en contrôlant l'accès et en garantissant l'intégrité des données. En tant que praticien des données, je vous encourage à vous entraîner à créer et à exécuter des procédures stockées afin de maîtriser les meilleures pratiques de gestion des bases de données.

Si vous souhaitez devenir un analyste de données compétent, consultez notre cursus d'analyste de données associé en SQL pour acquérir les compétences nécessaires. Le cours Reporting in SQL est également approprié si vous souhaitez apprendre à construire des tableaux de bord professionnels à l'aide de SQL. Enfin, je vous recommande d'obtenir la certification SQL Associate pour démontrer votre maîtrise de l'utilisation de SQL pour l'analyse des données et vous démarquer parmi les autres professionnels des données.