Qu'est-ce que les procédures stockées et quand devez-vous les utiliser ?

Question

Accepted Answer

Une **procédure stockée** est un ensemble d'instructions SQL (et de logique procédurale) sauvegardé dans la base de données et exécuté par nom. Elle exécute de la logique *à l'intérieur* de la base de données plutôt que dans le code de l'application. Elles offrent de vrais avantages mais aussi des compromis significatifs, donc savoir quand les utiliser est une véritable décision de conception.

## Une procédure stockée

```sql
-- define a procedure that encapsulates logic in the database
CREATE PROCEDURE transfer_funds(from_id INT, to_id INT, amount DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- can include logic: conditionals, loops, error handling, transactions
END;
$$;

CALL transfer_funds(1, 2, 100);   -- execute by name
```

Une procédure stockée regroupe SQL et la logique procédurale (variables, conditions, boucles, gestion des erreurs) dans une routine de base de données nommée et réutilisable. Les fonctions sont similaires mais retournent une valeur.

## Avantages potentiels

```text
✓ Performance — runs IN the database (no round-trips for multi-step logic); precompiled
✓ Reduced network traffic — one call vs many queries from the app
✓ Reusability — shared logic callable from multiple applications/clients
✓ Security — grant EXECUTE on a procedure without granting table access; reduces
  injection surface (parameterized)
✓ Consistency — centralize critical logic so all clients use the same implementation
```

## Les compromis significatifs (pourquoi beaucoup les évitent aujourd'hui)

```text
✗ Business logic in the DATABASE → harder to version-control, test, and maintain
  (logic split between app code and the database)
✗ Database-specific (PL/pgSQL vs T-SQL vs PL/SQL) → less portable, vendor lock-in
✗ Harder to debug than application code; weaker tooling
✗ Scaling — the database is often the hardest tier to scale; pushing logic there
  can increase its load
✗ Modern apps often prefer logic in the application layer (testable, portable, versioned)
```

## Quand les utiliser

```text
✓ Performance-critical data-intensive operations (heavy multi-step DB logic, batch jobs)
✓ Logic that MUST be enforced at the DB level across multiple clients
✓ Reducing round-trips for operations involving many queries
✗ AVOID putting general business logic in procedures by default — modern practice
  usually favors the application layer (better testing, versioning, portability)
```

## Pourquoi c'est important

Comprendre les procédures stockées — y compris leurs compromis — est une connaissance précieuse car elles représentent un véritable choix architectural avec de vrais avantages et de vrais inconvénients, et savoir *quand* les utiliser (et quand ne pas les utiliser) reflète un jugement mature.

Les procédures stockées peuvent offrir des **avantages de performance** (exécuter de la logique multi-étapes à l'intérieur de la base de données évite les allers-retours réseau), une réduction du trafic réseau, une réutilisabilité entre clients, une sécurité (accorder l'exécution sans accès aux tables), et une cohérence centralisée — les rendant appropriées pour les opérations critiques en performance et intensives en données, le traitement par lots, et la logique qui doit être appliquée au niveau de la base de données sur plusieurs applications.

Cependant, comprendre les **compromis significatifs** est tout aussi important et reflète la pratique moderne : mettre la logique métier dans la base de données la rend plus difficile à contrôler en version, tester et maintenir (logique divisée entre l'application et la base de données), vous lie au langage procédural spécifique d'une base de données (PL/pgSQL, T-SQL, PL/SQL — réduisant la portabilité), est plus difficile à déboguer, et ajoute une charge au tier base de données (souvent le plus difficile à mettre à l'échelle).

Pour ces raisons, **le développement d'applications moderne privilégie souvent de maintenir la logique métier dans la couche application** (testable, portable, contrôlée en version) plutôt que dans les procédures stockées.

L'idée clé est le **jugement de quand les utiliser** — approprié pour des besoins spécifiques critiques en performance ou d'application au niveau de la base de données, mais généralement évité par défaut pour la logique métier.

Étant donné que les procédures stockées sont courantes dans de nombreux systèmes (particulièrement en environnement d'entreprise/hérité), et puisque comprendre à la fois leurs avantages et leurs compromis (et la tendance moderne vers la logique de la couche application) est ce qui permet de prendre des décisions architecturales judicieuses, comprendre les procédures stockées est une connaissance précieuse de niveau senior qui démontre la capacité à peser la logique côté base de données par rapport à la logique côté application — une considération de conception significative et un sujet reflétant la maturité architecturale.