Informatique · PL/pgSQL et PL/SQL

Procédures et fonctions

On range un calcul ou une action dans la base, sous un nom. Une fonction renvoie une valeur, une procédure agit.

Déjà à l'aise ? Aller au quiz

Le principe

Un prix TTC, une hausse de salaire, un transfert entre deux comptes : ces opérations reviennent partout. Au lieu de les réécrire dans chaque programme, on les range dans la base, sous un nom.

appelant fonction calculer_ttc 100 108.10 appelant procédure augmenter_salaire 42, 10 aucun retour employes : Léa 2000 → 2200

Une fonction renvoie une valeur. Une procédure agit.

Après cette fiche, tu sais

  • choisir une fonction ou une procédure, et l'appeler comme il faut ;
  • écrire une fonction complète, avec RETURNS et RETURN ;
  • lire les modes IN, OUT et INOUT ;
  • gérer erreurs et transactions, et reconnaître les outils d'Oracle.
Une procédure n'a pas de valeur de retour. Peut-elle quand même renvoyer quelque chose à celui qui l'appelle ? Garde ta réponse en tête : on la vérifie dans la notion 3.

Oui, avec des paramètres OUT ? Ou jamais ?

Bonne intuition : la réponse est dans la notion 3.

Une valeur, ou une action

Notion 1 · Fonction ou procédure

Obtenir une valeur, ou agir

En PostgreSQL, une fonction (CREATE FUNCTION) renvoie une valeur et s'utilise à l'intérieur d'une requête. Une procédure (CREATE PROCEDURE, depuis PostgreSQL 11) n'a pas de valeur de retour, s'appelle seule avec CALL, et peut valider ou annuler des transactions pendant son exécution.1

La fonction

Elle renvoie une valeur à la requête qui l'appelle. Sa valeur peut donc aller dans une colonne.

une valeur qui revient dans la requête requête SELECT f(100) fonction RETURN … 100 108.10

La procédure

Elle ne renvoie rien à l'appelant. Elle agit sur les données, puis s'arrête.

appel CALL …(42, 10) procédure UPDATE employes 2000 → 2200 aucune valeur ne revient
FUNCTIONPROCEDURE
Valeur de retouroui : RETURNS type (éventuellement void)non, mais des paramètres OUT possibles
Appeldans une requête : SELECT f(…)seule : CALL p(…)
COMMIT / ROLLBACKjamaisoui, sous conditions (notion 6)
Usage typiquecalculer, transformer, liremodifier des données, traitements par lots

Le trajet de la donnée

Fais défiler : un appel de fonction, puis un appel de procédure. Regarde ce qui revient, ou pas, vers l'appelant.

Scène 1 · Trajet

Ce qui revient vers l'appelant

appelant SELECT calculer_ttc(100) → 108.10 CALL augmenter_ salaire(42, 10) aucune valeur fonction calculer_ttc RETURN round(…) procédure augmenter_salaire UPDATE employes Léa · 2000.00 100 108.10 42, 10

Exercice · à toi

Fonction ou procédure ?

Cinq besoins. À chaque fois, une contrainte décide pour toi : laquelle faut-il écrire ?

Écrire, puis appeler

Notion 2 · Écrire une fonction

RETURNS dans l'en-tête, RETURN dans le corps

Une fonction qui calcule un prix TTC, avec par défaut le taux normal de la TVA suisse, 8,1 %.5

Les quatre morceaux

en-tête : CREATE FUNCTION calculer_ttc(…) RETURNS NUMERIC : le type renvoyé corps : BEGIN RETURN round(…, 2); END; LANGUAGE plpgsql; : la langue du corps

1. Nommer

CREATE OR REPLACE FUNCTION, un nom, des paramètres typés.

2. Déclarer le retour

RETURNS NUMERIC, dans l'en-tête, avant le corps.

3. Calculer, puis RETURN

BEGIN, le calcul, RETURN la valeur, END.

4. Dire la langue

LANGUAGE plpgsql ferme la définition.

