En programmation procédurale de bases de données, les variables sont des conteneurs en mémoire vive qui conservent des informations temporaires tout au long de l'exécution d'un script ou d'une transaction. Elles permettent de mémoriser des compteurs, des totaux intermédiaires, des clés primaires générées ou des critères de filtrage dynamiques.
1. Typage des variables en PL/pgSQL#
Chaque variable déclarée en PL/pgSQL doit obligatoirement posséder un type de données défini à l'avance.
A. Les types scalaires usuels#
| Type SQL | Description | Exemple de valeur |
|---|---|---|
INTEGER / BIGINT |
Nombres entiers (compteurs, identifiants) | 42, 100523 |
NUMERIC(p, s) |
Nombre décimal à précision exacte (prix, monnaie) | 149.99 |
VARCHAR(n) / TEXT |
Chaîne de caractères de longueur bornée ou libre | 'Terminé', 'Paris' |
BOOLEAN |
Valeur booléenne (TRUE, FALSE, ou NULL) |
TRUE |
DATE |
Date calendaire | '2024-03-01' |
TIMESTAMP |
Horodatage avec heure, minute, seconde | clock_timestamp() |
B. Les types magiques de PostgreSQL : %TYPE et %ROWTYPE#
Plutôt que d'écrire en dur le type d'une variable au risque d'incohérence si le schéma de la table évolue, PostgreSQL propose deux attributs remarquables :
nom_table.colonne%TYPE: Copie automatiquement le type exact de la colonne ciblée.nom_table%ROWTYPE: Définit une variable composite capable de contenir une ligne entière de la table avec tous ses champs.RECORD: Variable de type composite générique, dont la structure s'adapte à n'importe quel résultat de requêteSELECTà l'exécution.
2. Déclaration et initialisation (DECLARE)#
Toutes les variables doivent être définies dans la section DECLARE, située impérativement avant le mot-clé BEGIN.
DO
$$
DECLARE
-- Déclaration simple (initialisée à NULL par défaut)
v_statut VARCHAR(20);
-- Déclaration avec valeur par défaut immédiate
v_taux_tva NUMERIC(4, 2) := 0.20;
v_nb_tentatives INTEGER DEFAULT 0;
-- Déclaration d'une constante (valeur immuable)
c_seuil_alerte CONSTANT NUMERIC := 10000.00;
BEGIN
RAISE NOTICE 'Taux de TVA appliqué : %', v_taux_tva;
RAISE NOTICE 'Seuil critique d alerte : % €', c_seuil_alerte;
END;
$$ LANGUAGE plpgsql;
[!NOTE] En PL/pgSQL, l'opérateur d'affectation standard est
:=(deux-points égal). Bien que le signe simple=soit également accepté par le compilateur, privilégiez systématiquement:=pour éviter toute confusion visuelle avec le test d'égalité logique.
3. Affectation dynamique à partir d'une requête SQL (SELECT INTO)#
En Data Engineering, on affecte rarement des valeurs en dur. On souhaite presque toujours extraire une métrique depuis une table de la base pour la stocker dans une variable.
C'est le rôle fondamental de la clause SELECT ... INTO :
DO
$$
DECLARE
v_total_commandes INTEGER;
v_ca_global NUMERIC(12, 2);
v_derniere_date DATE;
BEGIN
-- Extraction multi-colonnes en une seule requête optimisée
SELECT
COUNT(*),
COALESCE(SUM(montant), 0),
MAX(date_commande)
INTO
v_total_commandes,
v_ca_global,
v_derniere_date
FROM fct_commandes
WHERE date_commande >= CURRENT_DATE - INTERVAL '30 days';
RAISE NOTICE 'Bilan sur 30 jours :';
RAISE NOTICE '-> Nombre de commandes : %', v_total_commandes;
RAISE NOTICE '-> CA généré : % €', v_ca_global;
RAISE NOTICE '-> Dernière transaction: %', v_derniere_date;
END;
$$ LANGUAGE plpgsql;
La variable système FOUND#
Après l'exécution d'un SELECT INTO, PostgreSQL positionne automatiquement une variable booléenne interne nommée FOUND :
* Si au moins une ligne correspondante a été trouvée, FOUND vaut TRUE.
* Si la requête n'a renvoyé aucun enregistrement, FOUND vaut FALSE et les variables de destination prennent la valeur NULL.
4. Portée des variables (Scope)#
Chaque sous-bloc délimité par DECLARE ... BEGIN ... END; possède son propre espace de noms. Une variable déclarée dans un bloc externe est accessible dans les blocs internes, mais une variable locale définie dans un bloc interne cesse d'exister dès la sortie de celui-ci :
DO
$$
DECLARE
v_niveau_global TEXT := 'Racine';
BEGIN
RAISE NOTICE 'Niveau : %', v_niveau_global;
-- Début d'un sous-bloc interne
DECLARE
v_niveau_local TEXT := 'Sous-processus';
BEGIN
RAISE NOTICE 'Accès aux deux : % / %', v_niveau_global, v_niveau_local;
END;
-- Ici, v_niveau_local n'est plus accessible !
END;
$$ LANGUAGE plpgsql;
5. Mise en pratique : Extraction et calcul de performance#
Contexte#
Soit une table ventes contenant les colonnes id_vente, client_id, montant, et date_vente.
Objectif#
Écrire un bloc anonyme qui extrait :
1. Le chiffre d'affaires total de la table.
2. Le montant de la commande la plus élevée.
3. Le montant moyen par commande.
4. Calcule le montant TTC prévisionnel en appliquant un coefficient de 1.20.
DO
$$
DECLARE
v_ca_ht NUMERIC(12, 2);
v_max_vente NUMERIC(12, 2);
v_panier_moyen NUMERIC(12, 2);
v_taux_tva CONSTANT NUMERIC := 1.20;
v_ca_ttc NUMERIC(12, 2);
BEGIN
-- 1. Requête d'agrégation
SELECT
COALESCE(SUM(montant), 0),
COALESCE(MAX(montant), 0),
COALESCE(AVG(montant), 0)
INTO
v_ca_ht,
v_max_vente,
v_panier_moyen
FROM ventes;
-- 2. Calcul arithmétique procédural
v_ca_ttc := v_ca_ht * v_taux_tva;
-- 3. Affichage du rapport d'activité
RAISE NOTICE '========= RAPPORT DE VENTES =========';
RAISE NOTICE 'Chiffre d affaires HT : % €', v_ca_ht;
RAISE NOTICE 'Chiffre d affaires TTC : % €', v_ca_ttc;
RAISE NOTICE 'Plus grosse vente : % €', v_max_vente;
RAISE NOTICE 'Panier moyen unitaire : % €', ROUND(v_panier_moyen, 2);
RAISE NOTICE '======================================';
END;
$$ LANGUAGE plpgsql;
Les variables sont le socle de toute logique de calcul. Dans la prochaine leçon, nous apprendrons à aiguiller nos scripts grâce aux Structures Conditionnelles (IF et CASE).
Commentaires