1. Nommer
CREATE OR REPLACE FUNCTION, un nom, des paramètres typés.
Informatique · PL/pgSQL et PL/SQL
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 quizLe 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.
Une fonction renvoie une valeur. Une procédure agit.
Après cette fiche, tu sais
RETURNS et RETURN ;Oui, avec des paramètres OUT ? Ou jamais ?
Une valeur, ou une action
Notion 1 · Fonction ou procédure
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.
La procédure
Elle ne renvoie rien à l'appelant. Elle agit sur les données, puis s'arrête.
| FUNCTION | PROCEDURE | |
|---|---|---|
| Valeur de retour | oui : RETURNS type (éventuellement void) | non, mais des paramètres OUT possibles |
| Appel | dans une requête : SELECT f(…) | seule : CALL p(…) |
| COMMIT / ROLLBACK | jamais | oui, sous conditions (notion 6) |
| Usage typique | calculer, transformer, lire | modifier des données, traitements par lots |
Fais défiler : un appel de fonction, puis un appel de procédure. Regarde ce qui revient, ou pas, vers l'appelant.
Ce qui revient vers l'appelant
Cinq besoins. À chaque fois, une contrainte décide pour toi : laquelle faut-il écrire ?
Écrire, puis appeler
Notion 2 · Écrire une fonction
Une fonction qui calcule un prix TTC, avec par défaut le taux normal de la TVA suisse, 8,1 %.5
Les quatre morceaux
CREATE OR REPLACE FUNCTION, un nom, des paramètres typés.
RETURNS NUMERIC, dans l'en-tête, avant le corps.
BEGIN, le calcul, RETURN la valeur, END.
LANGUAGE plpgsql ferme la définition.
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;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 NUMERIC, dans l'en-tête, déclare le type renvoyé ; RETURN …;, dans le corps, renvoie la valeur et termine la fonction.
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
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
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
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
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
employes(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.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ésSELECT augmenter_salaire(42, 10); échoue : augmenter_salaire(integer, integer) is a procedure. Une procédure s'appelle avec CALL, jamais dans une requête.
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.
La valeur sort : la procédure l'écrit, l'appelant la récupère. À l'appel direct, on écrit NULL à sa place.1
Aller-retour : la valeur entre, la procédure peut la modifier, et elle ressort.
Si la procédure a des paramètres OUT ou INOUT, CALL renvoie une ligne avec leurs valeurs.1
Fais défiler : un paramètre IN, puis OUT, puis INOUT. Les valeurs ont été vérifiées dans PostgreSQL 18.
IN : la valeur entre
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 $$;Quand le bloc échoue
Notion 4 · Intercepter une erreur
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
Fais défiler : la flèche suit l'exécution, ligne par ligne.
diviser(10, 4) : la ligne RETURN a / b; s'exécute et renvoie 2.5. La section EXCEPTION n'est jamais visitée.
diviser(10, 0) : a / b lève l'erreur division_by_zero. Le reste du bloc est abandonné.
L'exécution saute à la section EXCEPTION, et la clause WHEN division_by_zero correspond.
Le gestionnaire affiche un avis et renvoie NULL : l'appel réussit, avec un résultat vide.
Sans cette section, l'erreur remonterait jusqu'à l'appelant : toute la requête échouerait avec division by zero.
WHEN OTHERS, qui attrape tout, cache souvent de vrais bogues.Plusieurs lignes, une fonction
Notion 5 · Renvoyer plusieurs lignes
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
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
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
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
-- 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;Fais défiler : un transfert de 200 CHF réussit, puis un transfert de 400 CHF viole la contrainte CHECK. Regarde les soldes.
Avant les transferts
Sept situations, toutes testées dans PostgreSQL 18. Range chacune.
Le même sujet, chez Oracle
Notion 7 · Oracle PL/SQL
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
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 → 2200En 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
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;
/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
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;
/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);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
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 à 6DECLARE
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
Une spécification publique, un corps qui cache le reste.
L'appelant reçoit le droit d'exécuter, pas l'accès aux tables.
Un numéro entre -20000 et -20999 pour chaque cas à signaler.
Six habitudes de pro
Habitudes de pro
p_) et les variables (v_) : ils ne se confondent plus avec les colonnes ;%TYPE : v_sal employes.salaire%TYPE suit le type de la colonne s'il change un jour.3Les décimales
Les calculs en NUMERIC allongent vite le nombre de décimales : on arrondit les montants avec round(…, 2).
Ignorer ou garder
PERFORM : un SELECT sans INTO provoque l'erreur query has no destination for result data ;division_by_zero) plutôt que OTHERS.Entraînement
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.
Exercice 3 · le piège
La fonction se crée sans erreur, puis plante à l'appel, pour deux raisons.
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.
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.
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
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.