Les fonctions (1ère partie )

Introduction

Aujourd’hui nous allons voir ensemble quelques fonctions bien utiles en SQL (ce sujet sera étudié à travers deux articles). Il faut s’avoir qu’il existe principalement deux types de fonctions, les fonctions de lignes (single row) et les fonctions de groupes (agrégation).

Dans ce premier  article nous allons voir principalement les différentes fonctions de ligne.

Les fonctions sur chaines

Voici une liste des fonctions sur chaines que j’ai trouvé bon de répertorier :

upper(mot) Convertir le mot en majuscule

lower(mot) Convertir le mot en minuscule

initcap(mot) Mettre en majuscule la première lettre.

replace(mot_cible , occ1 , occ2 ) Remplacer chaque occurrence de occ1 par occ2 dans mot_cible

substr(mot, m , n) Extraire n caractère du mot à partir de la position m

length(mot) Retourner la longueur du mot

rpad(mot cible , n , chaine) Complèter mot_cible avec chaine,  sur n caractère à droite de mot_cible

lpad(mot cible , n , chaine) Complèter mot_cible avec chaine ,  sur n caractère à gauche de mot_cible

concat(mot_1 , mot_2) Joindre le mot_1 et mot2 (une concaténation)

instr(mot_1 , x) Retourner la position numérique du caractère x (x représentant n’importe quel caractère)

Voilà quatre petits exemples, ici je vais utiliser la table fictive dual. La table DUAL peut être utilisée lorsqu’une requête ne cible pas de table particulière.

SELECT initcap('SaID') FROM dual ;                      ⇒ Said
SELECT replace('Affoumani' , 'f','s') FROM dual ;       ⇒ Assoumani
SELECT lower('AiMe') FROM dual ;                        ⇒ aime
SELECT rpad(lpad(' Oracle ',10,'*'),12,'*') FROM dual ; ⇒ *** Oracle ***

Vous l’aurez remarqué, on peut aussi imbriquer ces fonctions ☺

Les fonctions numériques

Voici une liste des fonctions numérique que j’ai trouvé bon de répertorier :

round(n ,m) Arrondir le nombre n à m decimal

trunc( n , m) Tronquer le nombre n à m decimal

mod(n, m ) La fonction modulo retourne le reste de la divion de n/m

ceil(n) Renvoyer l’entier superieur le plus proche du nombre n

floor(n) Renvoyer l’entier inferieur le plus proche du nombre n

power(n , m) Renvoyer n à la puissance m

sqrt(n) Renvoyer la racine carée de n

Voilà quatre petits exemples, toujours avec notre table fictive dual

SELECT round(20.236 , 2) FROM dual ;         ⇒ 20.24
SELECT trunc(20.236, 2) FROM dual ,          ⇒ 20,23
SELECT floor(20.796 ) FROM dual ;            ⇒ 20
SELECT mod(10,9) FROM dual ;                 ⇒ 1

Les fonctions Date

Oracle nous donne aussi la possibilité de manipuler les dates. Il faut savoir que les dates sont des types de données que l’on rencontre souvent. Le type date est composé de deux informations : la date et l’heure. Voici une liste des fonctions date que j’ai trouvé bon de répertorier :

sysdate Retourner la date actuelle du système

next_day(date) Renvoyer le jour suivant date

last_day(date) Renvoyer le dernier jour du mois de date

months_between(date1, date2) Renvoyer le nombre de mois entre date1 et date2

add_months(date , n ) Ajouter ou soustraire n mois de date

Il faut savoir que les fonctions round() et trunc() sont aussi utilisables avec les dates. Ils acceptent deux arguments, en premier la date et en second sur quelle partie de la date ils vont se baser pour leur opérations.

Voici quatre petits exemples :

SELECT sysdate FROM dual                       ⇒ 08-SEP-12
SELECT last_day(sysdate) FROM dual             ⇒ 30-SEP-12
SELECT trunc(sysdate , 'month') FROM dual ;    ⇒ 01-SEP-12
SELECT round(sysdate , year) FROM dual ;       ⇒01-JAN-13

Conversion de Format

Dans cette partie nous allons voir comment on peut utiliser les deux fonctions de conversion suivantes : to_char() et to_date(). Mais avant de commencer voici un petit tableau qui va nous servir :

YYYY l’année complet en nombre : exemple 1992

YEAR l’année en toute lettre : exemple twenty twelve

MM les deux premiers digits du mois : exemple 04

MONTH nom complet du mois : exemple april

DY les trois premières lettre du jour: exemple MON

DAY nom complet du jour : exemple Monday

HH24 heure sur une base 24

MI les minutes

SS les secondes

La fonction to_char() va nous permettre entre autre de convertir une date en chaine de caractère :

SELECT to_char(sysdate , 'day dd month year' ) FROM dual ;

⇒ saturday 08 september twenty twelve

SELECT to_char(sysdate , 'dd/mm/yyyy hh24:mi:ss' ) FROM dual ;

⇒ 08/09/yyyy 15:01:30

La fonction to_date() va quand à elle nous permettre de faire l’action inverse, c’est à dire convertir une chaine de caractère en date :

SELECT TO_DATE('september 08, 2012, 15:05','month dd, YYYY, HH24:MI') FROM DUAL;

⇒ 08-SEP-12

Conclusion

Nous avons vu dans cet article qu’il existe deux types de fonctions,  les fonctions de lignes que nous venons d’étudier et les fonction de groupe que l’on verra dans le prochain article. Parmi les fonctions de lignes on a vu qu’il existe trois sous types importants de fonctions :  les fonctions sur chaines, numérique et de date.

J’espère avoir été clair dans les explications et dans mes exemples.

Si vous avez des questions ou des remarques sur cet article, laissez un commentaire ☺

 Said ASSOUMANI