fonctions mono-ligne
DESCRIPTION
Fonctions Mono-Ligne, Fonctions sql, Fonctions Mono-Ligne sql oracle, Fonctions sql oracle, telecharger diapo sql oracleTRANSCRIPT
Copyright Oracle Corporation, 1998. Tous droits réservés.
33
Fonctions Mono-Ligne
www.TelechargerCours.com
3-2 Copyright Oracle Corporation, 1998. Tous droits réservés.
Objectifs
A la fin de ce chapitre, vous saurez :A la fin de ce chapitre, vous saurez :
• Décrire différents types de fonctions SQL
• Utiliser les fonctions caractère, numériques et date dans les ordres SELECT
• Expliquer les fonctions de conversion
www.TelechargerCours.com
3-3 Copyright Oracle Corporation, 1998. Tous droits réservés.
Qu’est ce qu’une fonction ?
Une fonction est une expression d’un type Une fonction est une expression d’un type de données spécifique qui fait partie de données spécifique qui fait partie d’une instruction utilisée pour calculer d’une instruction utilisée pour calculer une valeur .une valeur .
www.TelechargerCours.com
3-4 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions SQL
FonctionFonctionEntréeEntrée
arg 1arg 1
arg 2arg 2
arg narg n
La fonction La fonction exécute une exécute une
actionaction
SortieSortie
Valeur Valeur résultanterésultante
www.TelechargerCours.com
3-5 Copyright Oracle Corporation, 1998. Tous droits réservés.
Deux Types de Fonctions SQL
FonctionsFonctions
Fonctions Fonctions mono-lignemono-ligne
Fonctions Fonctions multi-lignemulti-ligne
www.TelechargerCours.com
3-6 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions Mono-Ligne
• Manipulent des éléments de données
• Acceptent des arguments et ramènent une valeur
• Agissent sur chacune des lignes rapportées
• Ramènent un seul résultat par ligne
• Peuvent modifier les types de données
• Peuvent être imbriquées
function_name (column|expression, [arg1, arg2,...])function_name (column|expression, [arg1, arg2,...])
www.TelechargerCours.com
3-7 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions Mono-Ligne
ConversionConversion
CaractèreCaractère
NumériqueNumérique
DateDate
GénéraleGénéraleFonctionsFonctions
mono-lignemono-ligne
www.TelechargerCours.com
3-8 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions Caractère
FonctionFonctioncaractèrecaractère
LOWERLOWER
UPPERUPPER
INITCAPINITCAP
CONCATCONCAT
SUBSTRSUBSTR
LENGTHLENGTH
INSTRINSTR
LPAD ...LPAD ...
Fonctions de conversionFonctions de conversionmajuscules/minusculesmajuscules/minuscules
Fonctions de manipulationFonctions de manipulationdes caractèresdes caractères
www.TelechargerCours.com
3-9 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions de Conversion Majuscules/Minuscules
Fonction
LOWER('Cours SQL')
UPPER('Cours SQL')
INITCAP('Cours SQL')
Résultat
cours sql
COURS SQL
Cours Sql
3-10 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation des Fonctions de Conversion Majuscules/Minuscules
Afficher le matricule, le nom et le numéro Afficher le matricule, le nom et le numéro de département de l’employé Blake.de département de l’employé Blake.
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake';
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake';
EMPNO ENAME DEPTNO--------- ---------- --------- 7698 BLAKE 30
EMPNO ENAME DEPTNO--------- ---------- --------- 7698 BLAKE 30
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE LOWER(ename) = 'blake';
no rows selectedno rows selected
3-11 Copyright Oracle Corporation, 1998. Tous droits réservés.
CONCAT('Une', 'Chaîne')
SUBSTR('Chaîne',1,3)
LENGTH('Chaîne')
INSTR('Chaîne', 'a')
LPAD(sal,10,'*')
UneChaîne
Cha
6
3
******5000
Fonction Résultat
Fonctions de Manipulation des Caractères
Manipulation de chaînes de caractèresManipulation de chaînes de caractères
www.TelechargerCours.com
3-12 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation des Fonctions de Manipulation des Caractères
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES';
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A')---------- ------------------- ------------- ----------------MARTIN MARTINSALESMAN 6 2ALLEN ALLENSALESMAN 5 1TURNER TURNERSALESMAN 6 0WARD WARDSALESMAN 4 2
www.TelechargerCours.com
3-13 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions NumériquesROUND :ROUND : Arrondit la valeur à la précision Arrondit la valeur à la précision
spécifiéespécifiée
TRUNC :TRUNC : Tronque la valeur à la précision Tronque la valeur à la précision
spécifiéespécifiée
MOD :MOD : Ramène le reste d’une divisionRamène le reste d’une division
ROUND(45.926, 2)ROUND(45.926, 2) 45.9345.93
TRUNC(45.926, 2)TRUNC(45.926, 2) 45.9245.92
MODE(1600,300)MODE(1600,300) 100100
3-14 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction ROUND
Affichage de la valeur 45.923 arrondie au Affichage de la valeur 45.923 arrondie au centième, à 0 décimale et à la dizaine centième, à 0 décimale et à la dizaine supérieure.supérieure.
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)--------------- -------------- ----------------- 45.92 46 50
www.TelechargerCours.com
3-15 Copyright Oracle Corporation, 1998. Tous droits réservés.
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)--------------- ------------- --------------- 45.92 45 40
Utilisation de la Fonction TRUNC
Affichage de la valeur 45.923 tronquée au Affichage de la valeur 45.923 tronquée au centième, à 0 décimale et à la dizaine.centième, à 0 décimale et à la dizaine.
www.TelechargerCours.com
3-16 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction MOD
Calculer le reste de la division salaireCalculer le reste de la division salairepar commission pour l’ensemble des par commission pour l’ensemble des employés ayant un poste de vendeur. employés ayant un poste de vendeur.
SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN';
ENAME SAL COMM MOD(SAL,COMM)---------- --------- --------- -------------MARTIN 1250 1400 1250ALLEN 1600 300 100TURNER 1500 0 1500WARD 1250 500 250
www.TelechargerCours.com
3-17 Copyright Oracle Corporation, 1998. Tous droits réservés.
Autres Fonctions Numériques
ABS(x) :ABS(x) : Valeur absolue de xValeur absolue de x
CEIL(n) :Plus petit entier supérieur ou égal à n.CEIL(n) :Plus petit entier supérieur ou égal à n.
SIGN(n) :SIGN(n) : Si n<0, -1; si n=0, 0; si n>0, 1.Si n<0, -1; si n=0, 0; si n>0, 1.
FLOOR(n) : Plus grand entier supérieur ou égal à FLOOR(n) : Plus grand entier supérieur ou égal à
n. n.
3-18 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation des Dates
• Oracle stocke les dates dans un format numérique interne : siècle, année, mois, jour, heures, minutes, secondes.
• Le format de date par défaut est DD-MON-YY.
• La fonction SYSDATE ramène la date et l’heure courante.
• DUAL est une table factice qu'on peut utiliser pour visualiser SYSDATE.
www.TelechargerCours.com
3-19 Copyright Oracle Corporation, 1998. Tous droits réservés.
Opérations Arithmétiques sur les Dates
• Ajout ou soustraction d’un nombre à une date pour obtenir un résultat de type date.
• Soustraction de deux dates afin de déterminer le nombre de jours entreces deux dates.
• Ajout d’un nombre d’heures à une date en divisant le nombre d’heures par 24.
www.TelechargerCours.com
3-20 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation d’Opérateurs Arithmétiques avec les Dates
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;
ENAME WEEKS---------- ---------KING 830.93709CLARK 853.93709MILLER 821.36566
www.TelechargerCours.com
3-21 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions Date
Nombre de mois situés entre deux datesMONTHS_BETWEEN(d1,d2)
ADD_MONTHS(date, n)
NEXT_DAY(date,’char’)
LAST_DAY(date)
ROUND(date [,’fmt’] )
TRUNC (date [,’fmt’] )
Ajoute des mois calendaires à une date
Jour qui suit la date spécifiée
Dernier jour du mois
Arrondit une date
Tronque une date
FONCTION DESCRIPTION
www.TelechargerCours.com
3-22 Copyright Oracle Corporation, 1998. Tous droits réservés.
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Utilisation des Fonctions Date
• ADD_MONTHS ('11-JAN-94',6)ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY') NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-SEP-95')LAST_DAY('01-SEP-95')
19.677419419.6774194
'11-JUL-94''11-JUL-94'
'08-SEP-95''08-SEP-95'
'30-SEP-95''30-SEP-95'
3-23 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation des Fonctions Date
SELECTSELECT empno, hiredate, empno, hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) Tenure,MONTHS_BETWEEN(SYSDATE,hiredate) Tenure,ADD_MONTHS(hiredate,6) review,ADD_MONTHS(hiredate,6) review,NEXT_DAY(hiredate, ’FRIDAY’) ,NEXT_DAY(hiredate, ’FRIDAY’) ,LAST_DAY(hiredate) LAST_DAY(hiredate)
FROMFROM emp empWHEREWHERE MONTHS_BETWEEN(SYSDATE,hiredate) < 200; MONTHS_BETWEEN(SYSDATE,hiredate) < 200;
www.TelechargerCours.com
3-24 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation des Dates
• Oracle stocke les dates dans un format numérique interne : siècle, année, mois, jour, heures, minutes, secondes.
• Le format de date par défaut est DD-MON-YY.
• La fonction SYSDATE ramène la date et l’heure courante.
• DUAL est une table factice qu'on peut utiliser pour visualiser SYSDATE.
www.TelechargerCours.com
3-25 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions de Conversion
Conversion Conversion de types de types
de données de données impliciteimplicite
ConversionConversionde typesde types
de données de données expliciteexplicite
Conversion Conversion de typesde types
de donnéesde données
www.TelechargerCours.com
3-26 Copyright Oracle Corporation, 1998. Tous droits réservés.
Conversion de Types de Données Implicite
Pour les affectations, Oracle effectue Pour les affectations, Oracle effectue automatiquement les conversions suivantesautomatiquement les conversions suivantes
VARCHAR2 ou CHAR
De Vers
VARCHAR2 ou CHAR
NUMBER
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
3-27 Copyright Oracle Corporation, 1998. Tous droits réservés.
Conversion de Types de Données Implicite
Pour l’évaluation d’expressions, Oracle effectue Pour l’évaluation d’expressions, Oracle effectue automatiquement les conversions suivantesautomatiquement les conversions suivantes
VARCHAR2 ou CHAR
De Vers
VARCHAR2 ou CHAR
NUMBER
DATE
www.TelechargerCours.com
3-28 Copyright Oracle Corporation, 1998. Tous droits réservés.
Conversion de Types de Données Explicite
NUMBERNUMBER CHARACTERCHARACTER
TO_CHARTO_CHAR
TO_NUMBERTO_NUMBER
DATEDATE
TO_CHARTO_CHAR
TO_DATETO_DATE
3-29 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction TO_CHAR avec les Dates
Le modèle de format :
• Doit être placé entre simples quotes et différencie les majuscules et minuscules.
• Peut inclure tout élément valide de format date
• Comporte un élément fm qui supprime les espaces de remplissage ou les zéros de tête
• Est séparé de la valeur date par une virgule
TO_CHAR(date, 'fmt')TO_CHAR(date, 'fmt')
3-30 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction TO_CHAR avec les Dates
SELECT empno, to_char(hiredate, 'MM/YY') SELECT empno, to_char(hiredate, 'MM/YY') "mois d'embauche""mois d'embauche"
FROM empFROM emp
//
3-31 Copyright Oracle Corporation, 1998. Tous droits réservés.
YYYY
Modèles de Format Date
YEAR
MM
MONTH
DY
DAY
Année exprimée avec 4 chiffres
Année exprimée en toutes lettres
Mois exprimé avec 2 chiffres
3 premières lettres du nom du jour
Jour exprimé en toutes lettres
Mois exprimé en toutes lettres
3-32 Copyright Oracle Corporation, 1998. Tous droits réservés.
Modèles de Format pour les Dates
• Les éléments horaires formatent la Les éléments horaires formatent la partie horaire de la date.partie horaire de la date.
• Pour ajouter des chaînes de caractères, Pour ajouter des chaînes de caractères, les placer entre guillemets.les placer entre guillemets.
• Différents suffixes existent pour les Différents suffixes existent pour les nombres.nombres.
HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12 of OCTOBER
ddspth fourteenth
3-33 Copyright Oracle Corporation, 1998. Tous droits réservés.
Format de Date RR
Année en Cours1995199520012001
Date Spécifiée27-OCT-9527-OCT-1727-OCT-1727-OCT-95
Format RR1995201720171995
Format YY1995191720172095
Si les 2 chiffres de l’année en cours sont
0-49
0-49 50-99
50-99
La nouvelle date appartient au siècle courant.La nouvelle date appartient au siècle suivant.
La nouvelle date appartient au siècle précédent.
La nouvelle date appartient au siècle courant.
Si l’année spécifiée est située entre
www.TelechargerCours.com
3-34 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction TO_CHAR avec les Dates
SQL> SELECT ename, 2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE 3 FROM emp;
ENAME HIREDATE---------- -----------------KING 17 November 1981BLAKE 1 May 1981CLARK 9 June 1981JONES 2 April 1981MARTIN 28 September 1981ALLEN 20 February 1981...14 rows selected.
3-35 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction TO_CHAR avec les Nombres
Utilisez les formats suivants avec Utilisez les formats suivants avec TO_CHAR pour afficher un nombre sous TO_CHAR pour afficher un nombre sous la forme d'une chaîne de caractère.la forme d'une chaîne de caractère.
TO_CHAR(number, 'fmt')TO_CHAR(number, 'fmt')
9
0
$
L
.
,
Représente un chiffre
Force l’affichage du zéro
Place un signe dollar flottant
Utilise le symbole monétaire local flottant
Imprime un point décimal
Imprime un séparateur de milliers
3-36 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction TO_CHAR avec les Nombres
SQL> SELECT TO_CHAR(sal,'$99,999') SALARY 2 FROM emp 3 WHERE ename = 'SCOTT';
SALARY-------- $3,000
www.TelechargerCours.com
3-37 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonctions TO_NUMBER et TO_DATE
• Conversion d’une chaîne de caractères en format numérique avec la fonction TO_NUMBER
TO_NUMBER(char)TO_NUMBER(char)
• Conversion d’une chaîne de caractères en format date avec la fonction TO_DATE
TO_DATE(char[, 'fmt'])TO_DATE(char[, 'fmt'])
3-38 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonction NVL
Convertit une valeur NULL en une valeur Convertit une valeur NULL en une valeur réelleréelle
• Fonctionne avec les données de type date, caractère et numérique.
• Les types de données doivent correspondre
– NVL(comm,0)
– NVL(hiredate,'01-JAN-97')
– NVL(job,'No Job Yet')
www.TelechargerCours.com
3-39 Copyright Oracle Corporation, 1998. Tous droits réservés.
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;
Utilisation de la Fonction NVL
ENAME SAL COMM (SAL*12)+NVL(COMM,0)---------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400JONES 2975 35700MARTIN 1250 1400 16400ALLEN 1600 300 19500...14 rows selected.
www.TelechargerCours.com
3-40 Copyright Oracle Corporation, 1998. Tous droits réservés.
Fonction DECODE
Facilite les recherches conditionnelles en Facilite les recherches conditionnelles en jouant le rôle de jouant le rôle de CASECASE ou ou IF-THEN-ELSEIF-THEN-ELSE
DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default])
DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default])
www.TelechargerCours.com
3-41 Copyright Oracle Corporation, 1998. Tous droits réservés.
Utilisation de la Fonction DECODE
SQL> SELECT job, sal, 2 DECODE(job, 'ANALYST', SAL*1.1, 3 'CLERK', SAL*1.15, 4 'MANAGER', SAL*1.20, 5 SAL) 6 REVISED_SALARY 7 FROM emp;
JOB SAL REVISED_SALARY--------- --------- --------------PRESIDENT 5000 5000MANAGER 2850 3420MANAGER 2450 2940...14 rows selected.
www.TelechargerCours.com
3-42 Copyright Oracle Corporation, 1998. Tous droits réservés.
Imbrication des Fonctions
• Le niveau d’imbrication des fonctions mono-ligne est illimité
• Les fonctions imbriquées sont évaluées de l'intérieur vers l'extérieur
F3(F2(F1(col,arg1),arg2),arg3)
Etape 1 = Ré sultat 1
Etape 2 = Ré sultat 2
Etape 3 = Ré sultat 3
www.TelechargerCours.com
3-43 Copyright Oracle Corporation, 1998. Tous droits réservés.
Imbrication des Fonctions
SQL> SELECT ename, 2 NVL(TO_CHAR(mgr),'No Manager') 3 FROM emp 4 WHERE mgr IS NULL;
ENAME NVL(TO_CHAR(MGR),'NOMANAGER')---------- -----------------------------KING No Manager
www.TelechargerCours.com
3-44 Copyright Oracle Corporation, 1998. Tous droits réservés.
Résumé
Utilisez des fonctions mono-ligne pour :Utilisez des fonctions mono-ligne pour :
• Transformer des données
• Formater des dates et des nombres pour l'affichage
• Convertir des types de données de Convertir des types de données de colonnescolonnes
www.TelechargerCours.com