Créer la fonctionPL/pgSQL
CREATE OR REPLACE FUNCTION calculer_ttc(prix NUMERIC, tva NUMERIC DEFAULT 0.081)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN round(prix * (1 + tva), 2);   -- arrondi au centime
END;
$$ LANGUAGE plpgsql;
L'utiliserSQL
SELECT calculer_ttc(100);                 -- 108.10
SELECT calculer_ttc(100, 0.026);          -- 102.60 (taux réduit)
SELECT calculer_ttc(tva => 0.026, prix => 20);   -- 20.52, paramètres nommés
SELECT nom, calculer_ttc(prix) AS prix_ttc FROM produits;

-- dans un bloc PL/pgSQL : affectation à une variable
DO $$
DECLARE v_total NUMERIC;
BEGIN
    v_total := calculer_ttc(100, 0.026);
    RAISE NOTICE 'v_total = %', v_total;    -- v_total = 102.60
END $$;
RETURNS et RETURN

RETURNS NUMERIC, dans l'en-tête, déclare le type renvoyé ; RETURN …;, dans le corps, renvoie la valeur et termine la fonction.

DEFAULT

Un paramètre avec valeur par défaut peut être omis à l'appel. Tous les paramètres d'entrée qui le suivent doivent aussi en avoir une.1

$$ … $$

Le corps est une chaîne délimitée par des dollars : elle peut contenir des apostrophes sans qu'on doive les doubler.3

CREATE OR REPLACE

Remplace une fonction existante sans casser les vues ou déclencheurs qui l'utilisent ; mais on ne peut pas changer ainsi son type de retour.3

Piège : la fonction qui oublie son RETURN

PostgreSQL accepte la création d'une fonction RETURNS integer dont une branche n'a pas de RETURN ; l'erreur tombe à l'exécution : control reached end of function without RETURN. Seules les fonctions RETURNS void ou à paramètres OUT peuvent s'en passer.2

Exercice · remettre dans l'ordre

Reconstruis la fonction

Les sept lignes de calculer_ttc ont été mélangées. Touche une ligne, puis celle avec laquelle l'échanger.

Entrer, sortir, aller-retour

Notion 3 · Procédures et paramètres

CALL, et des valeurs qui entrent ou sortent

Une procédure s'appelle avec CALL. Chaque paramètre a un sens de circulation : il entre, il sort, ou il fait les deux.

Le sens

appelant procédure IN : 42 OUT : Noah INOUT : 10 → 11
La table des exemplesemployes(id, nom, salaire) contient : 42 Léa 2000, 7 Noah 5200, 3 Zoé 6100, 15 Lina 4800, 9 Elias 4300, 11 Maé 3900. Les résultats indiqués en commentaire ont été obtenus avec elle.
Une procédure qui agitPL/pgSQL
CREATE OR REPLACE PROCEDURE augmenter_salaire(
    p_employe_id INTEGER,
    p_pourcentage NUMERIC
)
AS $$
BEGIN
    UPDATE employes
    SET salaire = round(salaire * (1 + p_pourcentage / 100), 2)
    WHERE id = p_employe_id;

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

CALL augmenter_salaire(42, 10);                                 -- Léa : 2000.00 → 2200.00
CALL augmenter_salaire(p_employe_id => 42, p_pourcentage => 10); -- paramètres nommés
Piège : appeler une procédure comme une fonction

SELECT augmenter_salaire(42, 10); échoue : augmenter_salaire(integer, integer) is a procedure. Une procédure s'appelle avec CALL, jamais dans une requête.

Les trois modes de paramètres

IN (par défaut)

La valeur entre. En PL/pgSQL, le paramètre est une variable locale : on peut la réaffecter, mais l'appelant n'en voit rien.

OUT

La valeur sort : la procédure l'écrit, l'appelant la récupère. À l'appel direct, on écrit NULL à sa place.1

INOUT

Aller-retour : la valeur entre, la procédure peut la modifier, et elle ressort.

Résultat d'un CALL

Si la procédure a des paramètres OUT ou INOUT, CALL renvoie une ligne avec leurs valeurs.1

Le sens de circulation

Fais défiler : un paramètre IN, puis OUT, puis INOUT. Les valeurs ont été vérifiées dans PostgreSQL 18.

Scène 2 · Paramètres

IN : la valeur entre

appelant procédure p IN integer v = 42 p := p + 1 p = 43, copie p_nom OUT varchar v_nom = ? écrit 'Noah' p_cpt INOUT integer v = 10 p_cpt + 1 42

