Une procédure stockée, aussi appelée stored procedure en anglais, est un concept utilisé en administration de base de données afin d’exécuter un ensemble d’instructions SQL. Une telle procédure est stockée au sein du Système de Gestion de Base de Donneés (SGBD) et peut être appelée à tout moment par son nom afin d’exécuter celle-ci.
Explications détaillées
Pour les développeurs, la manière la plus simple de comprendre une procédure stockée consiste à considérer que c’est l’équivalent de ce que l’ont nomme une “fonction” au sein d’autres langages informatiques.
A noter : les procédures stockées sont des éléments qui seront enregistré au sein de la base de données afin de pouvoir être appelé ultérieurement, en opposition aux requêtes SQL qui sont des instructions exécutée une fois puis qui sont supprimées à la fin de la session.
Les intérêts sont multiples :
- Simplifier : un même code qui doit souvent être effectuée peut être enregistré afin d’ê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ête à l’emploi par la solution informatique qui va utiliser ces données. Par ailleurs, cela va réduire les échanges entre le client et le serveur
- Sécurité : des applications peuvent avoir accès uniquement aux procédures stockées, sans avoir accès aux données des tables directement, et/ou s’assurer que l’accès aux données soit toujours effectué de la même manière
A noter : il convient d’être vigilant dans la comptabilité du code d’une procédure stockée entre des systèmes de gestion de base de données différents. Les migrations d’un système à un autre doivent être travaillées pour éviter les erreurs.
Exemples
Exemple MySQL
L’exemple ci-dessous est une procédure stockée au sein de MySQL qui permet lire les pays d’un continent rapidement en donnant le nom du contient 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, il est possible d’exécuter la requête SQL suivante:
CALL country_hos('Europe');
Exemple PostgreSQL
L’exemple ci-dessous est une procédure stockée au sein de PostreSQL pour insérer des données rapidement 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, il est possible d’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 ISNULL 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 via la requête suivante :
CALL genererUserID();