Le blog de Pacman

Die Softwareentwickler mögen Schleife und Cursors über alles, und finden desshalb die unwahrscheinlichsten Rechtfertigungen um die globale DML query zu vermeiden.

Unter denen gibt's die Handlung der Fehlerfällen :

"Neee, die ganze Anfrage darf nicht Rollbacked werden, nur wegen einer verdammten Zeile"

Aber seit 10gR2 gilt dieses Argument (fast) nicht mehr, denn es gibt jetzt die ERROR LOGGING !

Versuchen wir's mal mit verschiedenen Fehler Gründen...

1) Die Testtabelle :

 CREATE TABLE testlog (

 id NUMBER UNIQUE,

 n NUMBER CHECK(n > 0),

 c VARCHAR2(10) NOT NULL);

2) Die Errorlogtabelle :

EXEC dbms_errlog.create_error_log('TESTLOG', 'TESTERR')

3) Einfügen

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 2 THEN 1 ELSE level END, --die zweite Zeile wird unique contraint verletzen

  3         CASE level WHEN 4 THEN -1 ELSE level END, --die vierte verletzt die positiv check bedingung

  4         CASE level WHEN 6 THEN null WHEN 8 THEN lpad('x', 12, 'x') ELSE 'd' END --die sechte die NOT NULL bedingung und die achte überschreitet die maximale Länge

  5  FROM DUAL

  6  CONNECT BY level <= 12

  7  LOG ERRORS INTO TESTERR ('instest') REJECT LIMIT UNLIMITED;

8 ligne(s) créée(s).

SQL> SELECT *

  2  FROM testlog

  3  /

  ID    N C

---- ---- --------------------

   1    1 d

   3    3 d

   5    5 d

   7    7 d

   9    9 d

  10   10 d

  11   11 d

  12   12 d

=> 8 der 12 zeilen sind erfolgreich hinzufügen worden !

SQL> SELECT * FROM testerr;

Und natürlich klappt  es auch mit UPDATE, MERGE UND DELETE !

SQL> MERGE INTO testlog a

  2  USING (SELECT level l,

  3             CASE level WHEN 4 THEN -1 ELSE level + 1 END n,

  4             CASE level WHEN 5 THEN null ELSE 'c' END c

  5         FROM DUAL CONNECT BY LEVEL <= 13) t

  6  ON (a.id = t.l)

  7  WHEN MATCHED THEN UPDATE SET a.n = t.n, a.c = t.c

  8  WHEN NOT MATCHED THEN INSERT (a.id, a.n, a.c) VALUES(t.l, t.n, t.c)

  9  LOG ERRORS INTO TESTERR('testmerge') REJECT LIMIT UNLIMITED;

11 lignes fusionnées.

SQL> SELECT * FROM testlog;

  ID    N C

---- ---- --------------------

   1    2 c

   3    4 c

   5    5 d

   7    8 c

   9   10 c

  10   11 c

  11   12 c

  12   13 c

   2    3 c

   6    7 c

   8    9 c

  13   14 c

SQL> SELECT ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_TAG$

  2  FROM testerr;

4) Trotzdem ein Paar Schertze

Manchmal werden die unique contraints nur "am Ende" überprüft : in diese Fälle scheitert die ganze Anfrage.

Zum beispiel den direct path load :

SQL> INSERT INTO testlog

  2  SELECT /*+APPEND*/ CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

INSERT INTO testlog

*

ERREUR à la ligne 1 :

ORA-00001: unique constraint (EDGE_ADM.SYS_C00356572) violated

Ohne den append hint klappt's prima :

SQL> INSERT INTO testlog

  2  SELECT CASE level WHEN 10 THEN 1 ELSE level END, level, 'c'

  3  FROM DUAL

  4  CONNECT BY level <= 10

  5  LOG ERRORS INTO testerr ('rarghl') REJECT LIMIT UNLIMITED;

1 ligne créée.

Aujourd'hui, un thème assez banal : tester si une chaîne de caractères est un nombre !
C'est à priori pas super passionnant, mais l'objectif ici est surtout de détailler l'une des méthodes qui est souvent baclée.

On va donc explorer rapidement trois possibilités :
1) Essayer... et rattraper l'exception
2) Tester avec une expression régulière
3) La méthode translate

1) La grande classique

La méthode que l'on retrouve le plus souvent consiste à créer une fonction qui tente simplement de caster la chaîne en nombre.
Si l'opération échoue, on rattrappe l'exception.

La fonction :