Réponse à ma question : oui. Une procédure n'a pas de RETURN de valeur, mais ses paramètres OUT et INOUT reviennent à l'appelant.
OUT en pratiquePL/pgSQL
CREATE OR REPLACE PROCEDURE infos_employe(
    p_id IN INTEGER,
    p_nom OUT VARCHAR,
    p_salaire OUT NUMERIC
)
AS $$
BEGIN
    SELECT nom, salaire INTO p_nom, p_salaire
    FROM employes WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

CALL infos_employe(7, NULL, NULL);   -- une ligne : p_nom = Noah, p_salaire = 5200

DO $$
DECLARE v_nom VARCHAR; v_sal NUMERIC;
BEGIN
    CALL infos_employe(7, v_nom, v_sal);
    RAISE NOTICE '% gagne %', v_nom, v_sal;  -- Noah gagne 5200
END $$;
Et en Oracle PL/SQL ?Les modes s'écrivent IN, OUT et IN OUT (en deux mots). Un paramètre IN y agit comme une constante : le sous-programme ne peut pas changer sa valeur, le compilateur refuse l'affectation.4 PostgreSQL, lui, accepte l'affectation mais la garde locale.

Quand le bloc échoue

Notion 4 · Intercepter une erreur

BEGIN … EXCEPTION WHEN … THEN

Un bloc PL/pgSQL peut avoir une section EXCEPTION : si une instruction lève une erreur, PostgreSQL abandonne le reste du bloc, annule ce que le bloc a modifié et cherche une clause WHEN correspondante.2

Deux chemins

RETURN a / b tout va bien → 2.5 a / b, b = 0 erreur levée WHEN division_by_zero RETURN NULL

Le chemin d'une erreur

Fais défiler : la flèche suit l'exécution, ligne par ligne.

appel SELECT diviser(10, 4); → 2.5000000000000000
Étape 1 sur 5 · tout va bien

diviser(10, 4) : la ligne RETURN a / b; s'exécute et renvoie 2.5. La section EXCEPTION n'est jamais visitée.

Étape 2 sur 5 · l'erreur

diviser(10, 0) : a / b lève l'erreur division_by_zero. Le reste du bloc est abandonné.

Étape 3 sur 5 · le saut

L'exécution saute à la section EXCEPTION, et la clause WHEN division_by_zero correspond.

Étape 4 sur 5 · le rattrapage

Le gestionnaire affiche un avis et renvoie NULL : l'appel réussit, avec un résultat vide.

Étape 5 sur 5 · sans EXCEPTION

Sans cette section, l'erreur remonterait jusqu'à l'appelant : toute la requête échouerait avec division by zero.

Ne pas en mettre partoutUn bloc avec une section EXCEPTION est nettement plus coûteux à exécuter qu'un bloc sans : on ne l'utilise que si on sait quoi faire de l'erreur.2 WHEN OTHERS, qui attrape tout, cache souvent de vrais bogues.

Plusieurs lignes, une fonction

Notion 5 · Renvoyer plusieurs lignes

Une fonction qui se lit comme une table

RETURNS TABLE (…) (ou RETURNS SETOF type) déclare un résultat de plusieurs lignes. Dans le corps, RETURN QUERY et RETURN NEXT ne quittent pas la fonction : ils ajoutent des lignes au résultat, et c'est la fin de la fonction, ou un RETURN sans argument, qui le renvoie.2

Ligne après ligne

