Introduction PL/pgSQL

Qu'est-ce que PL/pgSQL ?

Procedural Language for PostgreSQL - Extension procédurale

PL/pgSQL combine la puissance de SQL avec les structures de contrôle procédurales. Il permet d'écrire des blocs de code réutilisables et de manipuler les données de manière avancée.

✨ Avantages

Performance améliorée (moins d'aller-retours avec la base)
Code réutilisable (procédures, fonctions)
Gestion des erreurs avancée
Sécurité renforcée

1 Structure d'un Bloc PL/pgSQL

DECLARE
    -- Section de déclaration (OPTIONNELLE)
    -- Variables, constantes, curseurs, exceptions
    v_nom VARCHAR(50);
    v_age INTEGER := 25;
    c_tva CONSTANT NUMERIC := 0.20;
BEGIN
    -- Section d'exécution (OBLIGATOIRE)
    -- Instructions SQL et PL/pgSQL
    v_nom := 'Alice';
    RAISE NOTICE 'Bonjour %', v_nom;
EXCEPTION
    -- Section de gestion des erreurs (OPTIONNELLE)
    WHEN OTHERS THEN
        RAISE NOTICE 'Erreur: %', SQLERRM;
END;

📝 DECLARE

Déclaration des variables

Optionnelle

▶️ BEGIN...END

Corps du programme

Obligatoire

⚠️ EXCEPTION

Gestion des erreurs

Optionnelle

2 Variables et Types de Données

📌 Déclaration de variables

DECLARE
    -- Types numériques
    v_nombre NUMERIC;
    v_entier INTEGER := 100;
    v_prix NUMERIC(10,2) := 99.99;
    -- Types chaînes de caractères
    v_nom VARCHAR(50) := 'Dupont';
    v_description TEXT;
    -- Type date
    v_date_naissance DATE := '1990-01-15'::DATE;
    v_timestamp TIMESTAMP := NOW();
    -- Type booléen (uniquement en PL/pgSQL)
    v_actif BOOLEAN := TRUE;
    -- Type %TYPE (prend le type d'une colonne)
    v_employee_name employees.first_name%TYPE;
    -- Type %ROWTYPE (prend la structure d'une table)
    v_employee employees%ROWTYPE;
    -- Constantes
    c_max_tentatives CONSTANT INTEGER := 3;
BEGIN
    v_nombre := 42;
    RAISE NOTICE 'Nombre: %', v_nombre;
END;

💡 Conventions de nommage

v_ pour les variables
c_ pour les constantes
p_ pour les paramètres
cur_ pour les curseurs

🎮 Testez les types

Type de variable:

Nom de la variable:

3 Structures de Contrôle

🔀 IF...THEN...ELSE

DECLARE
    v_note INTEGER := 15;
    v_mention VARCHAR(20);
BEGIN
    IF v_note >= 16 THEN
        v_mention := 'Très bien';
    ELSIF v_note >= 14 THEN
        v_mention := 'Bien';
    ELSIF v_note >= 12 THEN
        v_mention := 'Assez bien';
    ELSIF v_note >= 10 THEN
        v_mention := 'Passable';
    ELSE
        v_mention := 'Échec';
    END IF;
    RAISE NOTICE 'Mention: %', v_mention;
END;

🎯 CASE

DECLARE
    v_jour INTEGER := 3;
    v_nom_jour VARCHAR(20);
BEGIN
    v_nom_jour := CASE v_jour
        WHEN 1 THEN 'Lundi'
        WHEN 2 THEN 'Mardi'
        WHEN 3 THEN 'Mercredi'
        WHEN 4 THEN 'Jeudi'
        WHEN 5 THEN 'Vendredi'
        WHEN 6 THEN 'Samedi'
        WHEN 7 THEN 'Dimanche'
        ELSE 'Invalide'
    END;
    RAISE NOTICE 'Jour: %', v_nom_jour;
END;

4 Les Boucles

🔁 LOOP Basique

DECLARE
    v_compteur INTEGER := 0;
BEGIN
    LOOP
        v_compteur := v_compteur + 1;
        RAISE NOTICE '%', v_compteur;
        EXIT WHEN v_compteur >= 5;
    END LOOP;
END;

🔄 WHILE LOOP

DECLARE
    v_compteur INTEGER := 0;
BEGIN
    WHILE v_compteur < 5 LOOP
        v_compteur := v_compteur + 1;
        RAISE NOTICE '%', v_compteur;
    END LOOP;
END;

📋 FOR LOOP

BEGIN
    -- Boucle ascendante
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Itération: %', i;
    END LOOP;
    -- Boucle descendante
    FOR i IN REVERSE 1..5 LOOP
        RAISE NOTICE 'Compte à rebours: %', i;
    END LOOP;
END;

⚡ Astuce

Utilisez EXIT pour sortir d'une boucle, ou CONTINUE pour passer à l'itération suivante.

5 SQL dans PL/pgSQL

🔍 SELECT INTO

DECLARE
    v_nom employees.last_name%TYPE;
    v_salaire employees.salary%TYPE;
BEGIN
    -- Sélectionner UNE SEULE ligne
    SELECT last_name, salary
    INTO v_nom, v_salaire
    FROM employees
    WHERE employee_id = 100;
    RAISE NOTICE '% gagne %€', v_nom, v_salaire;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE NOTICE 'Aucun employé trouvé';
    WHEN TOO_MANY_ROWS THEN
        RAISE NOTICE 'Plusieurs employés trouvés';
END;

✏️ INSERT, UPDATE, DELETE

BEGIN
    -- INSERT
    INSERT INTO employees (employee_id, last_name, salary)
    VALUES (999, 'Nouveau', 3000);
    -- UPDATE
    UPDATE employees
    SET salary = salary * 1.10
    WHERE department_id = 50;
    -- DELETE
    DELETE FROM employees
    WHERE employee_id = 999;
    -- Valider les changements
    COMMIT;
    RAISE NOTICE 'Lignes affectées: %', ROW_COUNT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

⚠️ Important

SELECT INTO doit retourner exactement 1 ligne
Utilisez GET DIAGNOSTICS ROW_COUNT = ROW_COUNT pour connaître le nombre de lignes affectées
N'oubliez pas le COMMIT pour valider vos changements