FUNCTION Oracle SQL : Créer des fonctions stockées

FUNCTION Oracle SQL : Créer et utiliser des fonctions stockées en PL/SQL

La FUNCTION Oracle SQL est un objet de base de données fondamental qui permet d’encapsuler une logique métier réutilisable et de retourner une valeur calculée. Très utilisée dans les environnements professionnels, la FUNCTION Oracle améliore la lisibilité du code, facilite la maintenance des applications et optimise les performances des requêtes complexes. Ce cours vous guide pas à pas dans la maîtrise de cet objet PL/SQL incontournable.

Définition et utilisation d’une FUNCTION Oracle SQL

Une fonction stockée Oracle (ou stored function) est un bloc PL/SQL nommé, compilé et enregistré dans le dictionnaire de données Oracle. Contrairement à une procédure stockée (PROCEDURE), une fonction retourne obligatoirement une valeur via le mot-clé RETURN. Cette valeur peut être de tout type Oracle valide : VARCHAR2, NUMBER, DATE, BOOLEAN (en PL/SQL), etc.

Cas d’usage en entreprise

Dans les projets réels, les fonctions Oracle sont massivement utilisées pour :

Calculs métier centralisés : calcul d’une TVA, d’une remise commerciale, d’un score de risque client.
Transformations de données : formatage d’un numéro de téléphone, conversion d’une devise, nettoyage de chaînes.
Règles de gestion réutilisables : vérification de la validité d’un IBAN, calcul d’une ancienneté salariale.
Utilisation dans des requêtes SQL : une fonction peut être appelée directement dans un SELECT, un WHERE ou un ORDER BY.

L’un des avantages majeurs d’une fonction par rapport à du code SQL inline est la centralisation de la logique : une modification de règle métier n’impacte qu’un seul objet dans la base.

Syntaxe complète de la FUNCTION Oracle

Voici la syntaxe de référence pour créer une fonction stockée Oracle :

CREATE [OR REPLACE] FUNCTION nom_fonction
    (parametre1 [IN | OUT | IN OUT] type_donnee,
     parametre2 [IN | OUT | IN OUT] type_donnee,
     ...)
RETURN type_retour
[AUTHID {CURRENT_USER | DEFINER}]
[DETERMINISTIC]
[PARALLEL_ENABLE]
IS | AS
    -- Déclarations de variables locales
BEGIN
    -- Corps de la fonction
    RETURN valeur;
[EXCEPTION
    -- Gestion des erreurs]
END [nom_fonction];
/

Explication des paramètres essentiels

OR REPLACE : recompile la fonction si elle existe déjà, sans la supprimer au préalable. Indispensable en développement.
IN : paramètre en entrée (valeur passée à la fonction). Mode par défaut.
OUT : paramètre en sortie (peu courant dans une fonction, privilégié pour les procédures).
IN OUT : paramètre en entrée/sortie modifiable par la fonction.
RETURN type_retour : déclare le type de la valeur retournée. Obligatoire dans l’en-tête.
DETERMINISTIC : indique qu’à paramètres identiques, la fonction retourne toujours le même résultat. Permet à Oracle d’optimiser les appels répétés.
IS | AS : introduit le corps de la fonction. Les deux mots-clés sont équivalents.
RETURN valeur; : instruction obligatoire dans le corps pour retourner la valeur calculée.
/ : délimiteur SQL*Plus pour exécuter le bloc de création.

Exemples pratiques de FUNCTION Oracle SQL

Exemple 1 – Calcul du salaire annuel brut d’un employé

Contexte métier : dans un service RH, on souhaite calculer le salaire annuel brut d’un employé à partir de son salaire mensuel et du nombre de mois travaillés (souvent 13 ou 14 mois selon les conventions).

-- Création de la fonction SALAIRE_ANNUEL
CREATE OR REPLACE FUNCTION salaire_annuel (
    p_salaire_mensuel IN NUMBER,   -- Salaire mensuel brut
    p_nb_mois         IN NUMBER DEFAULT 12  -- Nombre de mois (défaut : 12)
)
RETURN NUMBER
DETERMINISTIC
IS
    v_salaire_annuel NUMBER;  -- Variable locale pour le résultat
BEGIN
    -- Vérification des paramètres d'entrée
    IF p_salaire_mensuel IS NULL OR p_salaire_mensuel < 0 THEN
        RETURN NULL;
    END IF;

    -- Calcul du salaire annuel
    v_salaire_annuel := p_salaire_mensuel * p_nb_mois;

    RETURN v_salaire_annuel;

EXCEPTION
    WHEN OTHERS THEN
        -- En cas d'erreur inattendue, on retourne NULL
        RETURN NULL;
END salaire_annuel;
/

-- Utilisation dans une requête SQL
SELECT
    employe_id,
    nom,
    salaire_mensuel,
    salaire_annuel(salaire_mensuel, 13) AS salaire_annuel_brut
FROM employes
WHERE departement_id = 10
ORDER BY salaire_annuel_brut DESC;

Dans cet exemple, la fonction est appelée directement dans le SELECT comme une fonction SQL native. Le paramètre p_nb_mois possède une valeur par défaut, ce qui le rend optionnel lors de l’appel.

