Utilisation des fonctions à une seule ligne

Utilisation des fonctions de ligne unique pour personnaliser la sortie

Oracle SQL fournit une riche bibliothèque de fonctions intégrées qui peuvent être utilisées pour diverses tâches. Les capacités essentielles d'une fonction peuvent être la conversion de cas des chaînes, des opérations dans une chaîne ou des sous-chaînes, des calculs mathématiques sur des données numériques et des opérations de date sur des valeurs de type date. Les fonctions SQL prennent éventuellement des arguments de l'utilisateur et renvoient obligatoirement une valeur.

Sur une catégorie plus large, il existe deux types de fonctions: -

Single Row functions- Les fonctions à une seule ligne sont celles qui travaillent sur une seule ligne et renvoient une sortie par ligne. Par exemple, les fonctions de conversion de longueur et de casse sont des fonctions à une seule ligne.

Multiple Row functions - Les fonctions de lignes multiples fonctionnent sur un groupe de lignes et renvoient un résultat pour l'ensemble complet de lignes. Ils sont également connus sous le nom de fonctions de groupe.

Fonctions à une seule ligne

Les fonctions à une seule ligne peuvent être des fonctions de caractères, des fonctions numériques, des fonctions de date et des fonctions de conversion. Notez que ces fonctions sont utilisées pour manipuler des éléments de données. Ces fonctions nécessitent un ou plusieurs arguments d'entrée et fonctionnent sur chaque ligne, renvoyant ainsi une valeur de sortie pour chaque ligne. L'argument peut être une colonne, un littéral ou une expression. Les fonctions à une seule ligne peuvent être utilisées dans l'instruction SELECT, les clauses WHERE et ORDER BY. Les fonctions à une seule ligne peuvent être -

  • General functions- Contient généralement des fonctions de gestion NULL. Les fonctions de la catégorie sont NVL, NVL2, NULLIF, COALESCE, CASE, DECODE.

  • Case Conversion functions- Accepte la saisie de caractères et renvoie une valeur de caractère. Les fonctions de la catégorie sont UPPER, LOWER et INITCAP.

    • La fonction UPPER convertit une chaîne en majuscules.

    • La fonction LOWER convertit une chaîne en minuscules.

    • La fonction INITCAP convertit uniquement les alphabets initiaux d'une chaîne en majuscules.

  • Character functions- Accepte la saisie de caractères et renvoie un nombre ou une valeur de caractère. Les fonctions de la catégorie sont CONCAT, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM et REPLACE.

    • La fonction CONCAT concatène deux valeurs de chaîne.

    • La fonction LENGTH renvoie la longueur de la chaîne d'entrée.

    • La fonction SUBSTR renvoie une partie d'une chaîne d'un point de départ donné à un point de fin.

    • La fonction INSTR renvoie la position numérique d'un caractère ou d'une chaîne dans une chaîne donnée.

    • Les fonctions LPAD et RPAD remplissent la chaîne donnée jusqu'à une longueur spécifique avec un caractère donné.

    • La fonction TRIM coupe l'entrée de chaîne à partir du début ou de la fin.

    • La fonction REPLACE remplace les caractères de la chaîne d'entrée par un caractère donné.

  • Date functions- Les opérations arithmétiques de date renvoient une date ou des valeurs numériques. Les fonctions de la catégorie sont MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND et TRUNC.

    • La fonction MONTHS_BETWEEN renvoie le nombre de mois entre les deux dates.

    • La fonction ADD_MONTHS ajoute un nombre 'n' de mois à une date d'entrée.

    • La fonction NEXT_DAY renvoie le jour suivant de la date spécifiée.

    • La fonction LAST_DAY renvoie le dernier jour du mois de la date d'entrée.

    • Les fonctions ROUND et TRUNC sont utilisées pour arrondir et tronquer la valeur de date.

  • Number functions- Accepte les entrées numériques et renvoie des valeurs numériques. Les fonctions de la catégorie sont ROUND, TRUNC et MOD.

    • Les fonctions ROUND et TRUNC sont utilisées pour arrondir et tronquer la valeur numérique.

    • MOD est utilisé pour renvoyer le reste de l'opération de division entre deux nombres.

