← Retour à la Base de connaissances← Question précédenteQuestion suivante →
ProgrammationDéveloppeur de base de données / Architecte de bases de données

Comment créer et utiliser correctement des sous-programmes (Procédure/Fonction) en SQL pour mettre en œuvre une logique métier réutilisable, et quelles sont les subtilités de la gestion des paramètres et des valeurs de retour ?

Réussissez les entretiens avec l'assistant IA Hintsage

Réponse.

Les sous-programmes en SQL se déclinent en deux types :

  • Procédures (Stored Procedures) : effectuent des actions (insertion, mise à jour, suppression), peuvent retourner des paramètres OUT ou des ensembles de lignes (via SELECT). Elles sont appelées avec CALL/EXEC et ne sont pas toujours utilisées dans la couche SELECT.
  • Fonctions (User Defined Functions, UDF) : retournent une valeur (scalaire ou table), peuvent être utilisées directement dans SELECT, WHERE, ORDER BY et d'autres constructions.

Important :

  • Les procédures NE peuvent pas être utilisées dans un SELECT normal, uniquement par appel avec EXEC/CALL.
  • Les fonctions peuvent retourner une seule valeur (scalaire) ou une table (fonctions tabulaires), et peuvent être utilisées dans n'importe quelle expression.
  • Documentez toujours les paramètres : IN (pour l'entrée), OUT (pour le retour de résultat), INOUT (bidirectionnels).

Exemple de fonction scalaire (PostgreSQL) :

CREATE FUNCTION get_tax(amount NUMERIC, rate NUMERIC DEFAULT 0.13) RETURNS NUMERIC AS $$
BEGIN
  RETURN amount * rate;
END;
$$ LANGUAGE plpgsql;

-- utilisation :
SELECT *, get_tax(price) AS tax FROM product;

Exemple de procédure stockée (SQL Server) :

CREATE PROCEDURE add_employee(@name NVARCHAR(100), @salary INT, @emp_id INT OUTPUT)
AS
BEGIN
  INSERT INTO employees (name, salary) VALUES (@name, @salary);
  SET @emp_id = SCOPE_IDENTITY();
END;

DECLARE @id INT;
EXEC add_employee 'John', 100000, @id OUTPUT;

Question piège.

Peut-on utiliser une procédure stockée directement dans SELECT ?

On répond souvent "oui", mais c'est incorrect.

Réponse :

  • Non : les procédures standards ne peuvent être appelées que séparément (EXEC/CALL), tandis que les fonctions peuvent être utilisées dans SELECT.

Exemples d'erreurs réelles dues à une méconnaissance des subtilités du sujet.

Histoire

Projet : Système de comptabilité primaire, mise en œuvre de rapports. Erreur : Au lieu d'une fonction, une procédure a été écrite pour calculer la somme — SELECT ne fonctionnait pas, il a fallu réécrire toute la logique des rapports utilisateurs.

Histoire

Projet : Système ERP avec des paramètres externes. Erreur : Pour la procédure, le mot-clé OUT n'a pas été défini, du coup le client ne pouvait pas connaître l'ID de l'enregistrement ajouté, l'intégration s'est "cassée".

Histoire

Projet : Service financier avec calculs de taxes selon différentes règles. Erreur : Utilisation d'une fonction scalaire dans une requête massive sans test de performance — la requête a ralenti le traitement de la table en raison de l'appel ligne par ligne (plan non optimisé).