Exemple 2 – Formatage d’un numéro de téléphone français

Contexte métier : une application CRM stocke les numéros de téléphone sous forme brute (10 chiffres sans espaces). On souhaite les afficher au format lisible XX XX XX XX XX.

-- Création de la fonction FORMAT_TELEPHONE
CREATE OR REPLACE FUNCTION format_telephone (
    p_numero IN VARCHAR2  -- Numéro brut : ex. '0612345678'
)
RETURN VARCHAR2
DETERMINISTIC
IS
    v_numero_clean VARCHAR2(10);  -- Numéro nettoyé
    v_resultat     VARCHAR2(14);  -- Résultat formaté
BEGIN
    -- Suppression des espaces et tirets éventuels
    v_numero_clean := REGEXP_REPLACE(p_numero, '[^0-9]', '');

    -- Vérification : un numéro français doit avoir exactement 10 chiffres
    IF LENGTH(v_numero_clean) != 10 THEN
        RETURN 'Numéro invalide';
    END IF;

    -- Formatage au standard français : XX XX XX XX XX
    v_resultat := REGEXP_REPLACE(v_numero_clean,
                                 '([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})([0-9]{2})',
                                 '\1 \2 \3 \4 \5');

    RETURN v_resultat;

EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END format_telephone;
/

-- Test de la fonction
SELECT
    client_id,
    nom_client,
    telephone_brut,
    format_telephone(telephone_brut) AS telephone_formate
FROM clients
WHERE rownum <= 5;

Cet exemple illustre l’utilisation de REGEXP_REPLACE (fonction Oracle native) à l’intérieur d’une fonction personnalisée, une pratique courante pour les transformations de chaînes.

Erreurs courantes avec FUNCTION Oracle SQL

Erreur : ORA-06503 – PL/SQL: Function returned without value

C’est l’erreur la plus fréquente lors de la création d’une fonction Oracle. Elle se produit lorsqu’un chemin d’exécution du code atteint la fin de la fonction sans rencontrer d’instruction RETURN.

Code problématique :

CREATE OR REPLACE FUNCTION categorie_age (p_age IN NUMBER)
RETURN VARCHAR2
IS
BEGIN
    IF p_age < 18 THEN
        RETURN 'Mineur';
    ELSIF p_age BETWEEN 18 AND 64 THEN
        RETURN 'Adulte';
    END IF;
    -- ⚠️ Oubli : que se passe-t-il si p_age >= 65 ?
    -- Oracle lève ORA-06503 à l'exécution
END categorie_age;
/

Solution : toujours prévoir un RETURN final qui couvre tous les cas, notamment avec un ELSE ou un RETURN après la structure conditionnelle :

    IF p_age < 18 THEN
        RETURN 'Mineur';
    ELSIF p_age BETWEEN 18 AND 64 THEN
        RETURN 'Adulte';
    ELSE
        RETURN 'Senior';  -- ✅ Tous les cas sont couverts
    END IF;
END categorie_age;
/

Bonne pratique : ajoutez systématiquement un RETURN NULL; dans le bloc EXCEPTION WHEN OTHERS pour éviter les erreurs non gérées lors des appels dans des requêtes SQL.

Résumé

Point clé	Détail
Objet Oracle	Bloc PL/SQL nommé, stocké dans le dictionnaire de données
Obligation	Doit retourner une valeur via `RETURN`
Modes de paramètres	`IN` (défaut), `OUT`, `IN OUT`
Appel SQL	Utilisable dans `SELECT`, `WHERE`, `ORDER BY`
Clause utile	`DETERMINISTIC` pour l’optimisation des appels répétés
Erreur fréquente	ORA-06503 : chemin d’exécution sans `RETURN`
Recréation	`OR REPLACE` pour modifier sans supprimer

2 bonnes pratiques Oracle à retenir

Déclarez toujours le clause DETERMINISTIC lorsque votre fonction retourne le même résultat pour des paramètres identiques. Oracle peut ainsi mettre en cache les résultats lors d’appels répétés dans une même requête, ce qui améliore significativement les performances sur des tables volumineuses.
Nommez explicitement vos paramètres lors de l’appel avec la notation nom_param => valeur plutôt que la notation positionnelle. Cette pratique améliore la lisibilité du code et évite les erreurs lors de l’ajout de nouveaux paramètres optionnels à la fonction.

Aller plus loin

Maintenant que vous maîtrisez la FUNCTION Oracle SQL, voici trois sujets complémentaires pour approfondir vos compétences PL/SQL :

PROCEDURE Oracle SQL : découvrez les différences entre une procédure et une fonction, et apprenez quand utiliser l’une plutôt que l’autre dans vos développements PL/SQL.
PACKAGE Oracle SQL : apprenez à regrouper vos fonctions et procédures dans un package pour mieux organiser votre code et gérer les dépendances entre objets.
TRIGGER Oracle SQL : maîtrisez les déclencheurs pour automatiser des traitements lors d’événements DML (INSERT, UPDATE, DELETE) sur vos tables.