Utilisation des fonctions de conversion

Outre les fonctions de l'utilitaire SQL, la bibliothèque de fonctions intégrée d'Oracle contient des fonctions de conversion de type. Il peut y avoir des scénarios dans lesquels la requête attend une entrée dans un type de données spécifique, mais elle la reçoit dans un type de données différent. Dans de tels cas, Oracle essaie implicitement de convertir la valeur inattendue en un type de données compatible qui peut être remplacé sur place et la continuité de l'application n'est pas compromise. La conversion de type peut être effectuée implicitement par Oracle ou explicitement par le programmeur.

La conversion de type de données implicite fonctionne sur la base d'une matrice qui présente la prise en charge d'Oracle pour la conversion de type interne. Outre ces règles, Oracle propose des fonctions de conversion de type qui peuvent être utilisées dans les requêtes pour une conversion et une mise en forme explicites. En fait, il est recommandé d'effectuer une conversion explicite au lieu de s'appuyer sur l'intelligence logicielle. Bien que la conversion implicite fonctionne bien, mais pour éliminer les chances asymétriques où les mauvaises entrées pourraient être difficiles à typer en interne.

Conversion de type de données implicite

Une valeur VARCHAR2 ou CHAR peut être implicitement convertie en valeur de type NUMBER ou DATE par Oracle. De même, une valeur de type NUMBER ou DATA peut être automatiquement convertie en données caractères par le serveur Oracle. Notez que l'interconversion impicite se produit uniquement lorsque le caractère représente respectivement une valeur de type nombre ou date valide.

Par exemple, examinez les requêtes SELECT ci-dessous. Les deux requêtes donneront le même résultat car Oracle traite en interne 15000 et «15000» comme étant identiques.

Requête-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Requête-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Conversion de type de données explicite

Les fonctions de conversion SQL sont des fonctions à une seule ligne capables de transtyper une valeur de colonne, un littéral ou une expression. TO_CHAR, TO_NUMBER et TO_DATE sont les trois fonctions qui effectuent une modification croisée des types de données.

Fonction TO_CHAR

La fonction TO_CHAR est utilisée pour convertir une entrée numérique ou de date en type de caractère avec un modèle de format (facultatif).

Syntaxe 

TO_CHAR(number1, [format], [nls_parameter])

Pour la conversion de nombre en caractère, les paramètres nls peuvent être utilisés pour spécifier des caractères décimaux, un séparateur de groupe, un modèle de devise locale ou un modèle de devise internationale. Il s'agit d'une spécification facultative - si elle n'est pas disponible, les paramètres nls de niveau session seront utilisés. Pour la conversion de date en caractère, le paramètre nls peut être utilisé pour spécifier les noms de jour et de mois, selon le cas.

Les dates peuvent être formatées dans plusieurs formats après la conversion en types de caractères à l'aide de la fonction TO_CHAR. La fonction TO_CHAR est utilisée pour qu'Oracle 11g affiche les dates dans un format particulier. Les modèles de format sont sensibles à la casse et doivent être placés entre guillemets simples.

Considérez la requête SELECT ci-dessous. La requête formate les colonnes HIRE_DATE et SALARY de la table EMPLOYEES à l'aide de la fonction TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME           HIRE_DATE          SALARY
-------------------- ------------------ ----------
Steven               JUNE      17, 2003  $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00
Lex                  JANUARY   13, 2001  $17000.00
Alexander            JANUARY   03, 2006   $9000.00

