SQL Oracle : Opérateurs
Les opérateurs arithmétiques
| () | Permet d’évaluer en priorité les opérateurs entre parenthèses |
| – | Signe négativement une expression numérique |
| +-*/ | Addition, soustraction, multiplication, division |
Les opérateurs chaînes de caractères
| || | concaténation de chaînes de caractères |
Les opérateurs divers
| (+) | Utilisé en jointure externe. La colonne suffixée de cet opérateur est une clé étrangère. |
| count(*) | Nombre d’enregistrements retournés par la sélection. |
| count(expr) | Nombre d’enregistrements retournés par la sélection, pour lesquels expr n’a pas une valeur NULL. |
| distinct | Elimine les doublons de l’expression suivant distinct. |
| prior | Définit la relation hiérarchique dans une interrogation arborescente. La partie droite de l’égalité indique l’enfant, la partie gauche le parent. |
Les fonctions numériques
m et n représentent des données de type numérique ou des expressions ayant une donnée résultante de type numérique.
| ABS(n) | Valeur absolue ABS(-32.5)=32.5 |
|
| CEIL(n) | Plus petit entier relatif égal ou supérieur CEIL(31.5)=32 CEIL(-31.5)=-31 |
|
| FLOOR(n) | Plus grand entier relatif inférieur ou égal FLOOR(31.5)=31 FLOOR(-31.5)=-32 |
|
| MOD(m,n) | Reste de la division de m par n MOD(35,4)=3 |
|
| POWER(m,n) | m puissance n POWER(4,3)=84 |
|
| SIGN(n) | Indique le signe de n SIGN(0)=0 SIGN(-5)=-1 SIGN(5)=1 |
|
| SQRT(n) | Racine carrée de n SQRT(9)=3 SQRT(-9)=NULL |
|
| ROUND(n) | Arrondi de n à 100 (Partie entière) ROUND(15.3)=15 ROUND(15.5)=16 |
|
| ROUND(n,m) | Arrondi de n à 10-m ROUND(1500,-3)=2000 ROUND(1499,-3)=1000 ROUND(1.55,1)=1.6 ROUND(1.551,2)=1.55 |
|
| TRUNC(n) | n tronqué à 100 (Partie entière) TRUNC(15.3)=15 |
|
| TRUNC(n,m) | n tronqué à 10-m TRUNC(1500,-3)=1000 TRUNC(1499,-3)=1000 TRUNC(1.55,1)=1.5 TRUNC(1.551,2)=1.5 |
|
Les fonctions de groupe
| AVG(expr) | Moyenne de toutes les valeurs de expr |
| COUNT(*) | Nombre d’enregistrements retournés par la sélection. |
| COUNT(expr) | Nombre d’enregistrements retournés par la sélection, pour lesquels expr n’a pas une valeur NULL. |
| MAX(expr) | Valeur maximale de toutes les valeurs de expr |
| MIN(expr) | Valeur minimale de toutes les valeurs de expr |
| STDDEV(expr) | Ecart type de toutes les valeurs de expr |
| SUM(expr) | Somme de toutes les valeurs de expr |
| VARIANCE(expr) | Variance de toutes les valeurs de expr |
Les fonctions chaîne de caractères
Les fonctions retournant une valeur numérique.
| ASCII(char) | Donne la valeur ASCII ou EBCDIC du premier caractère de la chaîne de caractères char. ASCII(‘(parenthèses)’)=40 ASCII(‘ ‘)=32 |
| INSTR(char, char1[,n[,m]]) | Recherche dans la chaîne de caractères char la position de la chaîne de caractères char1. Si n est précisé, la recherche se fait à partir du rang n. Si m est précisé, la recherche donne la position de la m-ième occurence de char2 dans char1. Si m est précisé, il est obligatoire de préciser n. INSTR(‘Contentement’,’t’,1,5)=0 INSTR(‘Détermination’,’i’,8)=11 |
| LENGTH(char)
LOCATE (ch1,ch2, deb)
|
Donne la longueur de la chaîne de caractères char. LENGTH(‘Intégrité’)=9 Donne la position de ch2 dans chaine 1, aprtir de la position ‘deb’. 0 si pas trouvée |
Les fonctions retournant une valeur alphanumérique.
| CHR(n) | Caractère ayant la valeur ASCII ou EBCDIC de n CHR(65) = ‘A’ CHR(97)=’a’ CHR(40)='( |
| INITCAP(char) | La première lettre de chaque mot de la chaîne de caractères est mise en majuscule, toutes les autres lettre sont mises en minuscules. INITCAP(‘mAdamE de sTaEl’)=’Madame De Stael’ |
| LOWER(char) | Toutes les lettres sont mises en minuscules LOWER(‘ConFiAnce’)=’confiance’ |
| UPPER(char) | Toutes les lettres sont mises en majuscules UPPER(‘ConFiAnce’)=’CONFIANCE’ |
| LPAD(char1,n,[char2]) | Fait précéder la chaîne de caractères char1 de la chaîne de caractères char2 (espace si char2 non spécifié) jusqu’à la longueur n. Si n est inférieur à la longueur de char1, tronque char1 à la longueur n. LPAD(‘Juin’, 9,’=’)= ‘=====Juin’ LPAD(‘Juin’,2,’=’)=’Ju’ |
| RPAD(char1,n,[char2]) | Fait suivre la chaîne de caractères char1 du caractère char2 (espace si char2 non spécifié) jusqu’à la longueur n. Si n est inférieur à la longueur de char1, tronque char à la longueur n. RPAD(‘Juin’, 9,’=’)= ‘Juin=====’ RPAD(‘Juin’,2,’=’)=’Ju’ |
| LTRIM(char1[,char2]) | Supprime du début de la chaîne de caractères char1 les caractères présents dans la chaîne de caractère char2 jusqu’à ce que plus aucun caractère de char2 ne débute char1. LTRIM(‘=======Juin’,’=’)= ‘Juin’ TRIM(‘Juin’,’Ju’)=’in’ LTRIM(‘JJuuJuJin’,’Ju’)=’ain’ |
| RTRIM(char1[,char2]) | Supprime de la fin de la chaîne de caractères char1 les caractères présents dans la chaîne de caractère char2 jusqu’à ce que plus aucun caractère de char2 ne termine char1. RTRIM(‘Juin=======’,’=’)= ‘Juin’ TRIM(‘Juin’,’in’)=’Ju’ RTRIM(‘Juiniinnnii’,’in’)=’Ju’ |
| REPLACE(char, char1, char2) | Remplace dans la chaîne de caractères char la chaîne de caractères char1 par la chaîne de caractères char2. REPLACE(‘1.235.256,45′,’.’,’ ‘)=’1 235 256,45’ REPLACE(‘ABBABAAAB’,’AB’,’C’)= »CBCAAC’ |
| SUBSTR(char1,n[,m]) | Extrait de la chaîne de caractères char1, les caractères situés à partir du rang n jusqu’à la longueur m, ou jusqu’à la fin si m non spécifié. SUBSTR(‘Respect’,4)=’pect’ SUBSTR(‘Respect’,4,2)=’pe’ |
| TRANSLATE(char, char1 , char2) | Remplace dans la chaîne de caractères char les caractères présents dans la chaîne de caractère char1 par les caractères de même rang présents dans la chaîne de caractères char2. REPLACE(‘1.235.256,45′,’.,’,’ ,’)=’1 235 256.45′ REPLACE(‘Satisfaction’,’sa’,’*’)=’Sti*fction’ |
Les fonctions date
Fonctions
| ADD_MONTHS(date, n) | Ajout de n mois à la date. ADD_MONTHS(’01-DEC-93′)=’01-JAN-94′ |
| LAST_DAY(date) | Indique le dernier jour du mois de date LAST_DAY(’15-FEV-93′)=’28-FEV-93′ |
| MONTHS_BETWEEN (date1, date2) |
Nombre de mois entre date1 et date2. La partie décimale est obtenue en divisant le nombre de jours par 31. MONTHS_BETWEEN(’26-JUN-90′,’25-DEC-93′)=40,967742 MONTHS_BETWEEN(’26-JUN-90′,’26-DEC-89′)=-6 |
| NEXT_DAY(d,j) | Date postérieure à la date d du jour j. NEXT_DAY(’12-DEC-93′, ‘MERCREDI’)=’15-DEC-93′ |
| ROUND(date[,format]) | Arrondi de la date au format spécifié. Si format non spécifié, arrondi au jour le plus proche. ROUND(TO_DATE(’30-JUN-93′),’Y’)=’01-JAN-93 ROUND(TO_DATE(’01-JUL-93′),’Y’)=’01-JAN-94′ ROUND( TO_DATE(’15-12-93 12:00:00′,’DD-MM-YY HH:MI:SS’)) = ’16-DEC-93′ ROUND(TO_DATE(’15-12-93 11:59:59′,’DD-MM-YY HH:MI:SS’)) =’15-DEC-93′ |
| TRUNC(date[,format]) | Date tronquée au format spécifié. Si format non spécifié, arrondi au jour. TRUNC(TO_DATE(’01-JUL-93′),’Y’)=’01-JAN-93′ TRUNC( TO_DATE(’15-12-93 12:00:00′,’DD-MM-YY HH:MI:SS’)) =’15-DEC-93′ TRUNC(TO_DATE(’15-12-93′),’MM’)=’01-DEC-93′ |
Formats des fonctions TRUNC et ROUND
| CC, SCC | Arrondi ou tronqué au siècle. |
| YYYY, SYYYY, YEAR, SYEAR, YYY, YY, Y | Arrondi ou tronqué à l’année. Changement d’arrondi à partir du 1er juillet. |
| Q | Arrondi ou tronqué au trimestre. Changement d’arrondi à partir du 16 du 2ème mois. |
| MONTH, MON, MM | Arrondi ou tronqué au mois. Changement d’arrondi à partir du 16ème jour. |
| WW | Arrondi ou tronqué à la semaine dans l’année. |
| W | Arrondi ou tronqué à la semaine dans le mois. |
| DDD, DD, J | Arrondi ou tronqué au jour (Valeur par défaut). |
| DAY, DY, D | Arrondi ou tronqué au dimanche le plus proche. |
| HH, HH12, HH24 | Arrondi ou tronqué à l’heure. |
| MI | Arrondi ou tronqué à la minute. |
Fonctions de conversion
Fonctions
| TO_NUMBER(chaîne) | Conversion d’une chaîne en nombre.TO_NUMBER(‘025’)=25 |
| TO_CHAR(expr[,format]) | Conversion d’une expression de type date ou numérique en chaînes de caractères.TO_CHAR(SYSDATE,’DD-MM-YY’)= ’01-01-94′ |
| TO_DATE(chaîne[, format]) | Conversion d’une chaîne de caractères en date.TO_DATE(’01-01-94′, ‘WW’)=1 |
| CHARTOROWID(chaîne) | Convertit une chaîne de caractères en rowid.CHARTOROWID(‘0000000E.0002.0001’)=0000000E.0002.0001 |
| ROWIDTOCHAR(rowid) | Convertit une valeur de type rowid en chaîne de caractères. |
| CONVERT(chaîne, [, dest_jeu] [, srce_jeu]) |
Convertit une chaîne de caractères, du jeu de caractères srce_jeu dans lequel elle est stockée dans la base vers le jeu de caractères dest_jeu.Par défaut le jeu de caractères dest_jeu est ‘US7ASCII’. Par défaut le jeu de caractères srce_jeu est celui de l’argument <char_set> du paramètre LANGAGE du INIT.ORA courant. |
| HEXTORAW(chaîne) | Convertit une chaîne de caractères contenant une valeur hexadécimale en valeur binaire. |
| RAWTOHEX(raw) | Convertit une valeur binaire en nombre hexadécimal. |
Résumé des conversions classiques par type :
| Type en entrée Type en sortie |
CHAR | DATE | NUMBER |
| CHAR | TO_DATE | TO_NUMBER | |
| DATE | TO_CHAR | Invalide | |
| NUMBER | TO_CHAR | TO_DATE |
Formats numériques
| 9 | Le nombre de 9 détermine la longueur d’affichage. 9999 123 |
| 0 | Placé devant des 9, indique que les zéros sont affichés. 09999 0123 |
| B | Placé devant des 9, indique que les zéros sont affichés par des espaces. B9999 123 |
| $ | Placé devant des 9, indique que le signe $ sera affiché devant le nombre. $9999 $123 |
| MI | Placé après les 9, affiche un – après une valeur négative. 9999MI 123- |
| PR | Placé après les 9, affiche une valeur négative entre <>. 9999PR <123> |
| , | Affiche une virgule à la position où elle se trouve dans la série de 9. 999,99 1,234 |
| . | Affiche un point à la position où il se trouve dans la série de 9.999.99 123.00 |
| V | Multiplie la valeur par 10n où n est le nombre de 9 suivant V. 999V99 12300 |
| E | Affiche le nombre en notation scientifique. 9.999EEEE 1.23E2 |
Formats date
| CC ou SCC | Indique le siècle. S fait précéder les dates situées Avant JC de -. |
| YYYY ou SYYYY | Année sur 4 chiffres. S fait précéder les dates situées Avant JC de -. |
| YYY ou YY ou Y | Les trois, deux ou dernier chiffres de l’année. |
| Y,YYY | Millénaire suivi d’une virgule. |
| YEAR ou SYEAR | Année en toutes lettres. S fait précéder les dates situées Avant JC de -. |
| BC ou AD | Affiche la mention BC ou AD après la date. |
| B.C. ou A.D. | Affiche la mention B.C. ou A.D. après la date. |
| Q | Affiche le trimestre (1 pour Janvier-Mars, …). |
| MM | Affiche le mois sous forme de deux chiffres (01 à 12). |
| MONTH | Nom du mois en toutes lettres sur 9 caractères (donc complété éventuellement par des blancs) en majuscules, capitales ou minuscules. |
| MON | Nom du mois en toutes lettres abrégé en trois lettres. |
| WW | Numéro de la semaine dans l’année (la semaine 1 commence le 1er jour de l’année et dure 7 jours). |
| W | Numéro de la semaine dans le mois (la semaine 1 commence le 1er jour du mois et dure 7 jours). |
| DDD | Rang du jour dans l’année (1 à 365 ou 366). |
| DD | Rang du jour dans le mois (1 à 28, 29, 30 ou 31). |
| D | Rang du jour dans la semaine (1 à 7). |
| DAY, Day, day | Nom du jour en toutes lettres sur 9 caractères (donc complété éventuellement par des blancs) en majuscules, capitales ou minuscules. |
| DY | Nom du jour en toutes lettres abrégé en trois lettres. |
| J | Jour julien. Nombre de jours écoulés depuis le 1er janvier 4712 BC. |
| AM ou PM | Suffixe la date de la mention AM (matin) ou PM (après-midi). |
| A.M. ou P.M. | Suffixe la date de la mention A.M. (matin) ou P.M. (après-midi). |
| HH ou HH12 | Heure de 01 à 12. |
| HH24 | Heure de 01 à 24. |
| MI | Minutes (01 à 60) |
| SS | Secondes (01 à 60) |
| SSSSS | Secondes écoulées depuis Minuit. |
| / . , – | Ponctuations autorisées dans le résultat. |
| « chaîne » | Chaîne de caractères chaîne reproduite dans le résultat. |
| fm | Préfixe permettant de supprimer les blancs suffixants et les zéros préfixants. Fonctionne comme un switch : si présent un nombre pair de fois dans le format, active les suppressions à partir du rang impair et les désactive à partir du rang pair. |
| TH | Suffixe suffixant de TH (DDTH:04TH). |
| SP | Suffixe demandant l’affichage en toutes lettres(DDSP:FOUR). |
| SPTH ou THSP | Suffixe demandant l’affichage en toutes lettres suffixé de TH. |