CREATE OR REPLACE FUNCTION IS_NUMBER ( p_str IN VARCHAR2 ) RETURN NUMBER

IS

v_dummy NUMBER;

BEGIN

   v_dummy := TO_NUMBER(p_str);

Le test :

SQL> SELECT is_number('robert') as notnum, is_number('123,43') as num

  2  FROM DUAL

  3  /

    NOTNUM        NUM

---------- ----------

        -1          0

Cependant, on entend souvent rétorquer à cette méthode "Mais euh, je n'ai pas le droit de créer de fonction sur ma base !"
D'où la suite...

2) Les expression régulières

Avec les expression régulières, on peut tester la "tronche" de la chaîne : plus que les caractères qu'elle contient, l'expression régulière teste également "l'agencement"...
A partir d'Oracle 10g, on a droit à REGEXP_LIKE !

Note importante : REGEXP_LIKE renvoie un booléen, qui n'est pas un type qui peut être renvoyé par l'instruction SELECT d'Oracle.
C'est pour cela qu'il faut la traiter avec un CASE WHEN...

3) Translate !

La méthode TRANSLATE, c'est simple :
En gros, on prend la chaîne, on remplace tous les chiffres par des blancs, et s'il ne reste que des blancs à la fin, c'est un nombre.

SELECT CASE WHEN trim(translate('1234,5', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as num

, CASE WHEN trim(translate('robert', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as notnum

FROM DUAL

/

       NUM     NOTNUM

---------- ----------

         1          0

Seulement voilà, le résultat est assez inexact, car on teste les caractères contenus, mais pas le séquencement !

Et c'est en fait la seule raison plus ou moins valable de s'étendre sur ce sujet maintes fois traité... faisons donc l'effort de détailler cela en détail.

a. Les espaces

Dans le premier exemple de translate, on ne teste pas les espaces.
Pourtant, les formats disponibles pour to_number ne permettent pas d'intercaler des espaces entre les chiffres...

Par exemple, le translate donne :

SQL> ed

Úcrit fichier afiedt.buf

  1  SELECT CASE WHEN trim(translate('12 345', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res

  2* FROM DUAL

SQL> /

       RES

----------

         1

Alors que le to_number vautre :

SQL> SELECT to_number('12 345') FROM dual;

SELECT to_number('12 345') FROM dual

                 *

ERREUR Ó la ligne 1 :

ORA-01722: invalid number

En fait, les espaces "trailing" et "leading" sont les seuls acceptés.
On peut donc simplement comparer la taille de la chaîne "trimée" à la taille de la chaine dépouillée de tous les espaces.

b. Nombre d'occurences des caractères spéciaux

La version basique de la méthode translate se contente d'accepter une liste de caractères.
Cependant, certains caractères tels que le séparateur de décimales ou le signe doivent apparaître au maximum une fois.

SQL> ed

Úcrit fichier afiedt.buf

  1  SELECT CASE WHEN trim(translate(' 12 345 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length(trim(' 12 345 ')), 0) - coalesce(length(replace(' 12 345 ', ' ', '')), 0)

  3  WHEN 0 THEN 1 ELSE 0 END as res2

  4* FROM DUAL

SQL> /

       RES       RES2

---------- ----------

         1          0

Pour tester le nombre d'occurences d'un caractère, il suffit de comparer la longueur de la chaîne de départ à la longueur de la chaîne épurée des dits caractères :

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(length('12,34,5'), 0) - coalesce(length(replace('12,34,5', ',', '')), 0) > 1 THEN 0 ELSE 1 END as res3

  3  FROM DUAL

  4  /

       RES       RES3

---------- ----------

         1          0

c. Restrictions sur la position d'un caractère spécifique

Les restrictions suivantes doivent être appliquées :
- le caractère "e" de l'écriture scientifique ne doit pas être en dernière position
- les caractères + et - doivent se trouver en première position

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE coalesce(length('12345e'), 0) - coalesce(instr('12345e', 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END as res4,

  3  CASE WHEN coalesce(instr('123+45', '+'), 0)  > 1 THEN 0 ELSE 1 END as res5

  4  FROM DUAL

  5  /

       RES       RES4       RES5

---------- ---------- ----------

         1          0          0

d. La position relative de certains caractères

Le séparateur de décimales doit apparaître avant le caractère "e" :

SQL> SELECT CASE WHEN trim(translate('12,34,5 ', '1234567890,', ' ')) IS NULL THEN 1 ELSE 0 END as res,

  2  CASE WHEN coalesce(instr('12e3,45', ','), 0) <= coalesce(instr('12e3,45', 'e'), 0)  THEN 1 ELSE 0 END as res4

  3  FROM DUAL;

       RES       RES4

---------- ----------

         1          0

 
Allez, si on combine toutes ces conditions, on devrait y arriver :

SQL> WITH testeuh AS
  2  (SELECT '123' AS c FROM DUAL UNION ALL
  3  SELECT '12,3' AS c FROM DUAL UNION ALL
  4  SELECT '+12,3' AS c FROM DUAL UNION ALL
  5  SELECT '+123,1e5' AS c FROM DUAL UNION ALL
  6  SELECT '123+5' AS c FROM DUAL UNION ALL
  7  SELECT '125,5,' AS c FROM DUAL UNION ALL
  8  SELECT '125e,5' AS c FROM DUAL UNION ALL
  9  SELECT '1 125,5' AS c FROM DUAL
 10  )
 11  SELECT c,
 12  CASE WHEN trim(translate(c, '1234567890,+-e', ' ')) IS NULL THEN 1 ELSE 0 END
 13  * CASE coalesce(length(trim(c)), 0) - coalesce(length(replace(c, ' ', '')), 0) WHEN 0 THEN 1 ELSE 0 END
 14  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, ',', '')), 0) > 1 THEN 0 ELSE 1 END
 15  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '+', '')), 0) > 1 THEN 0 ELSE 1 END
 16  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, '-', '')), 0) > 1 THEN 0 ELSE 1 END
 17  * CASE WHEN coalesce(length(c), 0) - coalesce(length(replace(c, 'e', '')), 0) > 1 THEN 0 ELSE 1 END
 18  * CASE coalesce(length(c), 0) - coalesce(instr(c, 'e'), 0)  WHEN 0 THEN 0 ELSE 1 END
 19  * CASE WHEN coalesce(instr(c, '+'), 0)  > 1 THEN 0 ELSE 1 END
 20  * CASE WHEN coalesce(instr(c, '-'), 0)  > 1 THEN 0 ELSE 1 END
 21  * CASE WHEN coalesce(instr(c, ','), 0) <= coalesce(instr(c, 'e'), 0) OR coalesce(instr(c, 'e'), 0) = 0 THEN 1 ELSE 0 END AS isnumeric
 22  FROM testeuh
 23  / 

C         ISNUMERIC
-------- ----------
123               1
12,3              1
+12,3             1
+123,1e5          1
123+5             0
125,5,            0
125e,5            0
1 125,5           0 

Une grande dédicace à Mr X pour sa contribution.
(Je n'ai pas le droit de le citer...)

La question du jour :
Est-il équivalent de placer son prédicat (mono table) de jointure externe dans la clause LEFT OUTER JOIN, ou dans le WHERE en permettant les NULL ?
(En fait, c'est même la question du mois vu que je n'ai pas été capable de finir un article depuis longtemps...)

Ben non !

Et il y a plusieurs raisons, que nous illustrerons par l'exemple.

Dans la suite de l'article, il y aura :

0) Mise en jambes, jeux de tests

1) Si la colonne que vous voulez filtrer sur le résultat est nullable (peut prendre la "valeur" NULL), alors il peut y avoir confusion entre le NULL de "il n'y a pas de lignes qui matchent la correspondance" et "la colonne de la ligne répondant au critère de jointure est NULL"

