Utiliser des fonctions monolignes
pour personnaliser les résultats
3-1
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Objectifs
A la fin de ce chapitre, vous pourrez :
• décrire les différents types de fonction disponibles en
langage SQL
• utiliser des fonctions de type date, caractère ou numérique
dans des instructions SELECT
3-2
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Contenu du chapitre
•
•
•
•
•
3-3
Fonctions SQL monolignes
Fonctions de type caractère
Fonctions numériques
Utilisation des dates
Fonctions de type date
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions SQL
Entrée
Sortie
Fonction
La fonction effectue
une action
arg 1
arg 2
Valeur
du résultat
arg n
3-4
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Deux types de fonction SQL
Fonctions
3-5
Fonctions
monolignes
Fonctions
multilignes
Renvoient un résultat
par ligne
Renvoient un résultat
par ensemble de lignes
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions monolignes
Les fonctions monolignes :
• manipulent des données
• acceptent des arguments et renvoient une valeur
• agissent sur chaque ligne renvoyée
• renvoient un résultat par ligne
• peuvent modifier le type de données
• peuvent être imbriquées
• acceptent des arguments qui peuvent se présenter sous la
forme d'une colonne ou d'une expression
function_name [(arg1, arg2,...)]
3-6
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions monolignes
Caractère
Fonctions
monolignes
Général
Conversion
3-7
Numérique
Date
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions de type caractère
Fonctions
de type caractère
3-8
Fonctions de
manipulation de caractères
Fonctions de
manipulation de caractères
LOWER
UPPER
INITCAP
CONCAT
SUBSTR
LENGTH
INSTR
LPAD | RPAD
TRIM
REPLACE
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions de conversion de casse
Ces fonctions convertissent la casse de chaînes de
caractères :
3 - 10
Fonction
Résultat
LOWER('SQL Course')
sql course
UPPER('SQL Course')
SQL COURSE
INITCAP('SQL Course')
Sql Course
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser les fonctions de conversion de casse
Affichez le numéro d'employé, le nom et le numéro de
département de l'employé Higgins :
SELECT employee_id, last_name, department_id
FROM
employees
WHERE last_name = 'higgins';
SELECT employee_id, last_name, department_id
FROM
employees
WHERE LOWER(last_name) = 'higgins';
3 - 11
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions de manipulation des caractères
Ces fonctions manipulent les chaînes de caractères :
3 - 12
Fonction
Résultat
CONCAT('Hello', 'World')
HelloWorld
SUBSTR('HelloWorld',1,5)
Hello
LENGTH('HelloWorld')
10
INSTR('HelloWorld', 'W')
6
LPAD(salary,10,'*')
*****24000
RPAD(salary, 10, '*')
24000*****
REPLACE
('JACK and JUE','J','BL')
BLACK and BLUE
TRIM('H' FROM 'HelloWorld')
elloWorld
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser les fonctions de manipulation de caractères
1
SELECT employee_id, CONCAT(first_name, last_name) NAME,
job_id, LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a'?"
FROM
employees
WHERE SUBSTR(job_id, 4) = 'REP';
1
3 - 13
2
3
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
2
3
Fonctions numériques
•
•
•
3 - 14
ROUND : Arrondit une valeur à un nombre de décimales
donné.
TRUNC : Tronque une valeur à un nombre de décimales
donné.
MOD : Renvoie le reste de la division.
Fonction
Résultat
ROUND(45.926, 2)
45.93
TRUNC(45.926, 2)
45.92
MOD(1600, 300)
100
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser la fonction ROUND
1
2
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM
DUAL;
1
2
3
3
DUAL est une table publique que vous pouvez utiliser pour
visualiser les résultats de fonctions ou de calculs.
3 - 15
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser la fonction TRUNC
1
2
SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-1)
FROM
DUAL;
1
3 - 16
2
3
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
3
Utiliser la fonction MOD
Pour tous les employés occupant un poste de vendeur,
calculez le reste du salaire après sa division par 5 000.
SELECT last_name, salary, MOD(salary, 5000)
FROM
employees
WHERE job_id = 'SA_REP';
3 - 17
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser des dates
•
•
La base de données Oracle stocke les dates selon un format
numérique interne : siècle, année, mois, jour, heures,
minutes et secondes.
Le format par défaut d'affichage de la date est DD-MON-RR.
– Vous pouvez stocker des dates du 21ème siècle au 20ème
siècle en indiquant seulement les deux derniers chiffres de
l'année.
– Vous pouvez stocker des dates du 20ème siècle
au 21ème siècle de la même manière.
SELECT last_name, hire_date
FROM
employees
WHERE hire_date < '01-FEB-88';
3 - 18
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Format de date RR
Année en
en cours
cours
Année
1995
1995
2001
2001
Date indiquée
27-OCT-95
27-OCT-95
27-OCT-95
27-OCT-95
Format RR
1995
2017
2017
1995
Format YY
1995
1917
2017
2095
Si l'année à deux chiffres indiquée est :
Si deux
chiffres de
l'année en
cours sont :
3 - 19
0–49
50–99
0–49
La date renvoyée
appartient au siècle
en cours
50–99
La date renvoyée
appartient au siècle
suivant celui en
cours
La date renvoyée
appartient au siècle
précédant celui en
cours
La date renvoyée
appartient au siècle
en cours
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser la fonction SYSDATE
SYSDATE est une fonction qui renvoie :
•
•
la date
l'heure
SELECT sysdate
FROM
dual;
3 - 21
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Opérations arithmétiques avec des dates
•
•
•
3 - 22
Vous pouvez ajouter un nombre à une date ou
le soustraire pour obtenir une nouvelle date.
Vous pouvez soustraire deux dates pour connaître
le nombre de jours qui les sépare.
Vous pouvez ajouter des heures à une date en divisant
le nombre d'heures par 24.
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser des opérateurs arithmétiques
avec des dates
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM
employees
WHERE department_id = 90;
3 - 23
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Fonctions de manipulation de dates
3 - 24
Fonction
Résultat
MONTHS_BETWEEN
Nombre de mois entre deux dates
ADD_MONTHS
Ajout de mois calendaires à la date
NEXT_DAY
Jour suivant la date indiquée
LAST_DAY
Dernier jour du mois
ROUND
Arrondi de la date
TRUNC
Troncature de la date
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser les fonctions de type date
3 - 25
Fonction
Résultat
MONTHS_BETWEEN
('01-SEP-95','11-JAN-94')
19.6774194
ADD_MONTHS ('31-JAN-96',1)
'29-FEB-96'
NEXT_DAY
('01-SEP-95','FRIDAY')
'08-SEP-95'
LAST_DAY
('01-FEB-95')
'28-FEB-95'
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Utiliser les fonctions ROUND et TRUNC avec des dates
Hypothèse : SYSDATE = '25-JUL-03' :
3 - 26
Fonction
Résultat
ROUND(SYSDATE,'MONTH')
01-AUG-03
ROUND(SYSDATE ,'YEAR')
01-JAN-04
TRUNC(SYSDATE ,'MONTH')
01-JUL-03
TRUNC(SYSDATE ,'YEAR')
01-JAN-03
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
Quiz
Lesquelles des affirmations suivantes sont vraies pour les
fonctions monolignes ?
1. Elles manipulent des données.
2. Elles acceptent des arguments et renvoient une valeur
pour chacun.
3. Elles agissent sur chaque ligne renvoyée.
4. Elles renvoient un résultat par ensemble de lignes.
5. Elles ne peuvent pas modifier le type de données.
6. Elles peuvent être imbriquées.
7. Elles acceptent des arguments qui peuvent se présenter
sous la forme d'une colonne ou d'une expression.
3 - 27
Copyright © 2015 Oracle et/ou ses filiales. Tous droits réservés.
