Utilisation d'expressions conditionnelles

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

Comme 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