2) Si une et une seule ligne de la table de droite (dans une jointure LEFT) répond au critère de jointure mais prend une valeur non NULL pour la colonne filtrée : 0 lignes renvoyées au lieu d'une !

3) Ca n'a rien à voir, mais un petit plébiscite pour la syntaxe ANSI contre la version dépréciée Oracle

Les jeux de tests : une table maître, une table détail. Une seule ligne de détail pour Marcel, 3 lignes de détail pour Robert, 0 pour Squelettor.

CREATE TABLE TEST_LJ_MAITRE AS
SELECT 1 AS idm, 'Robert' as nom FROM DUAL UNION ALL
SELECT 2 AS idm, 'Marcel' as nom FROM DUAL UNION ALL
SELECT 3 AS idm, 'Squelettor' as nom FROM DUAL;

CREATE TABLE TEST_LJ_DETAIL AS
SELECT 1 AS ids, 1 as idm, 'M' as s FROM DUAL UNION ALL
SELECT 2 AS ids, 1 as idm, 'F' as s  FROM DUAL UNION ALL
SELECT 3 AS ids, 1 as idm, NULL as s  FROM DUAL UNION ALL
SELECT 4 AS ids, 2 as idm, 'F' as s  FROM DUAL;

La jointure externe, c'est comme une jointure normale, sauf que les lignes de gauche (pour LEFT, et droite pour RIGHT...) sont dans le résultat même si aucune ligne de la table de droite ne correspond au critères spécifiés dans la clause "ON".

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              2          1 F
         1 Robert              3          1
         2 Marcel              4          2 F
         3 Squelettor

