Expressions et Fonctions simples

Une expression est un ensemble de variables (contenu d'une colonne), de constantes et de fonctions combinées au moyen d'opérateurs. Les fonctions prennent une valeur dépendant de leurs arguments qui peuvent être eux-mêmes des expressions.

Les expressions peuvent figurer :

en tant que colonne résultat d'un SELECT,
dans une clause WHERE,
dans une clause ORDER BY.

Il existe trois types d'expressions correspondant chacun à un type de données de SQL : arithmétique, chaîne de caractère, date. A chaque type correspondent des opérateurs et des fonctions spécifiques.

SQL autorise les mélanges de types dans les expressions et effectuera les conversions nécessaires : dans une expression mélangeant dates et chaînes de caractères, les chaînes de caractères seront converties en dates, dans une expression mélangeant nombres et chaînes de caractères, les chaînes de caractères seront converties en nombre.

Expressions et fonctions arithmétiques

Une expression arithmétique peut contenir :

des noms de colonnes
des constantes
des fonctions arithmétiques

combinés au moyen des opérateurs arithmétiques.

Opérateurs arithmétiques

Les opérateurs arithmétiques présents dans sql sont les suivants :

+ addition ou + unaire
- soustraction ou - unaire
* multiplication
/ division

Remarque : la division par 0 provoque une fin avec code d'erreur.

Priorité des opérateurs

Une expression arithmétique peut comporter plusieurs opérateurs. Dans ce cas, le résultat de l'expression peut varier selon l'ordre dans lequel sont effectuées les opérations. Les opérateurs de multiplication et de division sont prioritaires par rapport aux opérateurs d'addition et de soustraction. Des parenthèses peuvent être utilisées pour forcer l'évaluation de l'expression dans un ordre différent de celui découlant de la priorité des opérateurs.

Exemple : Donner pour chaque commercial son revenu (salaire + commission).

Exemple : Donner la liste des commerciaux classée par commission sur salaire décroissant.

Exemple : Donner la liste des employés dont la commission est inférieure à 5% du salaire.

Fonctions arithmétiques

Dans ce paragraphe, ont été regroupées les fonctions ayant un ou plusieurs nombres comme arguments, et renvoyant une valeur numérique.

> ABS(nb): Renvoie la valeur absolue de nb.
CEIL(nb): Renvoie le plus petit entier supérieur ou égal à nb.
COS(n): Renvoie le cosinus de n, n étant un angle exprimé en radians.
COSH(n): Renvoie le cosinus hyperbolique de n.
EXP(n): Renvoie e puissance n.
FLOOR(nb): Renvoie le plus grand entier inférieur ou égal à nb.
LN(n): Renvoie le logarithme népérien de n qui doit être un entier strictement positif.
LOG(m,n): Renvoie le logarithme en base m de n. m doit être un entier strictement supérieur à 1, et n un entier strictement positif.
MOD(m,n): Renvoie le reste de la division entière de m par n, si n vaut 0 alors renvoie m. Attention, utilisée avec au moins un de ses arguments négatifs, cette fonction donne des résultats qui peuvent être différents d'un modulo classique. Cette fonction ne donne pas toujours un résultat dont le signe du diviseur.
POWER(m,n): Renvoie m puissance n, m et n peuvent être des nombres quelconques entiers ou rééls mais si m est négatif n doit être un entier.
ROUND(n[,m]): Si m est positif, renvoie n arrondi (et non pas tronqué) à m chiffres après la virgule. Si m est négatif, renvoie n arrondi à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.
SIGN(nb): Renvoie -1 si nb est négatif, 0 si nb est nul, 1 si nb est positif.
SIN(n): Renvoie le sinus de n, n étant un angle exprimé en radians.
SINH(n): Renvoie le sinus hyperbolique de n.
SQRT(nb): Renvoie la racine carrée de nb qui doit être un entier positif ou nul.
TAN(n): Renvoie la tangente de n, n étant un angle exprimé en radians.
TANH(n): Renvoie la tangente hyperbolique de n.
TRUNC(n[,m]): Si m est positif, renvoie n arrondi tronqué à m chiffres après la virgule. Si m est négatif, renvoie n tronqué à m chiffres avant la virgule. m doit être un entier et il vaut 0 par défaut.

Exemple : Donner pour chaque employé son salaire journalier.

Expressions et fonctions sur les chaînes de caractères

Opérateur sur les chaînes de caractères

Il existe un seul opérateur sur les chaînes de caractères : la concaténation. Cet opérateur se note au moyen de deux caractères |(barre verticale) accolés. Le résultat d'une concaténation est une chaîne de caractères obtenue en écrivant d'abord la chaîne à gauche de || puis celle à droite de ||.

Fonctions sur les chaînes de caractères

Le paragraphe suivant contient les fonctions travaillant sur les chaînes de caractères et renvoyant des chaînes de caractères.

CONCAT(chaîne1,chaîne2): Renvoie la chaîne obtenue en concaténant chaîne1 à chaîne2. Cette fonction est équivalente à l'opérateur de concaténation | |.
INITCAP(chaîne): Renvoie chaîne en ayant mis la première lettre de chaque mot en majuscule et toutes les autres en minuscule. Les séparateurs de mots sont les espaces et les caractères non alphanumériques.
LOWER(chaîne): Renvoie chaîne en ayant mis toutes ses lettres en minuscules.
LPAD(chaîne,long,[char]): Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne pour qu'elle ait comme longueur long en ajoutant éventuellement à gauche le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.
LTRIM(chaîne[,ens]): Renvoie la chaîne obtenue en parcourant à partir de la gauche chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de defaut de ens est un espace.
REPLACE(chaine,avant,après: Renvoie chaine dans laquelle toutes les occurrences de la chaîne de caractères avant ont été remplacés par la chaîne de caractèresaprès.
RPAD(chaîne,n,[char]): Renvoie la chaîne obtenue en complétant, ou en tronquant, chaîne pour qu'elle ait comme longueur long en ajoutant éventuellement à droite le caractère (ou la chaîne de caractères) char. La valeur par défaut de char est un espace.
RTRIM(chaîne[,ens]): Renvoie la chaîne obtenue en parcourant à partir de la droite chaîne et en supprimant tous les caractères qui sont dans ens. On s'arrête quand on trouve un caractère qui n'est pas dans ens. La valeur de defaut de ens est un espace.
SOUNDEX(chaîne): Renvoie la chaîne de caratères constituée de la représentation phonétique des mots de chaîne.
SUBSTR(chaîne,m[,n]): Renvoie la partie de chaîne commençant au caractère m et ayant une longueur de n.
TRANSLATE(chaîne, avant, après): Renvoie une chaîne de caratères en remplaçant chaque caratère de chaîne présent dans avant par le caractère situé à la même position dans après. Les caratères de chaîne non présents dans avant ne sont pas modifiés. avant peut contenir plus de caractères que apres, dans ce cas les caratères de avant sans correspondants dans apres seront supprimés de chaîne .
UPPER(chaîne): Renvoie chaîne en ayant mis toutes ses lettres en majuscules.

Le paragraphe suivant contient les fonctions travaillant sur les chaînes de caractères et renvoyant des entiers.

INSTR(chaîne, sous-chaîne, debut, occ): Renvoie la position du premier caractère de chaîne correspondant à l'occurence occ de sous-chaîne en commençant la recherche à la position début.
LENGTH(chaîne): Renvoie la longueur de chaîne, exprimée en nombre de caractères.

Expressions et fonctions sur les dates

Opérateurs sur les dates

Au moyen des opérateurs arithmétiques + et - il est possible de construire les expressions suivantes :

date +/- nombre : le resultat est une date obtenue en ajoutant le nombre de jours nombre à la date date.
date2 - date1 : le resultat est le nombre de jours entre les deux dates.

Fonctions sur les dates

ADD_MONTHS(date,n): Renvoie la date obtenue en ajoutant n mois à date. n peut être un entier quelconque. Si le mois obtenu a moins de jours que le jour de date, le jour obtenu est le dernier du mois.
LAST_DAY(date): Renvoie la date du dernier jour du mois de date.
MONTHS_BETWEEN(date2, date1): Renvoie le nombre de mois entre date2 et date1, si date2 est après date1 le résultat est positif, sinon le résultat est négatif. Si les jours date2 et date1 sont les mêmes, ou si ce sont les derniers jours du mois, le résultat est un entier. La partie fractionnaire est calculée en considérant chaque jour comme 1/31ème de mois
NEXT_DAY(date,nom_du_jour): Renvoie la date du prochain jour de la semaine dont le nom est nom_de_jour.
ROUND(date[,précision]): Renvoie date arrondie à l'unité spécifiée dans précision. L'unité de précision est indiquée en utilisant un des masques de mise en forme de la date. On peut ainsi arrondir une date à l'année, au mois, à la minute,... Par défaut la précision est le jour.
SYSDATE: Renvoie la date et l'heure courantes du système d'exploitation hote.
TRUNC(date[,précision]): Renvoie date tronquée à l'unité spécifiée dans précision. Les paramètres sont analogues à ceux de la fonction ROUND.

Exemple : Donner la date du lundi suivant l'embauche de chaque employé.

Exemple : Donner la date d'embauche de chaque employé arrondie à l'année.

Exemple : Donner pour chauqe employé le nombre de jours depuis son embauche.

Fonctions de conversion

ASCII(chaine)

Renvoie le nombre correspondant au code ascii du premier caractère de chaine.

CHR(nombre)

Renvoie le caractère dont nombre est le code ascii.

TO_CHAR(nombre,format)

Renvoie la chaîne de caratères en obtenue en convertissant nombre en fonction de format.
Format est une chaîne de caractères pouvant contenir les caractères suivants :

9: représente un chiffre (non représenté si non significatif)
0: représente un chiffre (représenté même si non significatif)
.: point décimal apparent
V: définit la position du point décimal non apparent
,: une virgule apparaitra à cet endroit
$: un $ précèdera le premier chiffre significatif
B: le nombre sera représenté par des blancs s'il vaut 0
EEEE: le nombre sera représenté avec un exposant (le spécifier avantMI ou PR)
MI: le signe négatif sera à droite
PR: un nombre négatif sera entre <>

TO_CHAR(date,format)

Renvoie conversion d'une date en chaîne de caractères. Le format indique quelle partie de la date doit apparaître, c'est une combinaison des codes suivants :

scc: siècle avec signe
cc: siècle
sy,yyy: année (avec signe et virgule)
y,yyy: année( avec virgule)
yyyy: année
yyy: 3 derniers chiffres de l'année
yy: 2 derniers chiffres de l'année
y: dernier chiffre de l'année
q: numéro du trimestre dans l'année
ww: numéro de la semaine dans l'année
w: numéro de la semaine dans le mois
mm: numéro du mois
ddd: numéro du jour dans l'année
dd: numéro du jour dans le mois
d: numéro du jour dans la semaine
hh ou hh12: heure (sur 12 heures)
hh24: heure sur 24 heures
mi: minutes
ss: secondes
sssss: secondes après minuit
j: jour du calendrier julien

Les formats suivants permettent d'obtenir des dates en lettres ( en anglais) :

syear ou year: année en toutes lettres
month: nom du mois
mon: nom du mois abrégé sur 3 lettres
day: nom du jour
dy: nom du jour abrégé sur 3 lettres
am ou pm: indication am ou pm
bc ou ad: indication avant ou après jesus christ

Les suffixes suivants modifient la présentation du nombre auquel ils sont accolés :

th: ajout du suffixe ordinat st, nd, rd, th
sp: nombre en toutes lettres

Tout caractère spécial inséré dans le format sera reproduit tel quel dans la chaîne de caractères résultat.

TO_DATE(chaîne,format)

Permet de convertir une chaîne de caracteres en donnée de type date. Le format est identique à celui de la fonction TO_CHAR.

TO_NUMBER(chaine)

Convertit chaine en sa valeur numérique.

Remarque : On peut également inserer dans le format une chaîne de caractères quelconque, à condition de la placer entre guillemets"".

Exemple : Donner la liste de tous les employés dont le nom ressemble à DUPONT.

Exemple : Donner la liste de tous les noms des employés en ayant supprimé tous les 'L' et les 'E' en tête des noms.

Exemple : Donner la liste de tous les noms des employés en ayant remplacé les A et les M par des * dans les noms.

Exemple : Afficher tous les salaires avec un $ en tête et au moins trois chiffres ( dont deux décimales).

Autres fonctions

GREATEST(expr1, expr2,...)

Renvoie la plus grande des valeurs expr1, expr2,.... Toutes les expressions sont converties au format de expr1 avant comparaison.

LEAST

Renvoie la plus petite des valeurs expr1, expr2,.... Toutes les expressions sont converties au format de expr1 avant comparaison.

NVL(expr_1, expr_2)

Prend la valeur expr_1, sauf si expr_1 est NULL auquel cas NVL prend la valeur expr_2.
Une valeur NULL en SQL est une valeur non définie.
Lorsque l'un des termes d'une expression a la valeur NULL, l'expression entière prend la valeur NULL. D'autre part, un prédicat comportant une comparaison avec une expression ayant la valeur NULL prendra toujours la valeur faux. La fonction NVL permet de remplacer une valeur NULL par une valeur significative.

DECODE(crit, val_1, res_1 [, val_2, res_2 ...], def)

Cette fonction permet de choisir une valeur parmi une liste d'expressions, en fonction de la valeur prise par une expression servant de critère de sélection.
Le résultat récupéré est :

res_1 si l'expression crit a la valeur val_1
res_2 si l'expression crit a la valeur val_2
def (la valeur par défaut) si l'expression crit n'est égale à aucune des expressions val_1, val_2,...,.

Les expressions résultats res_1, res_2, ..., def peuvent être de types différents : caractère et numérique, ou caractère et date (le résultat est du type de la première expression rencontré dans le DECODE).

La fonction DECODE permet également de mélanger dans une colonne résultat des informations venant de plusieurs colonnes d'une même table.

Exemple : Donner pour chaque employé ses revenus (salaire + commission).

Exemple : Donner la liste des employés avec pour chacun d'eux sa catégorie (président = 1, directeur = 2, autre = 3)

Exemple : Donner la liste des employés en les identifiant par leur fonction dans le département 10 et par leur nom dans les autres départements.

Dernière modification :