Travailler avec NULL

Tester `NULL` - si cela est nécessaire

La plupart du temps, vous n'avez pas à prendre de précaution particulière pour les champs ou variables qui peuvent être NULL. Par exemple, si vous faites ceci :

select * from Clients where Ville = 'Ralston'

vous ne voulez certainement pas voir les clients pour lesquels la ville n'a pas été spécifiée. De même :

if (Age >= 18) then CanVote = 'Yes'

n'inclue pas les gens dont l'âge est inconnu, ce qui semble correct. Mais :

if (Age >= 18) then CanVote = 'Yes';
else CanVote = 'No';

semble moins vrai : si vous ne connaissez pas l'âge d'une personne, vous ne pouvez expressément lui refuser le droit de vote. Pire, cela :

if (Age < 18) then CanVote = 'No';
else CanVote = 'Yes';

n'a pas les mêmes conséquences. Si certain des âges NULL sont en réalité inférieurs à 18, vous allez laisser des mineurs voter!

La bonne approche ici est de tester NULL expressément:

if (Age is null) then CanVote = 'Unsure';
else 
  if (Age >= 18) then CanVote = 'Yes';
  else CanVote = 'No';
Note

else se réfère toujours au dernier if dans le même bloc. Mais il est préférable souvent d'éviter les confusions en utilisant les mots clés begin...end autour des groupes de lignes. Je ne l'ai pas fait ici - je voulais écrire un faible nombre de lignes. Mais du coup j'ai compensé en ajoutant cette note ;-)

Vérifier que des champs sont égaux

Quelques fois vous devez vérifier que deux champs ou variables sont égaux et vous voulez les considérer égaux s'ils sont tous deux NULL. Le test correct pour cela est :

if (A = B or A is null and B is null) then...

ou, si vous préférez :

if ((A = B) or (A is null and B is null)) then...

Attention tout de même: si seulement un des deux (A ou B) est NULL, l'expression de test devient NULL, pas false! C'est correct dans une instruction if , et nous pouvons même ajouter une clause else qui sera exécutée si A et B ne sont pas égaux (incluant le cas où un est NULL et l'autre ne l'est pas):

if (A = B or A is null and B is null) 
  then ...travail à faire si A égal B...
  else ...travail à faire si A et B sont différents...

Mais n'ayez pas la brillante idée d'inverser l'expression et de l'utiliser comme un test d'inégalité (comme je l'ai déjà fait dans le passé):

/* Ne faites pas cela! */
if (not(A = B or A is null and B is null))
  then ...travail à faire si A différent de B...

Le code ci-dessus fonctionnera correctement si A et B sont tous deux NULL ou tous deux non-NULL. Mais la clause then ne s'exécutera pas si un des deux seulement est NULL.

Si vous voulez que quelque chose soit fait seulement si A et B sont différents, utilisez soit une des expressions correctes ci dessus et mettez une expression muette dans la clause then, ou utilisez cette expression de test suivante :

/* Ceci est un test correct d'inégalité: */
if (A <> B
    or A is null and B is not null
    or A is not null and B is null) then...

Vérifier qu'une valeur de champ a changée

Dans les triggers, il est souvent utile de savoir si une valeur de champ a changée (y compris: passer de NULL à non-NULL ou vice versa) ou est restée la même. Ce n'est rien d'autre qu'un cas particulier du test de l'(in)égalité de deux champs. Utilisez juste New.Fieldname et Old.Fieldname pour A et B:

if (New.Job = Old.Job or New.Job is null and Old.Job is null)
  then ...le champ Job est resté le même...
  else ...le champ Job a changé...

Substituer une valeur à `NULL`

La fonction `COALESCE`

Il existe une fonction dans Firebird 1.5 qui convertit NULL en quasiment tout ce que l'on veut. Cela permet de faire une conversion à la volée et utiliser le résultat dans le processus, sans utiliser la construction « if (MyExpression is null) then ». Cette fonction s'appelle COALESCE et s'utilise comme suit :

COALESCE(Expr1, Expr2, Expr3, ...)

COALESCE retourne la première expression non-NULL dans la liste d'arguments. Si toutes les expressions sont NULL, elle renvoie NULL.

Voici comment utiliser COALESCE pour écrire le nom complet d'une personne avec ses prénom, surnom et nom, en supposant que certains surnoms sont NULL:

select Prenom
       || coalesce(' ' || Surnom, '')
       || ' ' || Nom
from Personnes

Ou bien encore en considérant que le surnom et le prénom peuvent être NULL:

select coalesce (Surnom, Prenom, 'Mr/Mme.')
       || ' ' || Nom
from AutresPersonnes

COALESCE ne vous aidera que dans les situations où NULL peut être traité de la même manière qu'une valeur permise pour le type de données. Si NULL a besoin d'un traitement particulier, comme dans l'exemple « droit de vote » utilisé précédemment, votre seule option est d'utiliser « if (MonExpression is null) then ».

Firebird 1.0: les fonctions `*NVL`

Firebird 1.0 ne connaît pas COALESCE. Toutefois, vous pouvez utiliser quatre UDFs qui procurent une bonne partie de ses fonctionnalités. Ces UDFs sont dans la bibliothèque fbudf et sont :

iNVL, pour les integers
i64NVL, pour les bigint
dNVL, pour les double precision
sNVL, pour les chaînes de caractères

Les fonctions *NVL prennent deux arguments. Comme COALESCE, elles renvoient le premier argument s'il n'est pas NULL; sinon, elles renvoient le second. Notez que la bibliothèque de Firebird 1.0 fbudf - et par conséquent, les fonctions *NVL - n'est disponible que pour Windows.