1 Les Procédures Stockées

Une procédure est un bloc PL/pgSQL nommé qui peut accepter des paramètres. Elle ne retourne pas de valeur directement (contrairement aux fonctions).

📦 Créer une procédure

-- Procédure simple
CREATE OR REPLACE PROCEDURE afficher_bonjour() AS $$
BEGIN
    RAISE NOTICE 'Bonjour !';
END;
$$ LANGUAGE plpgsql;
-- Appel
CALL afficher_bonjour();
-- Procédure avec paramètres IN
CREATE OR REPLACE PROCEDURE augmenter_salaire(
    p_employee_id IN INTEGER,
    p_pourcentage IN NUMERIC
) AS $$
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_pourcentage/100)
    WHERE employee_id = p_employee_id;
    COMMIT;
    RAISE NOTICE 'Salaire augmenté de %', p_pourcentage || '%';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'Employé introuvable';
END;
$$ LANGUAGE plpgsql;
-- Appel
CALL augmenter_salaire(100, 10);  -- Augmente de 10%

💡 Avantages des procédures

  • Code réutilisable et modulaire
  • Meilleure performance (code précompilé)
  • Sécurité accrue (contrôle des accès)
  • Maintenance facilitée

2 Modes de Paramètres

📥 IN

Paramètre en entrée (par défaut)

  • ✓ Lecture seule
  • ✗ Pas modifiable

📤 OUT

Paramètre en sortie

  • ✗ Pas de valeur initiale
  • ✓ Retourne une valeur

🔄 IN OUT

Entrée et sortie

  • ✓ Valeur initiale
  • ✓ Modifiable
CREATE OR REPLACE PROCEDURE calculer_stats(
    p_dept_id    IN  INTEGER,
    p_nb_emp     OUT INTEGER,
    p_salaire_moy OUT NUMERIC,
    p_salaire_max OUT NUMERIC
) AS $$
BEGIN
    SELECT COUNT(*),
           AVG(COALESCE(salary, 0)),
           MAX(salary)
    INTO p_nb_emp,
         p_salaire_moy,
         p_salaire_max
    FROM employees
    WHERE department_id = p_dept_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_nb_emp := 0;
        p_salaire_moy := 0;
        p_salaire_max := 0;
END;
$$ LANGUAGE plpgsql;
-- Appel avec paramètres OUT
DO $$
DECLARE
    v_nb INTEGER;
    v_moy NUMERIC;
    v_max NUMERIC;
BEGIN
    CALL calculer_stats(50, v_nb, v_moy, v_max);
    RAISE NOTICE 'Nombre d''employés: %', v_nb;
    RAISE NOTICE 'Salaire moyen: %', v_moy;
    RAISE NOTICE 'Salaire max: %', v_max;
END;
$$;

3 Les Fonctions

Une fonction est similaire à une procédure, mais elle RETOURNE obligatoirement une valeur. Elle peut être utilisée dans des requêtes SQL.

🔧 Créer une fonction

-- Fonction simple
CREATE OR REPLACE FUNCTION calculer_tva(
    p_prix_ht IN NUMERIC,
    p_taux_tva IN NUMERIC DEFAULT 20
) RETURNS NUMERIC AS $$
DECLARE
    v_prix_ttc NUMERIC;
BEGIN
    v_prix_ttc := p_prix_ht * (1 + p_taux_tva/100);
    RETURN v_prix_ttc;
END;
$$ LANGUAGE plpgsql;
-- Utilisation dans un bloc PL/pgSQL
DO $$
DECLARE
    v_prix NUMERIC;
BEGIN
    v_prix := calculer_tva(100);  -- Utilise le taux par défaut (20%)
    RAISE NOTICE 'Prix TTC: %', v_prix;
    v_prix := calculer_tva(100, 10);  -- Utilise un taux de 10%
    RAISE NOTICE 'Prix TTC réduit: %', v_prix;
END;
$$;
-- Utilisation dans une requête SQL
SELECT product_name,
       price,
       calculer_tva(price, 20) AS price_ttc
FROM products;

💼 Exemple: Calculer une prime

CREATE OR REPLACE FUNCTION calculer_prime(
    p_employee_id IN INTEGER
) RETURNS NUMERIC AS $$
DECLARE
    v_salaire NUMERIC;
    v_anciennete NUMERIC;
    v_prime NUMERIC := 0;
BEGIN
    -- Récupérer les infos de l'employé
    SELECT salary,
           EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date))
    INTO v_salaire, v_anciennete
    FROM employees
    WHERE employee_id = p_employee_id;
    -- Calculer la prime selon l'ancienneté
    IF v_anciennete >= 10 THEN
        v_prime := v_salaire * 0.15;
    ELSIF v_anciennete >= 5 THEN
        v_prime := v_salaire * 0.10;
    ELSIF v_anciennete >= 2 THEN
        v_prime := v_salaire * 0.05;
    END IF;
    RETURN v_prime;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 0;
END;
$$ LANGUAGE plpgsql;

4 Procédure vs Fonction

Critère Procédure Fonction
Valeur de retour ❌ Non (utilise OUT) ✅ Oui (RETURN)
Usage dans SQL ❌ Non ✅ Oui
Paramètres OUT ✅ Oui ✅ Oui (mais rare)
DML (INSERT/UPDATE) ✅ Recommandé ⚠️ Déconseillé
Objectif Exécuter une action Calculer/retourner une valeur

🎯 Règle générale

Utilisez une fonction pour calculer et retourner une valeur. Utilisez une procédure pour effectuer des actions (INSERT, UPDATE, DELETE).

5 Gestion et Commandes Utiles

Supprimer

DROP PROCEDURE nom_procedure;
DROP FUNCTION nom_fonction;

Voir le code source

SELECT definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname = 'nom_procedure'
AND n.nspname = 'public';

Lister toutes les procédures/fonctions

SELECT p.proname as name, 
       CASE WHEN p.prokind = 'f' THEN 'FUNCTION'
            WHEN p.prokind = 'p' THEN 'PROCEDURE'
       END as type
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
AND p.prokind IN ('f', 'p')
ORDER BY p.proname;

Vérifier les erreurs de compilation

-- PostgreSQL affiche les erreurs directement lors de la création
-- Pas d'équivalent direct à SHOW ERRORS
-- Les erreurs sont visibles dans les logs PostgreSQL

💪 Exercice Pratique

Créez une fonction qui calcule l'âge d'une personne

Spécifications:

  • Nom: calculer_age
  • Paramètre: date de naissance (DATE)
  • Retour: âge en années (NUMBER)
  • Utiliser MONTHS_BETWEEN et SYSDATE
🏠 Retour au Hub Mukbang
Glisser pour continuer vers Exercices Curseurs
⬇️