Cours SQL ORACLE – 07-07 : FUNCTION

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.