Au passage, on rappelle que :
- INNER JOIN est strictement équivalent à JOIN
- LEFT OUTER JOIN est strictement équivalent à LEFT JOIN

Personnellement, j'utilise JOIN et LEFT OUTER JOIN. Non pas par paresse, mais parce que dans la théorie des ensembles, une "relation" est naturellement "INNER"... alors que la jointure externe est plus difficile à rapprocher d'un concept mathématique simple.
Ainsi, je trouve que LEFT JOIN peut donner l'illusion pour les débutants que l'ordre des tables dans une jointure "INNER" à une signification, alors que ce n'est bien évidemment pas le cas. C'est pour cela que je précise le OUTER.

Après, c'est une histoire de goûts...

On veut à présent récupérer les relations des MAITREs avec des DETAILs masculins (DETAIL.s = 'M'), et ceux qui ne sont pas en relation avec des DETAILs masculins (qui du coup auront les colonnes DETAIL à NULL)

Première tentative :

SQL> SELECT *
  2  FROM TEST_LJ_MAITRE a
  3    LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4  WHERE b.s = 'M'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M

Mon dieu, tout a disparu !
Ben oui, pour les MAITRE sans détail, le prédicat b.s = 'M' est faux !

Deuxième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s <> 'F'
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M

Ca ne marche pas mieux. Ben ouais, NULL, ce n'est pas vraiment une valeur.
Quand b.s IS NULL, la question b.s <> 'F' a pour réponse : indéterminé !

Troisième tentative :

SQL> SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4   WHERE b.s = 'M' OR b.s IS NULL
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         1 Robert              3          1
         3 Squelettor

On a certes ramené Squelettor... mais on ramené une deuxième ligne pour Robert, avec une relation de s. indéterminé !

Quatrième tentative :
On se dit que ce qui caractérise le côté ouvert, c'est que l'identifiant de la table externe soit NOT NULL. Et donc, on va compléter la condition précédente :

SQL> SELECT *
  2    FROM TEST_LJ_MAITRE a
  3      LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4    WHERE b.s = 'M' OR (b.s IS NULL AND b.ids IS NULL)
  5  /

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         3 Squelettor

Presque !
Mais le pauvre Marcel est passé à la trappe. Et je trouve que c'est le plus gros piège, car on pense souvent à inclure le NULL dans la clause WHERE... mais c'est vraiment insuffisant.
Ici, Marcel est en relation avec un seul DETAIL, mais de s = F.
Et donc, il passe la jointure
  "LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm"
Mais est refoulé dans le WHERE, quelle que soit la manière dont on le fasse !

Et du coup, quand un critère est vraiment "externe", il faut le mettre dans la clause ON qui est faite pour cela !

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a
  3     LEFT OUTER JOIN TEST_LJ_DETAIL b ON a.idm = b.idm
  4                      AND b.s = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Pas de discrimination, quelle que soit l'orientation !

Pour finir, la syntaxe "old school" :
Mettre un (+) dans les prédicats du WHERE.

SQL>  SELECT *
  2   FROM TEST_LJ_MAITRE a, TEST_LJ_DETAIL b
  3   WHERE a.idm = b.idm(+)
  4     AND b.s(+) = 'M';

       IDM NOM               IDS        IDM S
---------- ---------- ---------- ---------- -
         1 Robert              1          1 M
         2 Marcel
         3 Squelettor

Et effectivement, ça marche aussi.

Mais il faut rappeler que si cette syntaxe est disponible sous Oracle, ce n'est pas juste pour se démarquer des autres... mais simplement parce qu'elle a été mise en place avant la norme SQL 92 qui a standardisé les JOIN. (Attention, je n'ai pas dit légalisé, ni dépénalisé)

La preuve, c'est que même Oracle recommande la syntaxe normalisée :

Oracle Corporation recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause join syntax:[...]

(Ok, c'est un peu agressif le rouge, mais c'est pour bien insister !)

Tiré d'ici :
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054625

Voilà, Voilà !