Les fonctions dans une base de données sont des objets SQL qui effectuent des opérations sur les données stockées dans la base de données et renvoient un résultat. Elles sont utilisées pour effectuer des calculs, des transformations de données et des manipulations sur les données stockées, ce qui les rend extrêmement utiles pour simplifier les requêtes, générer des résultats personnalisés et automatiser certaines tâches.
Voici quelques-unes des fonctions couramment utilisées dans Oracle :
CONCAT : Combinaison de deux chaînes de caractères.SUBSTR : Extraction d’une sous-chaîne à partir d’une chaîne.LENGTH : Calcul de la longueur d’une chaîne de caractères.Exemple :
SELECT CONCAT(Prenom, ' ', Nom) AS NomComplet FROM Employes;
TO_NUMBER : Conversion d’une chaîne de caractères en nombre.TO_DATE : Conversion d’une chaîne de caractères en date.Exemple :
SELECT TO_NUMBER(Salaire) * 12 AS SalaireAnnuel FROM Employes;
ROUND : Arrondissement d’un nombre à une précision donnée.SUM : Calcul de la somme des valeurs d’une colonne.Exemple :
SELECT ROUND(Salaire, 2) AS SalaireArrondi FROM Employes; SELECT SUM(Salaire) AS SommeSalaire FROM Employes;
SYSDATE : Récupération de la date et de l’heure système.MONTHS_BETWEEN : Calcul du nombre de mois entre deux dates.Exemple :
SELECT SYSDATE AS DateActuelle FROM dual; SELECT MONTHS_BETWEEN(DateEmbauche, SYSDATE) AS MoisAnciennete FROM Employes;
AVG : Calcul de la moyenne d’une colonne.COUNT : Comptage des lignes d’un ensemble de résultats.MAX : Recherche de la valeur maximale d’une colonne.MIN : Recherche de la valeur minimale d’une colonne.Exemple :
SELECT AVG(Salaire) AS SalaireMoyen FROM Employes; SELECT COUNT(*) AS NombreEmployes FROM Employes WHERE Departement = 'Ventes';
UPPER : Conversion en majuscules.LOWER : Conversion en minuscules.Exemple :
SELECT UPPER(Nom) AS NomMajuscules FROM Employes; SELECT LOWER(Prenom) AS PrenomMinuscules FROM Employes;
NVL : Remplacement de valeurs NULL par une valeur par défaut.Exemple :
SELECT NVL(Email, 'Non spécifié') AS AdresseEmail FROM Employes;
Les fonctions Oracle sont puissantes et flexibles, et elles peuvent être utilisées dans les clauses SELECT, WHERE, HAVING, ORDER BY, et d’autres parties des requêtes SQL pour manipuler et présenter les données de manière appropriée pour les besoins de l’application.
Les fonctions de l’utilisateur (ou « user-defined functions » en anglais) dans Oracle sont des fonctions personnalisées que vous pouvez créer pour effectuer des opérations spécifiques sur les données dans votre base de données. Contrairement aux fonctions intégrées d’Oracle que nous avons discutées précédemment, les fonctions de l’utilisateur sont des fonctions personnalisées que vous créez pour répondre à des besoins spécifiques de votre application. Voici comment vous pouvez créer et utiliser des fonctions de l’utilisateur dans Oracle :
Création d’une fonction de l’utilisateur :
Pour créer une fonction de l’utilisateur dans Oracle, vous devez utiliser le langage PL/SQL (Procedural Language/Structured Query Language). Voici un exemple de création d’une fonction de l’utilisateur qui calcule le carré d’un nombre :
CREATE OR REPLACE FUNCTION CalculerCarre (n IN NUMBER) RETURN NUMBER IS BEGIN RETURN n * n; END CalculerCarre;
Dans cet exemple, la fonction « CalculerCarre » prend un argument « n » de type NUMBER et renvoie le carré de « n » en utilisant la clause « RETURN ».
Utilisation d’une fonction de l’utilisateur :
Après avoir créé une fonction de l’utilisateur, vous pouvez l’utiliser dans vos requêtes SQL comme n’importe quelle autre fonction. Voici un exemple d’utilisation de la fonction « CalculerCarre » :
-- Utilisation de la fonction dans une requête SELECT SELECT CalculerCarre(5) AS CarreDeCinq FROM dual; -- Utilisation de la fonction dans une mise à jour UPDATE Employes SET Salaire = CalculerCarre(Salaire) WHERE Departement = 'Ventes';
Dans cet exemple, nous utilisons la fonction « CalculerCarre » pour calculer le carré de 5 et pour mettre à jour les salaires des employés en utilisant cette fonction.
Les fonctions de l’utilisateur offrent une grande flexibilité car vous pouvez les personnaliser pour répondre à des besoins spécifiques de votre application. Cependant, elles nécessitent une connaissance de base du langage PL/SQL d’Oracle pour les créer et les utiliser efficacement.
CREATE OR REPLACE FUNCTION CelsiusVersFahrenheit (celsius IN NUMBER) RETURN NUMBER IS v_fahrenheit NUMBER; BEGIN v_fahrenheit := (celsius * 9/5) + 32; RETURN v_fahrenheit; END CelsiusVersFahrenheit;
CREATE OR REPLACE FUNCTION GenererNumeroEmploye RETURN NUMBER IS v_numero NUMBER; BEGIN SELECT ma_sequence.NEXTVAL INTO v_numero FROM dual; RETURN v_numero; END GenererNumeroEmploye;
CREATE OR REPLACE FUNCTION ConcatenerAvecSeparateur ( liste_en IN VARCHAR2, separateur IN VARCHAR2 ) RETURN VARCHAR2 IS v_resultat VARCHAR2(4000); BEGIN -- Logique de concaténation ici -- Exemple : Supposons que les valeurs soient séparées par des virgules SELECT LISTAGG(column_value, separateur) WITHIN GROUP (ORDER BY column_value) INTO v_resultat FROM TABLE(SYS.ODCINUMBERLISTPARSE(liste_en, separateur)); RETURN v_resultat; END ConcatenerAvecSeparateur;
CREATE OR REPLACE FUNCTION CalculerAge ( date_naissance IN DATE ) RETURN NUMBER IS v_age NUMBER; BEGIN v_age := TRUNC(MONTHS_BETWEEN(SYSDATE, date_naissance) / 12); RETURN v_age; END CalculerAge;
CREATE OR REPLACE FUNCTION CalculerMedian (valeurs IN NUMBER_ARRAY) RETURN NUMBER IS v_median NUMBER; BEGIN -- Tri des valeurs SELECT MEDIAN(column_value) INTO v_median FROM TABLE(SYS.ODCINUMBERLISTPARSE(valeurs)); RETURN v_median; END CalculerMedian;