Illustrations

Fonctions générales

La requête SELECT ci-dessous illustre l'utilisation de la fonction NVL. 

SELECT first_name, last_name, salary, NVL (commission_pct,0) 
FROM employees
WHERE rownum < 5;

FIRST_NAME           LAST_NAME                     SALARY NVL(COMMISSION_PCT,0)
-------------------- ------------------------- ---------- ---------------------
Steven               King                           24000                     0
Neena                Kochhar                        17000                     0
Lex                  De Haan                        17000                     0
Alexander            Hunold                          9000                     0

Fonctions de conversion de cas

La requête SELECT ci-dessous illustre l'utilisation des fonctions de conversion de casse.

SELECT UPPER (first_name), INITCAP (last_name), LOWER (job_id)
FROM employees
WHERE rownum < 5;

UPPER(FIRST_NAME)    INITCAP(LAST_NAME)        LOWER(JOB_
-------------------- ------------------------- ----------
STEVEN               King                      ad_pres
NEENA                Kochhar                   ad_vp
LEX                  De Haan                   ad_vp
ALEXANDER            Hunold                    it_prog

Fonctions de caractère

La requête SELECT ci-dessous illustre l'utilisation de la fonction CONCAT pour concaténer deux valeurs de chaîne.

SELECT CONCAT (first_name, last_name) 
FROM employees
WHERE rownum < 5;

CONCAT(FIRST_NAME,LAST_NAME)
--------------------------------
EllenAbel
SundarAnde
MozheAtkinson
DavidAustin

La requête SELECT ci-dessous illustre l'utilisation des fonctions SUBSTR et INSTR. La fonction SUBSTR renvoie la partie de la chaîne d'entrée de la 1ère à la 5ème position. La fonction INSTR renvoie la position numérique du caractère «a» dans le prénom.

SELECT SUBSTR (first_name,1,5), INSTR (first_name,'a')
FROM employees
WHERE rownum < 5;

SUBST INSTR(FIRST_NAME,'A')
----- ---------------------
Ellen                     0
Sunda                     5
Mozhe                     0
David                     2

La requête SELECT ci-dessous montre l'utilisation de LPAD et RPAD pour imprimer correctement les informations sur l'employé et le poste.

SELECT RPAD(first_name,10,'_')||LPAD (job_id,15,'_')
FROM employees
WHERE rownum < 5;

RPAD(FIRST_NAME,10,'_')||
-------------------------
Steven____________AD_PRES
Neena_______________AD_VP
Lex_________________AD_VP
Alexander_________IT_PROG

Fonctions numériques

La requête SELECT ci-dessous illustre l'utilisation des fonctions ROUND et TRUNC.

SELECT ROUND (1372.472,1)
FROM dual;

ROUND(1372.472,1)
-----------------
           1372.5

SELECT TRUNC (72183,-2)
FROM dual;

TRUNC(72183,-2)
---------------
          72100

Opérations arithmétiques de date

La requête SELECT ci-dessous montre une fonction arithmétique de date où la différence entre la date d'embauche de l'employé et la date système est effectuée.

SELECT employee_id, (sysdate - hire_date) Employment_days
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_DAYS
----------- ---------------
        100      3698.61877
        101      2871.61877
        102      4583.61877
        103      2767.61877

Fonctions de date

La requête SELECT ci-dessous illustre l'utilisation des fonctions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY et LAST_DAY.

SELECT employee_id, MONTHS_BETWEEN (sysdate, hire_date) Employment_months
FROM employees
WHERE rownum < 5;

EMPLOYEE_ID EMPLOYMENT_MONTHS
----------- -----------------
        100        121.504216
        101        94.3751837
        102        150.633248
        103        90.9558289

SELECT ADD_MONTHS (sysdate, 5), NEXT_DAY (sysdate), LAST_DAY (sysdate)
FROM dual;

ADD_MONTH NEXT_DAY( LAST_DAY(
--------- --------- ---------
01-JAN-14 05-AUG-13 31-AUG-13