Les FUNCTION comme pour les PROCEDURE sont du bloc PL/SQL stockés dans l’a BD ORACLE. Les fonctions retournent toujours une valeur d’un type ORACLE définie.

La structure d’une FUNCTION est comme suit:

CREATE [OR REPLACE] FUNCTION <NOM_FUNCTION>([VAR1 IN <TYPE_VAR1>],
                    [VAR1 IN <TYPE_VAR1>],
                    ....
                    [VARN OUT <TYPE_VARN>])
                    RETURN <TYPE_RETOUR>
                    IS

[Déclaration variables à utiliser]

BEGIN

[Corps du programme]

END;
/

Pour déclarer une FUNCTION:

• CREATE : Créé la fonction <NOM_FUNCTION>.
• OR REPLACE : Facultatif mais vaut mieux l’utiliser. Cela permet de recréer la FUNCTION <NOM_FUNCTION> si elle existe déjà.
• FUNCTION : Mot clé pour définir qu’il s’agit d’une fonction.
• <NOM_FUNCTION> : Le nom de la FUNCTION à créer.
• ([VAR1 IN <TYPE_VAR1>], .., [VARN OUT ]) : Les paramètres de notre FUNCTION, NOM_VARIABLE, IN ou OUT ou IN OUT et le type du paramètre (VARCHAR2, NUMBER, DATE, etc…).
  • IN : paramètre en entrée.
  • OUT : paramètre en sortie.
  • IN OUT : paramètre en entrée et en sortie.
  • Les paramètres sont facultatifs. Nous pouvons déclarer une FUNCTION sans paramètres d’entrées ou de sorties.
• IS : Mot clé pour déclarer le début de la FUNCTION.
• Variables à utiliser : Déclaration des variables qu’on va utiliser dans notre FUNCTION.
• BEGIN : Début du bloc PL/SQL de la FUNCTION.
• Corps du programme : Bloc PL/SQL de la FUNCTION.
• END; : Fin de notre FUNCTION.
• / : Permet de créer la FUNCTION .

Il est intéressant de signaler qu’on peut utiliser les paramètres OUT dans les FUNCTION comme pour les PROCEDURE, mais cela n’empêche pas que les FUNCTION doivent toujours retourner une valeur par la commande RETURN.

On commence par un exemple simple de FUNCTION qui va nous retourner la date Systéme sous forme de VARCHAR2 formater en DD/MM/YYYY HH:MI:SS :

CREATE OR REPLACE FUNCTION DATE_DU_JOUR RETURN VARCHAR2 IS
BEGIN
   RETURN TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS');
END;
/

La sortie de ce code est :

FUNCTION DATE_DU_JOUR compiled

On fait appel à cette fonction dans une requête :

SELECT DATE_DU_JOUR FROM DUAL;

DATE_DU_JOUR
-------------------
03/01/2017 18:33:52 

Dans ce deuxième exemple, nous allons créé une fonction qui vérifie si le PARAM1 en entrée est un code EMP dans notre table EMP du Schéma SCOTT. Si c’est le cas, la fonction retournera 1 sinon 0.

CREATE OR REPLACE FUNCTION F_VERIF_EMP(PARAM1 IN EMP.EMPNO%TYPE) RETURN NUMBER IS
   I NUMBER;
BEGIN
   SELECT 1 INTO I FROM EMP WHERE EMPNO = PARAM1;
   RETURN 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
   RETURN 0;
END;
/

On lance fonction avec le paramétré 7788 et 9999:

SELECT DECODE(F_VERIF_EMP(7788), 1, 'Existe', 'N''existe pas') N_7788,
DECODE(F_VERIF_EMP(9999), 1, 'Existe', 'N''existe pas') N_9999
from dual;

N_7788       N_9999
------------ ------------ 
Existe       N'existe pas

Dans cet exemple, nous allons créer une fonction qui compare deux paramètres en entrées de type VARCHAR2 sans tenir compte de la casse est retourne le résultat sous forme de VRAI ou FAUX:

CREATE OR REPLACE FUNCTION F_COMPARE_CHAINE (PARAM1 IN VARCHAR2,
                                            PARAM2 IN VARCHAR2) RETURN VARCHAR2
                                            IS
BEGIN
   IF UPPER(PARAM1) = UPPER(PARAM2) THEN
      RETURN 'VRAI';
   END IF;
   RETURN 'FAUX';
END;
/