nom salaire Zoé 6100 Noah 5200 Lina 4800 RETURN NEXT ajoute une ligne à la fois
Les n meilleurs salairesPL/pgSQL
CREATE OR REPLACE FUNCTION get_top_salaries(n INTEGER)
RETURNS TABLE(nom VARCHAR, salaire NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT e.nom, e.salaire      -- alias e : évite le conflit avec nom et salaire
    FROM employes e
    ORDER BY e.salaire DESC
    LIMIT n;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_top_salaries(3);
  nom  | salaire
-------+---------
 Zoé   |    6100
 Noah  |    5200
 Lina  |    4800
Piège : le conflit de noms

Les colonnes de RETURNS TABLE (nom, salaire) deviennent des variables dans le corps. Une requête qui écrit nom sans préfixe devient ambiguë : on qualifie les colonnes par un alias (e.nom).

Valider ou annuler

Notion 6 · Transactions

COMMIT dans une procédure, jamais dans une fonction

Une procédure appelée par CALL (comme un bloc DO) peut terminer la transaction en cours avec COMMIT ou ROLLBACK ; une nouvelle commence aussitôt.2

Trois limites : jamais dans une fonction ; jamais dans un bloc qui a une section EXCEPTION, qui forme une sous-transaction ; jamais si le CALL est lui-même dans une transaction ouverte par BEGIN.1 Dans ces trois cas, PostgreSQL 18 répond invalid transaction termination.

Tout ou rien

UPDATE compte 1 : −200 UPDATE compte 2 : +200 COMMIT ROLLBACK en cas d'erreur aucun compte n'est débité à moitié
Un transfert entre deux comptesPL/pgSQL
-- comptes(id, solde) avec la contrainte CHECK (solde >= 0)
CREATE OR REPLACE PROCEDURE transferer_fonds(
    p_from INTEGER,
    p_to INTEGER,
    p_montant NUMERIC
)
AS $$
BEGIN
    UPDATE comptes SET solde = solde - p_montant WHERE id = p_from;
    UPDATE comptes SET solde = solde + p_montant WHERE id = p_to;
    COMMIT;
    RAISE NOTICE 'Transfert de % effectué', p_montant;
END;
$$ LANGUAGE plpgsql;

Deux transferts, un refusé

Fais défiler : un transfert de 200 CHF réussit, puis un transfert de 400 CHF viole la contrainte CHECK. Regarde les soldes.

Scène 3 · Transaction

Avant les transferts

soldes compte 1 500 compte 2 100 échelle : 600 CHF pour toute la barre

Remarque le deuxième transfert : l'erreur arrive au premier UPDATE, avant le COMMIT. Toute la transaction en cours est annulée, aucun compte n'est débité à moitié.
Exercice · classer

Accepté ou refusé par PostgreSQL ?

Sept situations, toutes testées dans PostgreSQL 18. Range chacune.

Le même sujet, chez Oracle

Notion 7 · Oracle PL/SQL

Le même sujet, d'autres mots

Oracle PL/SQL range lui aussi procédures et fonctions dans la base. Les idées sont les mêmes : IN, OUT, IN OUT, RETURN. Ce qui change, ce sont la syntaxe et les outils.4

Correspondances

PostgreSQL Oracle renvoi RETURNS NUMERIC RETURN NUMBER appel CALL p(…) EXEC p(…) trace RAISE NOTICE DBMS_OUTPUT.PUT_LINE erreur RAISE EXCEPTION RAISE_APPLICATION_ERROR
Une procédure OraclePL/SQL
CREATE OR REPLACE PROCEDURE augmenter_salaire (
    p_employe_id IN NUMBER,
    p_pourcentage IN NUMBER
) AS
BEGIN
    UPDATE employes
    SET salaire = ROUND(salaire * (1 + p_pourcentage / 100), 2)
    WHERE id = p_employe_id;
END augmenter_salaire;
/

EXEC augmenter_salaire(42, 10);   -- Léa : 2000 → 2200

En Oracle, AS (ou IS) ouvre le corps, END nom; le ferme, et la barre oblique / termine l'instruction dans SQL*Plus ou SQL Developer. EXEC est un raccourci pour un bloc BEGIN … END;.

Le sens des modes

En Oracle, IN OUT s'écrit en deux mots. Un paramètre IN est en lecture seule : l'affecter est refusé à la compilation.4

appelant v = 10 procédure p_cpt := p_cpt + 1 IN OUT : 10 11 la valeur entre, puis ressort modifiée
OUT : une ligne revientPL/SQL
CREATE OR REPLACE PROCEDURE infos_employe (
    p_id IN NUMBER,
    p_nom OUT VARCHAR2,
    p_salaire OUT NUMBER
) AS
BEGIN
    SELECT nom, salaire INTO p_nom, p_salaire
    FROM employes WHERE id = p_id;
END infos_employe;
/

-- SET SERVEROUTPUT ON pour voir DBMS_OUTPUT dans SQL*Plus
DECLARE
    v_nom VARCHAR2(50);
    v_sal NUMBER;
BEGIN
    infos_employe(7, v_nom, v_sal);
    DBMS_OUTPUT.PUT_LINE(v_nom || ' gagne ' || v_sal);   -- Noah gagne 5200
END;
/
IN OUT : l'aller-retourPL/SQL
CREATE OR REPLACE PROCEDURE incrementer (p_cpt IN OUT NUMBER) AS
BEGIN
    p_cpt := p_cpt + 1;
END incrementer;
/

DECLARE
    v NUMBER := 10;
BEGIN
    incrementer(v);
    DBMS_OUTPUT.PUT_LINE(v);   -- 11
END;
/

Un package

Un package regroupe des sous-programmes sous un nom. La spécification montre ce qui est public ; le corps écrit le code, et peut cacher des fonctions privées.6

appelant calculer_ttc spécification (public) calculer_ttc · augmenter_salaire corps (caché) arrondir · code des deux
Spécification : ce qui est publicPL/SQL
CREATE OR REPLACE PACKAGE paie AS
    FUNCTION calculer_ttc (p_prix IN NUMBER, p_tva IN NUMBER DEFAULT 0.081) RETURN NUMBER;
    PROCEDURE augmenter_salaire (p_employe_id IN NUMBER, p_pourcentage IN NUMBER);
END paie;
/
Corps : le code, et une aide privéePL/SQL
CREATE OR REPLACE PACKAGE BODY paie AS
    FUNCTION arrondir (p_montant IN NUMBER) RETURN NUMBER IS   -- privée : absente de la spécification
    BEGIN
        RETURN ROUND(p_montant, 2);
    END arrondir;

    FUNCTION calculer_ttc (p_prix IN NUMBER, p_tva IN NUMBER DEFAULT 0.081) RETURN NUMBER IS
    BEGIN
        RETURN arrondir(p_prix * (1 + p_tva));
    END calculer_ttc;

    PROCEDURE augmenter_salaire (p_employe_id IN NUMBER, p_pourcentage IN NUMBER) IS
    BEGIN
        UPDATE employes
        SET salaire = arrondir(salaire * (1 + p_pourcentage / 100))
        WHERE id = p_employe_id;
    END augmenter_salaire;
END paie;
/

SELECT paie.calculer_ttc(100) FROM dual;   -- 108.1 : Oracle n'écrit pas le zéro final
EXEC paie.augmenter_salaire(42, 10);

Qui peut exécuter ?

Par défaut, une procédure s'exécute avec les droits de son propriétaire (AUTHID DEFINER) : l'appelant n'a besoin que du droit EXECUTE. Avec AUTHID CURRENT_USER, placé avant AS, ce sont les droits de l'appelant qui comptent.76

app_rh EXECUTE paie propriétaire employes UPDATE l'appelant n'a pas besoin de UPDATE sur employes
Donner EXECUTE, pas les tablesSQL
GRANT EXECUTE ON paie TO app_rh;

Un code d'erreur à toi

RAISE_APPLICATION_ERROR lève une erreur avec un numéro choisi par toi : il doit être compris entre -20000 et -20999. L'appelant voit ORA-20001 suivi de ton message.8

numéros acceptés -20999 -20000 ORA-20001: Note 7 hors de l'échelle 1 à 6 SQLCODE vaut -20001
RAISE_APPLICATION_ERRORPL/SQL
CREATE OR REPLACE PROCEDURE ajouter_note (p_eleve IN NUMBER, p_note IN NUMBER) AS
BEGIN
    IF p_note < 1 OR p_note > 6 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Note ' || p_note || ' hors de l''échelle 1 à 6');
    END IF;
    INSERT INTO notes (eleve_id, note) VALUES (p_eleve, p_note);
END ajouter_note;
/

EXEC ajouter_note(3, 7);
-- ORA-20001: Note 7 hors de l'échelle 1 à 6
Nommer l'erreur, puis la rattraperPL/SQL
DECLARE
    e_hors_echelle EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_hors_echelle, -20001);
