330 likes | 442 Views
Les fonctions. Introduction. Syntaxe : nom_fonction (arg1, arg2, …) Exemple : SUBSTR(nom, 1,4) Ceci retourne les 4 premier caractères Frederic Fred Une fonction peut être utilisée là où l’on peut utiliser un nom de colonne SELECT nom FROM employe WHERE LENGTH(nom) = 6;.
E N D
Introduction • Syntaxe: • nom_fonction (arg1, arg2, …) • Exemple : SUBSTR(nom, 1,4) • Ceciretourne les 4 premier caractères • Frederic Fred • Unefonctionpeutêtreutiliséelàoùl’onpeututiliser un nom de colonne SELECT nom FROM employe WHERE LENGTH(nom) = 6;
Types de fonctions • Il y a deux grands types de fonctions : • Fonctions individuelles qui s’appliquent à des données. • Exemple : LENGTH(nomColonne) • Fonctions de groupes basées sur des regroupements de lignes. • Exemple: SUM(nomColonne) • Selon le type de données, les fonctions individuelles sont subdivisées en: • Fonctions numériques • Fonction de caractères • Retournant des nombres • Retournant des caractères • Fonction de dates • Autres
Fonctions caractères • C'est l'ensemble des fonctions qui traitent des chaînes de caractères. • Il y en a une vingtaine. • Exemples: CHR(n) retourne le caractère dont le code ASCII est n en décimal. SELECT CHR(75) FROM DUAL; Retourne le caractère K. Note : la table DUAL est une utilisée lors d’une interrogation sans table. Ex: SELECT sysdate FROM DUAL;
Exemple de fonctions caractères • Produire la liste des commis avec leur salaire SELECT INITCAP(Nom) Employé, ‘$’ || LPAD(salaire,6, ’*’) Salaire FROM employe WHERE LOWER(poste) = 'commis';
Fonctions numériques • C'est l'ensemble des fonctions qui traitent des nombres • Il y en a une vingtaine. • Exemples: • ABS (n) : retourne la valeur absolue de n. • ROUND(n,m) : retourne la valeur de n arrondie à 10-m position. • Par défaut, arrondi à l’unité (m=0) • Valeur de m et correspondance: • -1 : dizaine, • -2 : centaine, • -3 : milliers • 1 : dixième, • 2 : centième, • 3 : millième • Exemple: • Produire la liste des employés avec leur salaire arrondi aux milliers de dollars. SELECT nom, ROUND ( salaire, -3 ) FROM employe;
Fonctions de conversions • Il s'agit d'un ensemble de fonctions qui permettent de convertir des données d'un type à un autre. • Il y en a 9. • Exemples: • TO_CHAR( date , format ) • converti une date en chaîne de caractères. • TO_DATE( char , format ) • converti une chaîne de caractères valides en date.
Formats de dates • Dans les fonctions TO_CHAR et TO_DATE on peut utiliser 30 spécifications pour indiquer un format de date. • Les principales spécifications sont: YYYY,YYY,YY ou Y spécifie l'année de la date avec 4,3,2 ou 1 chiffres. MM spécifie le numéro du mois MONTH spécifie le nom du mois MON spécifie les 3 premières lettres du nom du mois DAY spécifie le nom du jour DD spécifie le numéro de la journée dans le mois "texte" Le texte sera reproduit tel quel. Exemples: TO_CHAR ( date_embauche, 'DD/MM/YY' ) retourne : 16/03/93 TO_CHAR ( DATEMBAUCHE , 'DAY, "le" DD MONTH YYYY') retourne : mardi, le 16 mars 1993
Traitement des heures • L'heure est une donnée qui fait partie de la date. • Lors d’une conversion automatique l’heure n’est pas affichée. • Les spécifications à utiliser pour manipuler les heures sont: HH pour l'heure de 1 à 12, HH24 pour l'heure de 1 à 24, MI pour les minutes de 0 à 59, SS pour les secondes de 0 à 59. • Exemples: TO_CHAR ( date, 'DD/MM/YY HH24:MI:SS‘ ) Retourne, par exemple : 17/03/11 14:08:16 • L’insertion d’une date avec SYSDATE inclut l’heure à laquelle s’est fait l’enregistrement.
Les fonctions de dates • C'est l'ensemble des fonctions qui traitent des dates. • Il y en a une dizaine. • Exemples: • ADD_MONTHS (date, n) • ajoute n mois à la date. • LAST_DAY (date) • retourne la date du dernier jour du mois de la 'date'. SELECT LAST_DAY(’01/01/2011’) FROM DUAL; Retourne le ’31/01/2011’
Fonctions de groupes • Fonctions qui retournent une seule valeur pour un ensemble de rangées. • Fonction de groupes • Fonction de lignes SELECT SUM(salaire) FROM employe SELECT ROUND(salaire,-2) FROM employe
Fonction groupe et WHERE • WHERE spécifie quelles rangées doivent être retenues. SELECT SUM(salaire) FROM employe WHERE id_departement= 20;
Uniformité de la sélection • Un SELECT nepeutretourner des résultatsindividuels et des résultats de fonction de groupes • La requêtesuivanteengendrerauneerreur: SELECT nom, SUM(salaire) FROM employe;
Les principalesfonctions de groupes • AVG la moyenne • COUNT le nombre de lignes • MAX le maximum • MIN le minimum • SUM la somme
DISTINCT et ALL • DISTINCT • des valeurs identiques ne sont utilisées qu’un seul fois. • ALL (par défaut) • toutes les valeurs sont utilisées ALL DISTINCT
Valeurs NULL • Les fonctions de groupes ignorent les «NULL» • sauf COUNT(*) • COUNT(*) • retourne le nombre de rangées.
GROUP BY • GROUP BY permet de former des groupe de rangées • Les fonctions de groupesretournentunevaleur pour chaquegroupe • Pour avoir la somme des salaires, par département : SELECT SUM(salaire) FROM employe GROUP BY id_departement
Valeur de groupe • Le critère de formation de groupespeutêtreutilisédans un résultat de SELECT SELECT id_departement, SUM(salaire) FROM employe GROUP BY id_departement; Dept 10 Dept 20 Dept 30 8750 10875 9400
GROUP BYavec critères multiples • Pour avoir le nombred’employés par poste et par département: SELECT id_departement, poste, COUNT(*) FROM employe GROUP BY id_departement, poste
Fonction de groupe sans GROUP BY • Si l’on exécute l’instruction suivante: SELECT AVG(salaire) FROM employe; Puisqu’il n’y a pas de GROUP BY de spécifier, l’ensemble de référence sera tous les enregistrements de la table employe.
HAVING • Permet de sélectionner les groupes 10,commis 10,gestion 20,commis 20,gestion
Exemple • Obtenir • le numéro du département • la somme des salaires • pour les départements dont la somme des salaires est > $9 000 SELECT id_departement, SUM(salaire) FROM employe GROUP BY id_departement HAVING SUM(salaire) > 9000;
WHERE et HAVING • WHERE permet de spécifier les rangées à considérer. • HAVING permet de spécifier les groupes à considérer.
Exemple • Obtenir la somme des salaires des employés (excluant les commis) • pour les départements dont la somme des salaires (excluant les commis) est > $8,000. SELECT id_departement, SUM(salaire) FROM employe WHERE poste != ‘commis’ GROUP BY id_departement HAVING SUM(salaire) > 8000;