On lance la fonction avec le paramétré « Bonjour COURS SQL » et « Bonjour Cours Sql »:

SELECT F_COMPARE_CHAINE('Bonjour COURS SQL', 'Bonjour Cours Sql') FROM DUAL;

F_COMPARE_CHAINE('BONJOURCOURSSQL','BONJOURCOURSSQL')
-----------------------------------------------------
VRAI

Pour finir, nous allons créer une fonction qui retourne le paramètre en entrée en majuscule et enlève les espaces qui existent dans ce paramètre:

CREATE OR REPLACE FUNCTION F_MAJUSCULE_SUPR_ESPACE_CHAINE (PARAM1 IN VARCHAR2)
                                                           RETURN VARCHAR2 IS
   VAR VARCHAR2(4000);
BEGIN
   VAR := REPLACE(UPPER(PARAM1), ' ', '');
   RETURN VAR;
END;
/

On lance la fonction avec le paramétré ‘  Bo njour Cours Sq l   ‘:

SELECT F_MAJUSCULE_SUPR_ESPACE_CHAINE('  Bo njour Cours Sq l   ') FROM DUAL;

Le résultat est :

F_MAJUSCULE_SUPR_ESPACE_CHAINE('BONJOURCOURSSQL')
-------------------------------------------------
BONJOURCOURSSQL 

Dans notre prochain cours nous allons voir les packages.

Syntaxe d’une FUNCTION en PL/SQL Oracle

Une fonction stockée est un bloc PL/SQL nommé qui renvoie obligatoirement une valeur via RETURN.

CREATE OR REPLACE FUNCTION nom_fonction (
   p_param1 IN TYPE,
   p_param2 IN TYPE DEFAULT valeur
) RETURN type_retour
IS
   v_variable TYPE;
BEGIN
   -- traitement
   RETURN v_variable;
END nom_fonction;
/

Exemples pratiques

1. Une fonction de calcul simple :

CREATE OR REPLACE FUNCTION calcul_tva (p_montant IN NUMBER, p_taux IN NUMBER DEFAULT 20)
RETURN NUMBER
IS
BEGIN
   RETURN ROUND(p_montant * p_taux / 100, 2);
END;
/

SELECT calcul_tva(1000) FROM DUAL;      -- 200
SELECT calcul_tva(1000, 5.5) FROM DUAL; -- 55

2. Une fonction qui interroge la base :

CREATE OR REPLACE FUNCTION nb_employes (p_dept IN NUMBER)
RETURN NUMBER
IS
   v_nb NUMBER;
BEGIN
   SELECT COUNT(*) INTO v_nb FROM employes WHERE departement = p_dept;
   RETURN v_nb;
EXCEPTION
   WHEN NO_DATA_FOUND THEN RETURN 0;
END;
/

3. Appeler la fonction directement dans une requête :

SELECT d.nom_departement, nb_employes(d.id) AS effectif
FROM departements d;

FUNCTION ou PROCEDURE : comment choisir ?

Règle simple : si vous voulez utiliser le résultat dans une requête SQL, c'est une fonction. Sinon, c'est une procédure.

Erreurs fréquentes

• ORA-06503 : function returned without value — un chemin d'exécution ne passe par aucun RETURN (typiquement une branche IF sans ELSE).
• ORA-14551 : cannot perform a DML operation inside a query — votre fonction fait un INSERT/UPDATE et vous l'appelez depuis un SELECT. Ajoutez PRAGMA AUTONOMOUS_TRANSACTION, ou mieux : n'écrivez pas dans une fonction appelée en SQL.
• Fonction lente en SELECT — une fonction appelée sur un million de lignes s'exécute un million de fois. Ajoutez DETERMINISTIC si le résultat ne dépend que des paramètres, ou réécrivez la logique en SQL pur.
• Compilation en erreur — utilisez SHOW ERRORS ou interrogez USER_ERRORS pour voir la ligne fautive.

Sur le même thème

• Cours SQL ORACLE – 00 : Introduction aux objets d’une BD Oracle
• Cours SQL ORACLE – 01 : Les Tables (CREATE ALTER TABLE).
• Cours SQL ORACLE – 02 : Les requêtes DML (SELECT, INSERT, UPDATE et DELETE).
• Cours SQL ORACLE – 03 : Les jointures (LEFT RIGHT FULL JOIN).
• Cours SQL ORACLE – 04 : Les fonctions d’agrégation
• Cours SQL ORACLE – 05 : GROUP BY HAVING

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.