BEGIN
    ajouter_note(3, 7);
EXCEPTION
    WHEN e_hors_echelle THEN
        DBMS_OUTPUT.PUT_LINE('Refusé, code ' || SQLCODE);   -- Refusé, code -20001
END;
/

Oracle a aussi ses exceptions prédéfinies : ZERO_DIVIDE pour la division par zéro, NO_DATA_FOUND quand un SELECT INTO ne renvoie aucune ligne, TOO_MANY_ROWS quand il en renvoie trop.9

1. Grouper dans un package

Une spécification publique, un corps qui cache le reste.

2. Donner EXECUTE

L'appelant reçoit le droit d'exécuter, pas l'accès aux tables.

3. Un code par erreur métier

Un numéro entre -20000 et -20999 pour chaque cas à signaler.

Six habitudes de pro

Habitudes de pro

Six bonnes pratiques

  • préfixer les paramètres (p_) et les variables (v_) : ils ne se confondent plus avec les colonnes ;
  • déclarer les variables avec %TYPE : v_sal employes.salaire%TYPE suit le type de la colonne s'il change un jour.3
  • ne pas réaffecter un paramètre IN : c'est trompeur en PL/pgSQL et refusé en Oracle.
colonne employes.salaire variable v_sal employes.salaire%TYPE même type : si la colonne change, la variable suit

