🔧

Procédures vs Fonctions

Organiser et réutiliser ton code SQL

En PL/pgSQL, tu peux créer des procédures et des fonctions pour encapsuler de la logique réutilisable. La différence principale ?

📦 PROCEDURE

  • ❌ Ne retourne rien (void)
  • ✅ Peut avoir des paramètres OUT
  • ✅ Peut faire COMMIT/ROLLBACK
  • 📌 Appelée avec CALL

⚡ FUNCTION

  • ✅ Retourne une valeur
  • ❌ Pas de COMMIT/ROLLBACK
  • ✅ Utilisable dans SELECT
  • 📌 Appelée directement ou dans une expression

💡 Règle simple

Fonction = Tu veux calculer et retourner quelque chose
Procédure = Tu veux exécuter des actions (INSERT, UPDATE, DELETE...)

Le trajet de la donnée

Clique sur un bouton : observe ce qui revient (ou pas) vers l'appelant.

APPELANT (session SQL) calculer_ttc(100) = 120 CALL augmenter_ salaire(42, 10) (aucun retour) ∅ void — rien ne revient FUNCTION calculer_ttc RETURN p * 1.20 PROCEDURE augmenter_salaire UPDATE employes table employes ▸ Léa │ 2 000 € 100 120 42, 10
Choisis un trajet : la fonction rend une valeur, la procédure exécute des actions.

⚡ Créer une Fonction 

CREATE OR REPLACE FUNCTION calculer_ttc(prix NUMERIC, tva NUMERIC DEFAULT 0.20)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN prix * (1 + tva);
END;
$$ LANGUAGE plpgsql;

Utilisation :

-- Dans un SELECT
SELECT nom, calculer_ttc(prix) AS prix_ttc FROM produits;

-- Affectation à une variable
v_total := calculer_ttc(100, 0.10);  -- 110

-- Appel direct
SELECT calculer_ttc(50);  -- 60

⚠️ Points importants

  • • RETURNS définit le type de retour
  • • RETURN (sans S) renvoie la valeur
  • • Paramètres avec valeurs par défaut : DEFAULT
  • • $$ ... $$ = délimiteurs du corps

📦 Créer une Procédure 

CREATE OR REPLACE PROCEDURE augmenter_salaire(
    p_employe_id INTEGER,
    p_pourcentage NUMERIC
)
AS $$
BEGIN
    UPDATE employes
    SET salaire = salaire * (1 + p_pourcentage / 100)
    WHERE id = p_employe_id;

    RAISE NOTICE 'Salaire augmenté de %', p_pourcentage || '%';
END;
$$ LANGUAGE plpgsql;

Utilisation :

-- Appel avec CALL (obligatoire pour les procédures)
CALL augmenter_salaire(42, 10);

-- Avec paramètres nommés
CALL augmenter_salaire(p_employe_id => 42, p_pourcentage => 10);

↔️ Types de Paramètres

IN (défaut)

Lecture seule, ne peut pas être modifié

p_id IN INTEGER

OUT

Écriture seule, retourné à l'appelant

p_result OUT INTEGER

INOUT

Lecture + écriture

p_counter INOUT INTEGER

Exemple avec OUT :

CREATE OR REPLACE PROCEDURE get_employee_info(
    p_id IN INTEGER,
    p_name OUT VARCHAR,
    p_salary OUT NUMERIC
)
AS $$
BEGIN
    SELECT nom, salaire INTO p_name, p_salary
    FROM employes WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

-- Appel
CALL get_employee_info(42, NULL, NULL);
-- Retourne les valeurs dans les paramètres OUT

Le sens de circulation des paramètres

Choisis un mode : regarde dans quel sens la valeur voyage entre l'appelant et la procédure.

APPELANT CALL get_info(…) garde sa copie : 42 v_res = 99 v_cpt = 11 PROCÉDURE $$ corps $$ lecture seule p_res := 99 p_cpt := p_cpt + 1 p_id IN integer 42 p_res OUT integer 99 p_cpt INOUT integer 10

🛠️ Exemples Pratiques

1. Fonction avec gestion d'erreur

CREATE OR REPLACE FUNCTION diviser(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'Division par zéro interdite !';
    END IF;

    RETURN a / b;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Erreur: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. Fonction retournant une table

CREATE OR REPLACE FUNCTION get_top_salaries(n INTEGER)
RETURNS TABLE(nom VARCHAR, salaire NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT e.nom, e.salaire
    FROM employes e
    ORDER BY e.salaire DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT * FROM get_top_salaries(5);

3. Procédure avec transaction

CREATE OR REPLACE PROCEDURE transferer_fonds(
    p_from INTEGER,
    p_to INTEGER,
    p_montant NUMERIC
)
AS $$
BEGIN
    -- Débiter le compte source
    UPDATE comptes SET solde = solde - p_montant WHERE id = p_from;

    -- Créditer le compte destination
    UPDATE comptes SET solde = solde + p_montant WHERE id = p_to;

    -- Commit explicite (uniquement dans procédure)
    COMMIT;

    RAISE NOTICE 'Transfert de % effectué', p_montant;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$ LANGUAGE plpgsql;

✨ Bonnes Pratiques

✅ À faire

  • • Préfixer les paramètres (p_)
  • • Préfixer les variables (v_)
  • • Utiliser %TYPE et %ROWTYPE
  • • Gérer les exceptions
  • • Documenter avec des commentaires

❌ À éviter

  • • Modifier un paramètre IN
  • • Oublier le ; après RETURN
  • • SELECT sans INTO dans une fonction
  • • COMMIT/ROLLBACK dans une fonction
  • • Boucles infinies (oublier l'incrémentation)

🎯 Quiz - Teste tes connaissances !

Glisser pour continuer vers Les Triggers
⬇️
Bachelor Informatique