Le premier TO_CHAR est utilisé pour convertir la date de location au format de date MONTH JJ, AAAA, c'est-à-dire le mois épelé et complété par des espaces, suivi du jour à deux chiffres du mois, puis de l'année à quatre chiffres. Si vous préférez afficher le nom du mois en casse mixte (c'est-à-dire «Décembre»), utilisez simplement cette casse dans l'argument format: ('Month DD, YYYY').

La deuxième fonction TO_CHAR de la Figure 10-39 est utilisée pour formater le SALAIRE afin d'afficher le signe de la devise et deux décimales.

Oracle propose un ensemble complet de modèles de format. Le tableau ci-dessous montre la liste des modèles de format qui peuvent être utilisés pour taper les valeurs de date et de nombre sous forme de caractère à l'aide de TO_CHAR.

Modèle de format La description
,(virgule) Il renvoie une virgule à la position spécifiée. Vous pouvez spécifier plusieurs virgules dans un modèle de format numérique. Restrictions: Un élément virgule ne peut pas commencer un modèle de format numérique. Une virgule ne peut pas apparaître à droite d'un caractère décimal ou d'un point dans un modèle de format numérique.
.(période) Renvoie un point décimal, qui est un point (.) À la position spécifiée. Restriction: vous ne pouvez spécifier qu'une seule période dans un modèle de format numérique
$ Renvoie la valeur avec un signe dollar en tête
0 Renvoie les zéros non significatifs. Renvoie des zéros à la fin.
9 Renvoie la valeur avec le nombre de chiffres spécifié avec un espace de début si positif ou avec un moins de début si négatif. Les zéros non significatifs sont vides, sauf pour une valeur zéro, qui renvoie un zéro pour la partie entière du nombre à virgule fixe.
B Renvoie des espaces pour la partie entière d'un nombre à virgule fixe lorsque la partie entière est égale à zéro (indépendamment des "0" dans le modèle de format).
C Renvoie à la position spécifiée le symbole monétaire ISO (la valeur actuelle du paramètre NLS_ISO_CURRENCY).
ré Renvoie à la position spécifiée le caractère décimal, qui est la valeur actuelle du paramètre NLS_NUMERIC_CHARACTER. La valeur par défaut est un point (.). Restriction: Vous ne pouvez spécifier qu'un seul caractère décimal dans un modèle de format numérique.
EEE Renvoie une valeur en utilisant la notation scientifique.
FM Renvoie une valeur sans espaces de début ou de fin.
g Renvoie à la position spécifiée le séparateur de groupe (la valeur actuelle du paramètre NLS_NUMERIC_CHARACTER). Vous pouvez spécifier plusieurs séparateurs de groupe dans un modèle de format numérique. Restriction: Un séparateur de groupe ne peut pas apparaître à droite d'un caractère décimal ou d'un point dans un modèle de format numérique
L Renvoie à la position spécifiée le symbole de la devise locale (la valeur actuelle du paramètre NLS_CURRENCY).
MI Renvoie une valeur négative avec un signe moins à la fin (-). Renvoie une valeur positive avec un blanc à la fin. Restriction: L'élément de format MI ne peut apparaître qu'à la dernière position d'un modèle de format numérique.
PR Renvoie une valeur négative dans. Il ne peut apparaître qu'à la fin d'un modèle de format numérique.
RN, rm Renvoie une valeur sous forme de chiffres romains en majuscules. Renvoie une valeur sous forme de chiffres romains en minuscules. La valeur peut être un entier compris entre 1 et 3999.
S Renvoie une valeur négative avec un signe moins de début ou de fin (-). Renvoie une valeur positive avec un signe plus au début ou à la fin (+). Restriction: L'élément de format S ne peut apparaître que dans la première ou la dernière position d'un modèle de format numérique.
TM "Texte minimum". Renvoie (en sortie décimale) le plus petit nombre de caractères possible. Cet élément est insensible à la casse.
U Renvoie à la position spécifiée le symbole de double devise «Euro» (ou autre) (la valeur actuelle du paramètre NLS_DUAL_CURRENCY).
V Renvoie une valeur multipliée par 10n (et si nécessaire, arrondissez-la), où n est le nombre de 9 après le «V».
X Renvoie la valeur hexadécimale du nombre de chiffres spécifié.

TO_NUMBER, fonction

La fonction TO_NUMBER convertit une valeur de caractère en un type de données numérique. Si la chaîne en cours de conversion contient des caractères non numériques, la fonction renvoie une erreur.

Syntaxe

TO_NUMBER (string1, [format], [nls_parameter])

Le tableau ci-dessous montre la liste des modèles de format qui peuvent être utilisés pour typer les valeurs de caractères sous forme de nombre à l'aide de TO_NUMBER.

Modèle de format La description
CC Siècle
SCC Century BC préfixé par -
AAAA Année à 4 chiffres
SYYY Année BC préfixée par -
IYYY Année ISO avec 4 chiffres
YY Année avec 2 chiffres
RR Année avec 2 chiffres avec compatibilité An 2000
AN Année en caractères
SYEAR Année en caractères, BC préfixée par -
avant JC Indicateur BC / AD
Q Trimestre en chiffres (1,2,3,4)
MM Mois de l'année 01, 02 ... 12
MOIS Mois en caractères (ex. Janvier)
LUN JAN, FÉVRIER
WW Numéro de semaine (ie 1)
W Numéro de semaine du mois (soit 5)
IW Numéro de semaine de l'année dans la norme ISO.
DDD Jour de l'année en chiffres (soit 365)
DD Jour du mois en chiffres (soit 28)
ré Jour de la semaine en chiffres (soit 7)
JOURNÉE Jour de la semaine en caractères (c.-à-d. Lundi)
FMDAY Jour de la semaine en caractères (c.-à-d. Lundi)
DY Jour de la semaine avec une brève description des caractères (ex: SOLEIL)
J Jour julien (nombre de jours depuis le 1er janvier 4713 avant JC, où le 1er janvier 4713 avant JC est 1 dans Oracle)
HH, H12 Numéro d'heure du jour (1-12)
HH24 Numéro d'heure du jour avec notation 24 heures (0-23)
MATIN APRÈS-MIDI AM ou PM
MANQUER Nombre de minutes et secondes (soit 59),
SSSSS Nombre de secondes ce jour.
DS Format de date court. Dépend des paramètres NLS. Utilisez uniquement avec horodatage.
DL Format de date long. Dépend des paramètres NLS. Utilisez uniquement avec horodatage.
E Nom abrégé de l'ère. Valable uniquement pour les calendriers: Japanese Imperial, ROC Official, Thai Buddha.
EE Le nom complet de l'ère
FF Les fractions de seconde. Utilisez avec horodatage.
FF1..FF9 Les fractions de seconde. Utilisez avec horodatage. Le chiffre contrôle le nombre de chiffres décimaux utilisés pour les fractions de seconde.
FM Mode de remplissage: supprime les blancs en sortie de conversion
FX Format exact: nécessite une correspondance de modèle exacte entre les données et le modèle de format.
IYY OU IY OU I Les 3,2,1 derniers chiffres de l'année standard ISO. Sortie uniquement
RM La représentation en chiffres romains du mois (I .. XII)
RR Les 2 derniers chiffres de l'année.
RRRR Les 2 derniers chiffres de l'année lorsqu'ils sont utilisés pour la sortie. Accepte les années à cinq chiffres lorsqu'il est utilisé pour la saisie.
SP Format orthographié. Peut apparaître à la fin d'un élément numérique. Le résultat est toujours en anglais. Par exemple, le mois 10 au format MMSP renvoie «dix»
SPTH Format orthographique et ordinal; 1 résultats en premier.
TH Convertit un nombre en son format ordinal. Par exemple, 1 devient 1er.
TS Format de temps court. Dépend des paramètres NLS. Utilisez uniquement avec horodatage.
TZD Nom abrégé du fuseau horaire. c'est-à-dire PST.
TZH, TZM Décalage heure / minute du fuseau horaire.
TZR Région du fuseau horaire
X Caractère de base local. En Amérique, c'est une période (.)

Les requêtes SELECT ci-dessous acceptent les nombres comme entrées de caractères et les imprime en suivant le spécificateur de format.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Fonction TO_DATE

La fonction prend des valeurs de caractère en entrée et renvoie une date formatée équivalente à celle-ci. La fonction TO_DATE permet aux utilisateurs d'entrer une date dans n'importe quel format, puis convertit l'entrée dans le format par défaut utilisé par Oracle 11g.

Syntaxe:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Un argument format_mask consiste en une série d'éléments représentant exactement à quoi les données doivent ressembler et doivent être saisis entre guillemets simples.

Modèle de format La description
AN Année, en toutes lettres
AAAA Année à 4 chiffres
YYY, YY, Y 3, 2 ou 1 dernier (s) chiffre (s) de l'année.
IYY, IY, je 3, 2 ou 1 dernier (s) chiffre (s) de l'année ISO.
IYYY Année à 4 chiffres basée sur la norme ISO
RRRR Accepte une année à 2 chiffres et renvoie une année à 4 chiffres.
Q Trimestre d'année (1, 2, 3, 4; JAN-MAR = 1).
MM Mois (01-12; JAN = 01).
LUN Nom abrégé du mois.
MOIS Nom du mois, rempli de blancs jusqu'à 9 caractères.
RM Mois en chiffres romains (I-XII; JAN = I).
WW Semaine de l'année (1-53) où la semaine 1 commence le premier jour de l'année et se poursuit jusqu'au septième jour de l'année.
W Semaine du mois (1-5) où la semaine 1 commence le premier jour du mois et se termine le septième.
IW Semaine de l'année (1-52 ou 1-53) selon la norme ISO.
ré Jour de la semaine (1-7).
JOURNÉE Nom du jour.
DD Jour du mois (1-31).
DDD Jour de l'année (1-366).
DY Nom abrégé du jour.
J Jour Julien; le nombre de jours depuis le 1er janvier 4712 avant JC.
HH12 Heure de la journée (1-12).
HH24 Heure de la journée (0-23).
MANQUER Minute (0-59).
SSSSS Secondes après minuit (0-86399).
FF Fractions de secondes. Utilisez une valeur de 1 à 9 après FF pour indiquer le nombre de chiffres dans les fractions de seconde. Par exemple, «FF4».
MATIN APRÈS-MIDI Indicateur méridien
AD, BC Indicateur AD, BC
TZD Informations sur l'heure d'été. Par exemple, «PST»
TZH, TZM, TZR Heure / minute / région du fuseau horaire.

L'exemple suivant convertit une chaîne de caractères en une date:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Fonctions générales

Les fonctions générales sont utilisées pour gérer les valeurs NULL dans la base de données. L'objectif des fonctions générales de gestion NULL est de remplacer les valeurs NULL par une autre valeur. Nous verrons brièvement ces fonctions ci-dessous.

NVL

La fonction NVL remplace une valeur alternative par une valeur NULL.

Syntaxe:

NVL( Arg1, replace_with )

Dans la syntaxe, les deux paramètres sont obligatoires. Notez que la fonction NVL fonctionne avec tous les types de types de données. Et aussi que le type de données de la chaîne d'origine et le remplacement doivent être dans un état compatible, c'est-à-dire identiques ou implicitement convertibles par Oracle.

Si arg1 est une valeur de caractère, alors oracle convertit la chaîne de remplacement en type de données compatible avec arg1 avant de les comparer et renvoie VARCHAR2 dans le jeu de caractères de expr1. Si arg1 est numérique, alors Oracle détermine l'argument avec la priorité numérique la plus élevée, convertit implicitement l'autre argument en ce type de données et renvoie ce type de données.

L'instruction SELECT ci-dessous affichera «n / a» si un employé n'a encore été affecté à aucun travail, c'est-à-dire que JOB_ID est NULL. Sinon, il afficherait la valeur JOB_ID réelle.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

En tant qu'amélioration par rapport à NVL, Oracle a introduit une fonction pour remplacer la valeur non seulement pour les valeurs de colonnes NULL, mais également pour les colonnes NOT NULL. La fonction NVL2 peut être utilisée pour remplacer une valeur alternative pour NULL ainsi qu'une valeur non NULL.

Syntaxe:

NVL2( string1, value_if_NOT_null, value_if_null )

L'instruction SELECT ci-dessous afficherait «Bench» si le JOB_CODE d'un employé est NULL. Pour une valeur définie non nulle de JOB CODE, il afficherait la valeur constante «Job Assigned».

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

La fonction NULLIF compare deux arguments expr1 et expr2. Si expr1 et expr2 sont égaux, il renvoie NULL; sinon, il renvoie expr1. Contrairement à l'autre fonction de gestion NULL, le premier argument ne peut pas être NULL.

Syntaxe:

NULLIF (expr1, expr2)

Notez que le premier argument peut être une expression évaluée à NULL, mais il ne peut pas être le littéral NULL. Les deux paramètres sont obligatoires pour l'exécution de la fonction.

La requête ci-dessous renvoie NULL puisque les deux valeurs d'entrée, 12 sont égales.

SELECT	NULLIF (12, 12)
FROM DUAL;

De même, la requête ci-dessous renvoie «SUN» car les deux chaînes ne sont pas égales.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

SE FONDRE

La fonction COALESCE, une forme plus générique de NVL, renvoie la première expression non nulle de la liste d'arguments. Il prend au minimum deux paramètres obligatoires mais le maximum d'arguments n'a pas de limite.

Syntaxe:

COALESCE (expr1, expr2, ... expr_n )

Considérez la requête SELECT ci-dessous. Il sélectionne la première valeur non nulle introduite dans les champs d'adresse d'un employé.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Fait intéressant, le fonctionnement de la fonction COALESCE est similaire à la construction IF..ELSIF..ENDIF. La requête ci-dessus peut être réécrite comme -

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Fonctions conditionnelles

Oracle fournit les fonctions conditionnelles DECODE et CASE pour imposer des conditions même dans une instruction SQL.

La fonction DECODE

La fonction est l'équivalence SQL de l'instruction procédurale conditionnelle IF..THEN..ELSE. DECODE fonctionne avec des valeurs / colonnes / expressions de tous les types de données.

Syntaxe:

DECODE (expression, search, result [, search, result]... [, default])

La fonction DECODE compare l'expression à chaque valeur de recherche dans l'ordre. S'il existe une égalité entre l'expression et l'argument de recherche, il renvoie le résultat correspondant. En cas de non-correspondance, la valeur par défaut est renvoyée, si elle est définie, sinon NULL. En cas de non-concordance de compatibilité de type, oracle effectue en interne une conversion implicite possible pour renvoyer les résultats.

En fait, Oracle considère que deux valeurs nulles sont équivalentes tout en travaillant avec la fonction DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Si expression est nulle, Oracle renvoie le résultat de la première recherche qui est également nul. Le nombre maximum de composants dans la fonction DECODE est de 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Expression CASE

Les expressions CASE fonctionnent sur le même concept que DECODE mais diffèrent dans la syntaxe et l'utilisation.

Syntaxe:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

La recherche Oracle commence à partir de la gauche et se déplace vers la droite jusqu'à ce qu'elle trouve une condition vraie, puis renvoie l'expression de résultat qui lui est associée. Si aucune condition n'est vérifiée et qu'une clause ELSE existe, Oracle renvoie le résultat défini avec else. Sinon, Oracle renvoie null.

Le nombre maximal d'arguments dans une expression CASE est de 255. Toutes les expressions sont prises en compte dans cette limite, y compris l'expression initiale d'une expression CASE simple et l'expression facultative ELSE. Chaque paire WHEN ... THEN compte pour deux arguments. Pour éviter de dépasser cette limite, vous pouvez imbriquer des expressions CASE de sorte que return_expr elle-même soit une expression CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1