Les décimales

Les calculs en NUMERIC allongent vite le nombre de décimales : on arrondit les montants avec round(…, 2).

1,081 × 1,081 1,168561 1,17 round(…, 2) ramène les décimales à deux

Ignorer ou garder

  • pour une requête dont on ignore le résultat, écrire PERFORM : un SELECT sans INTO provoque l'erreur query has no destination for result data ;
  • n'intercepter que les erreurs qu'on sait traiter, et nommer la condition (division_by_zero) plutôt que OTHERS.
PERFORM count(*) résultat ignoré SELECT count(*) INTO v_nb résultat gardé variable v_nb (si INTO)

Entraînement

Trois exercices corrigés

Tables utilisées : eleves(id, nom, classe) et notes(eleve_id, note), notes de 1 à 6. Tous les corrigés ont été exécutés dans PostgreSQL 18.

5,5 4,5 4 5 4,75

Exercice 3 · le piège

La fonction se crée sans erreur, puis plante à l'appel, pour deux raisons.

SELECT count(*) … sans INTO query has no destination for result data fin sans RETURN without RETURN RETURN v_nb la correction
1Écris une fonction moyenne_classe(p_classe) qui renvoie la moyenne des notes d'une classe, arrondie au centième.Voir le corrigé
CREATE OR REPLACE FUNCTION moyenne_classe(p_classe TEXT)
RETURNS NUMERIC AS $$
DECLARE
    v_moyenne NUMERIC;
BEGIN
    SELECT round(avg(n.note), 2) INTO v_moyenne
    FROM notes n JOIN eleves e ON e.id = n.eleve_id
    WHERE e.classe = p_classe;
    RETURN v_moyenne;
END;
$$ LANGUAGE plpgsql;

SELECT moyenne_classe('2M3');   -- 4.75 avec les notes 5.5, 4.5, 4 et 5

Pour une classe sans notes, avg renvoie NULL, et la fonction aussi.

2Écris une procédure ajouter_note(p_eleve, p_note) qui refuse une note hors de l'échelle 1 à 6, puis l'enregistre.Voir le corrigé
CREATE OR REPLACE PROCEDURE ajouter_note(p_eleve INTEGER, p_note NUMERIC)
AS $$
BEGIN
    IF p_note < 1 OR p_note > 6 THEN
        RAISE EXCEPTION 'Note % hors de l''échelle 1 à 6', p_note;
    END IF;
    INSERT INTO notes (eleve_id, note) VALUES (p_eleve, p_note);
END;
$$ LANGUAGE plpgsql;

CALL ajouter_note(3, 5.5);   -- enregistrée
CALL ajouter_note(3, 7);     -- ERREUR : Note 7 hors de l'échelle 1 à 6

Dans une chaîne SQL, l'apostrophe se double (l''échelle). Une procédure convient : on agit sur la table, sans valeur à renvoyer.

3Cette fonction se crée sans erreur, mais plante dès qu'on l'appelle. Pourquoi, et comment la corriger ?Voir le corrigé
CREATE OR REPLACE FUNCTION nb_eleves(p_classe TEXT)
RETURNS INTEGER AS $$
BEGIN
    SELECT count(*) FROM eleves WHERE classe = p_classe;
END;
$$ LANGUAGE plpgsql;

