Les secrets de la fonction DECODE

La fonction decode sous Oracle permet de faire un traitement de conditions sur les données (que ce soit au niveau de la donnée ramenée ou au niveau du filtre de la requête).
Ainsi, on transforme la donnée dans une requête SQL, sans passer par un language informatique supérieur (PL, ou autre) :

decode({valeur de test}, {valeur de comparaison}, {valeur retournée en cas de correspondance}, [{valeur de comparaison}, {valeur retournée en cas de correspondance}, [ ... ]], {valeur retournée si aucune correspondance n'est trouvée})


Soit, par exemple, la table personne :
create table personne (
civilite varchar2(5 char),
nom varchar2(80 char),
prenom varchar2 (80 char),
date_naissance date,
date_deces date
);

alter table personne add constraint chk_choix check (civilite in ('M.', 'Mme', 'Melle'));

alter session set nls_date_format='DD/MM/RRRR';

insert into personne (civilite, prenom, nom, date_naissance, date_deces)
values ('M.', 'John', 'Lennon', '09/10/1940', '08/12/1980');

insert into personne (civilite, prenom, nom, date_naissance, date_deces)
values ('M.', 'James Douglas', 'Morrison', '08/12/1943', '03/07/1971');

insert into personne (civilite, prenom, nom, date_naissance, date_deces)
values ('Melle', 'Britney Jean', 'Spears', '02/12/1981', null);

insert into personne (civilite, prenom, nom, date_naissance, date_deces)
values ('Mme', 'Mariah', 'Carey', '27/03/1970', null);

commit;




select civilite, prenom , nom, date_naissance, date_deces from personne;

CIVIL PRENOM NOM DATE_NAISS DATE_DECES
----- -------------------- -------------------- ---------- ----------
M. John Lennon 09/10/1940 08/12/1980
M. James Douglas Morrison 08/12/1943 03/07/1971
Melle Britney Jean Spears 02/12/1981
Mme Mariah Carey 27/03/1970




Exemple de comparaison simple : Connaître le sexe d'une personne (si civilite = 'M.' alors male, sinon femelle) :


select decode(civilite, 'M.', 'Male', 'Femelle'), nom, prenom from personne;

DECODE( PRENOM NOM
------- -------------------- --------------------
Male John Lennon
Male James Douglas Morrison
Femelle Britney Jean Spears
Femelle Mariah Carey


Créer une phrase permettant d'inviter la personne en prenant en compte son sexe :


select civilite || ' ' || nom || ' ' || prenom || ', vous êtes l''heureu' ||
decode(civilite, 'M.', 'x ', 'se ') || 'gagnant' ||
decode(civilite, 'M.', ' ', 'e ') || 'de notre tombola.'
as invitation from personne;

INVITATION
----------------------------------------------------------------------------------
M. Lennon John, vous êtes l'heureux gagnant de notre tombola.
M. Morrison James Douglas, vous êtes l'heureux gagnant de notre tombola.
Melle Spears Britney Jean, vous êtes l'heureuse gagnante de notre tombola.
Mme Carey Mariah, vous êtes l'heureuse gagnante de notre tombola.



Exemple de comparaison multiple (if elseif elseif else) : écrire la civilité en entier :

select decode(civilite, 'M.', 'Monsieur ', 'Mme', 'Madame ', 'Melle', 'Mademoiselle ', null) || nom || ' ' || prenom as nom_complet from personne;

NOM_COMPLET
---------------------------------------------------------------
Monsieur Lennon John
Monsieur Morrison James Douglas
Mademoiselle Spears Britney Jean
Madame Carey Mariah


Exemple d'une comparaison de valeur (date, nombre, ...) : Connaître la majorité d'une personne :

Ici, il suffit de se dire que si la personne a plus de 18 ans, elle est majeure, sinon, elle est mineure.
Cependant, la fonction decode ne peut prendre en premier argument qu'une expression booléenne évalulée (pas une chaîne de caractères comportant un signe de comparaison comme égalité (=) ; inférieur à (<) ; supérieur à (>) ; différent de (<>) ). L'astuce consiste alors à passer par la fonction sign et transformer les deux membres de la comparaison en une soustraction :


insert into personne (civilite, prenom, nom, date_naissance, date_deces)
values ('M.', 'Pierre', 'Garçon', '12/06/1998', null);
commit;

select prenom || ' ' || nom || ' est ' || decode(sign(MONTHS_BETWEEN(sysdate, date_naissance)-216), 1, 'majeur', 'mineur') || decode(civilite, 'M.', '.', 'e.') as majorite from personne where date_deces is null;

majorite
-----------------
Pierre Garçon est mineur.
Britney Jean Spears est majeure.
Mariah Carey est majeure.




Exemple d'une comparaison de chaîne de caractère comme le LIKE suivi d'une chaine contenant le signe pourcentage : de la même manière que pour le cas précédent, il faut trouver un moyen pour que le premier argument de la fonction decode ne soit qu'un bouléen.Voir les opérations sur les chaînes de caractères.
Par exemple, trouver les personnes commençant par Le (like 'Le%') s'effectuera de la manière suivante :

select decode(substr(nom, 1, 2), 'Le', 'oui', 'non') commence_par_le, nom from personne;

COM NOM
--- --------------------
non Garçon
non Morrison
non Spears
non Carey
oui Lennon


ou, plus complexe encore : like '%ar%' :

select decode(sign(instr(nom, 'ar')),1, 'oui', 'non') contient_ar, nom from personne;

CON NOM
--- --------------------
oui Garçon
non Morrison
oui Spears
oui Carey
non Lennon


Les exemples peuvent se multiplier, cependant, l'utilisation d'un CASE deviendra plus lisible si les tests se complexifient.

La fonction decode accepte, en outre le null ; que ce soit en argument de comparaison ou en argument de retour.
Lorsque que le test s'effectue sur la colonne retournée, le decode s'apparente alors à un coalesce (ou nvl) :

select nom, decode(date_deces, null, 'vivant', to_char(date_deces))
from personne;

NOM DECODE(DAT
-------------------- ----------
Garçon vivant
Morrison 03/07/1971
Spears vivant
Carey vivant
Lennon 08/12/1980

est équivalent à :

select nom, coalesce(to_char(date_deces), 'vivant')
from personne;

NOM COALESCE(T
-------------------- ----------
Garçon vivant
Morrison 03/07/1971
Spears vivant
Carey vivant
Lennon 08/12/1980



Enfin, rien n'interdit d'utiliser une autre colonne (d'une jointure par exemple) ou une sous-requête dans la valeur de retour ou la valeur de comparaison du decode.



Voir également l'utilisation des fonctions de chaînes de caractères (substr, instr, ...)




Vous n'avez pas trouver réponse à votre question ? Préciser votre recherche :

Catégories