Procédures stockées (CREATE PROCEDURE)

Une procédure stockée, également appelée “stored procedure” en anglais, est un concept utilisé en administration de base de données pour exécuter un ensemble d’instructions SQL. Cette procédure est enregistrée dans le système de gestion de base de données (SGBD) et peut être appelée à tout moment par son nom pour être exécutée.

Explications détaillées

Pour les développeurs, la manière la plus simple de comprendre une procédure stockée est de la considérer comme l’équivalent d’une “fonction” dans d’autres langages informatiques.

À noter : les procédures stockées sont enregistrées dans la base de données afin de pouvoir être appelées ultérieurement, contrairement aux requêtes SQL qui sont exécutées une fois et supprimées à la fin de la session.

Les intérêts des procédures stockées sont multiples :

• Simplification : un même code qui doit souvent être exécuté peut être enregistré pour être appelé rapidement.
• Amélioration des performances : les opérations peuvent être exécutées du côté du serveur de base de données et envoyées directement prêtes à l’emploi par la solution informatique qui utilise ces données. De plus, cela réduit les échanges entre le client et le serveur.
• Sécurité : des applications peuvent avoir accès uniquement aux procédures stockées, sans accéder directement aux données des tables, et/ou s’assurer que l’accès aux données soit toujours effectué de la même manière.

À noter : il est important de faire attention à la compatibilité du code d’une procédure stockée entre différents systèmes de gestion de base de données. Les migrations d’un système à un autre doivent être soigneusement travaillées pour éviter les erreurs.

Exemples

Exemple MySQL

L’exemple ci-dessous est une procédure stockée dans MySQL qui permet de lire les pays d’un continent rapidement en donnant le nom du continent concerné :

DELIMITER //
CREATE PROCEDURE country_hos(IN con CHAR(20))
BEGIN
    SELECT Name, HeadOfState FROM Country WHERE Continent = con;
END //
DELIMITER ;

Pour appeler la procédure, vous pouvez exécuter la requête SQL suivante :

CALL country_hos('Europe');

Exemple PostgreSQL

L’exemple ci-dessous est une procédure stockée dans PostgreSQL permettant d’insérer rapidement des données via les paramètres de la procédure stockée :

CREATE PROCEDURE insert_data(a integer, b integer) LANGUAGE SQL AS $$
    INSERT INTO tbl VALUES (a);
    INSERT INTO tbl VALUES (b);
$$;

Pour exécuter la procédure stockée, vous pouvez exécuter la requête SQL suivante :

CALL insert_data(1, 2);

Exemple PL/SQL (PostrgreSQL)

L’exemple ci-dessous est un code PL/SQL :

CREATE PROCEDURE genererUserID() RETURNS OPAQUE AS
DECLARE 
    user_id INTEGER;
BEGIN
    SELECT INTO user_id MAX(id_user) FROM user;
    IF user_id IS NULL THEN
        user_id := 0;
    END IF;
    NEW.id_user := user_id + 1;
    RETURN NEW;
END;
LANGUAGE 'plpgsql';

Il est possible d’appeler cette procédure en utilisant la requête suivante :

CALL genererUserID();

Maintenant vous avez toutes les clés en main pour utiliser les procédures stockées dans votre base de données. À vous de jouer !