Une procédure stockée (stored procedure) est une suite d’instructions SQL qui s’exécutent les unes à la suite des autres et qui est conservée de façon durable (1 jour, 1 mois, 1 an, il n’y a pas de limite). Pour faire une analogie avec Excel, on pourrait dire qu’il s’agit d’une macro qui répète un ensemble de demandes simples préalablement enregistrées.
Une fois créée, on retrouve donc la procédure stockée parmi les autres objets de la base de données (comme une table) et on peut l’appeler pour l’exécuter.
À l’intérieur d’une procédure stockée, on peut utiliser des conditions et des boucles (IF, WHILE, LOOP, REPEAT, CASE, ITERATE…) ce qui permet d’effectuer des opérations beaucoup plus riches sur les données directement dans la base.
Tous les SGBD ne permettent pas l’utilisation de procédures stockées (MySQL ne le permet que depuis la version 5).
Intérêt des procédures stockées
- Performances accrues : chaque requête envoyée à la base de donnée doit être interprétée puis exécutée. Ça prend du temps. Une procédure stockée n’est interprétée qu’une seule fois puis conservée par le serveur de base de données sous une forme particulière (compilée). Ça permet d’économiser du temps de serveur et des transferts de données.
- Simplification des commandes : on peut envoyer des paramètres aux procédures stockées et, pour les développeurs, les procédures stockées sont comme des fonctions : concises à appeler et à utiliser, elles simplifient le code et facilitent la lecture.
- Plus grande cohérence dans les opérations : plutôt que d’avoir 10 façons différentes de faire la même action, la procédure stockée homogénéise les traitements;
- Meilleure sécurité : une fois validée une procédure stockée n’est plus modifiée. Cela évite de ré-inventer la roue et de faire de nouvelles requêtes (avec les risques d’erreurs associés). Cela évite aussi de toucher directement aux tables de la base de données. À noter aussi que les procédures stockées peuvent éviter les injections SQL.
- Meilleure gestion des droits : les procédures stockées, parce qu’elles fonctionnent comme des boites noires sécurisées, peuvent permettre à des utilisateurs de réaliser des opérations auxquelles ils n’ont normalement pas le droit. Dans l’environnement contrôlée de la procédure stockée, on peut ainsi autoriser des actions bien précises à des utilisateurs de niveaux de droits inférieurs.
Défauts des procédures stockées
- Au sein d’un logiciel, l’évolution des versions nécessitent des ajustements afin de prendre en compte les modifications apportées aux procédures stockées.
- D’un SGBD à l’autre, la syntaxe et les possibilités diffèrent.
- Le déboggage des procédures stockées est moins poussé que les déboggeurs de requêtes.