Deux défauts : le SELECT n'a pas d'INTO (erreur query has no destination for result data), et il manque le RETURN. Version corrigée :

CREATE OR REPLACE FUNCTION nb_eleves(p_classe TEXT)
RETURNS INTEGER AS $$
DECLARE
    v_nb INTEGER;
BEGIN
    SELECT count(*) INTO v_nb FROM eleves WHERE classe = p_classe;
    RETURN v_nb;
END;
$$ LANGUAGE plpgsql;

À retenir

Quatre idées à garder

Fonction RETURN une valeur s'utilise dans une requête Procédure CALL, pour agir pas de valeur de retour Erreur EXCEPTION WHEN … THEN rattraper si on sait faire Transaction COMMIT · ROLLBACK dans une procédure seulement
FonctionRETURNS dans l'en-tête, RETURN dans le corps, s'utilise dans une requête.
Procédurepas de valeur de retour, s'appelle seule avec CALL.
ParamètresIN entre (copie locale en PL/pgSQL), OUT sort, INOUT fait l'aller-retour.
DEFAULTparamètre facultatif ; ceux qui suivent en ont aussi un.
ErreursEXCEPTION WHEN condition THEN ; bloc coûteux, à réserver aux erreurs qu'on sait traiter.
Plusieurs lignesRETURNS TABLE ou SETOF ; RETURN QUERY et RETURN NEXT ajoutent des lignes.
TransactionsCOMMIT et ROLLBACK seulement dans une procédure appelée par CALL, hors bloc EXCEPTION et hors transaction ouverte.
PiègesRETURN oublié, SELECT sans INTO, procédure appelée par SELECT.
Dernière étape : le quiz. Douze questions tirées au hasard, différentes à chaque partie. Si tu te trompes, lis bien l'explication.
Quiz final

Teste-toi

Sources

  1. PostgreSQL 18 Documentation : User-Defined Procedures (différences avec les fonctions), CALL (paramètres OUT, transaction ouverte), CREATE PROCEDURE (modes, valeurs par défaut). procédures, CALL, CREATE PROCEDURE (postgresql.org)
  2. PostgreSQL 18 Documentation, PL/pgSQL : Transaction Management et Control Structures (RETURN, RETURN QUERY, interception des erreurs, coût d'un bloc EXCEPTION). transactions, structures de contrôle (postgresql.org)
  3. PostgreSQL 18 Documentation : CREATE FUNCTION (OR REPLACE, chaînes entre dollars) et PL/pgSQL Declarations (%TYPE). CREATE FUNCTION, déclarations (postgresql.org)
  4. Oracle, Database PL/SQL Language Reference 19c, « Subprogram Parameters » (modes IN, OUT, IN OUT). docs.oracle.com
  5. Confédération suisse, Loi fédérale régissant la taxe sur la valeur ajoutée (LTVA), RS 641.20, art. 25 (taux normal 8,1 %, taux réduit 2,6 %). fedlex.admin.ch
  6. Oracle, SQL Language Reference 19c, « CREATE PROCEDURE » (modes IN, OUT, IN OUT ; clause AUTHID), « CREATE PACKAGE », « CREATE PACKAGE BODY » et « GRANT » (privilège EXECUTE). CREATE PROCEDURE, CREATE PACKAGE, GRANT (docs.oracle.com)
  7. Oracle, Database PL/SQL Language Reference 19c, « Invoker's Rights and Definer's Rights (AUTHID Property) ». droits de l'appelant et du propriétaire (docs.oracle.com)
  8. Oracle, Database PL/SQL Packages and Types Reference 19c, paquetage DBMS_STANDARD, « RAISE_APPLICATION_ERROR » (numéros de -20000 à -20999). DBMS_STANDARD (docs.oracle.com)
  9. Oracle, Database PL/SQL Language Reference 19c, « Handling PL/SQL Errors » (PRAGMA EXCEPTION_INIT, exceptions prédéfinies ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS). gestion des erreurs (docs.oracle.com)

Fiche relue et sourcée en septembre 2026, refaite en récit en octobre 2026. Le code PostgreSQL a été exécuté dans PostgreSQL 18. Le code Oracle suit la documentation Oracle 19c.

Glisser pour continuer vers Déclencheurs et curseurs
Université