Oracle : comment INSÉRER si une ligne n'existe pas

Question

Quel est le moyen le plus simple d'INSÉRER une ligne si elle n'existe pas, en PL/SQL (oracle) ?

Je veux quelque chose comme :

 IF NOT EXISTS (SELECT * FROM table WHERE name = 'jonny') THEN
  INSERT INTO table VALUES ("jonny", null);
END IF;

Mais ça ne marche pas.

Remarque : cette table comporte 2 champs, par exemple, le nom et l' âge . Mais le seul nom est PK.

Answer 1

INSERT INTO table
SELECT 'jonny', NULL
  FROM dual -- Not Oracle? No need for dual, drop that line
 WHERE NOT EXISTS (SELECT NULL -- canonical way, but you can select
                               -- anything as EXISTS only checks existence
                     FROM table
                    WHERE name = 'jonny'
                  )

Answer 2

En supposant que vous soyez sur 10g, vous pouvez également utiliser l'instruction MERGE. Cela vous permet d'insérer la ligne si elle n'existe pas et d'ignorer la ligne si elle existe. Les gens ont tendance à penser à MERGE lorsqu'ils veulent faire un "upsert" (INSERT si la ligne n'existe pas et UPDATE si la ligne existe) mais la partie UPDATE est maintenant facultative, elle peut donc également être utilisée ici.

 SQL> create table foo (
  2    name varchar2(10) primary key,
  3    age  number
  4  );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  merge into foo a
  2    using (select 'johnny' name, null age from dual) b
  3       on (a.name = b.name)
  4   when not matched then
  5    insert( name, age)
  6*    values( b.name, b.age)
SQL> /

1 row merged.

SQL> /

0 rows merged.

SQL> select * from foo;

NAME              AGE
---------- ----------
johnny

Answer 3

Si le nom est un PK, il suffit d'insérer et de détecter l'erreur. La raison de faire ceci plutôt que n'importe quelle vérification est que cela fonctionnera même avec plusieurs clients insérant en même temps. Si vous vérifiez puis insérez, vous devez maintenir un verrou pendant ce temps, ou vous attendre à l'erreur de toute façon.

Le code pour cela serait quelque chose comme

 BEGIN
  INSERT INTO table( name, age )
    VALUES( 'johnny', null );
EXCEPTION
  WHEN dup_val_on_index
  THEN
    NULL; -- Intentionally ignore duplicates
END;

Answer 4

En utilisant des parties de la réponse @benoit, j'utiliserai ceci:

 DECLARE
    varTmp NUMBER:=0;
BEGIN
    -- checks
    SELECT nvl((SELECT 1 FROM table WHERE name = 'john'), 0) INTO varTmp FROM dual;

    -- insert
    IF (varTmp = 1) THEN
        INSERT INTO table (john, null)
    END IF;

END;

Désolé car je n'utilise aucune réponse complète, mais j'ai besoin de IF car mon code est beaucoup plus complexe que cet exemple de table avec des champs de nom et d'âge. J'ai besoin d'un code très clair. Et bien merci, j'ai beaucoup appris ! J'accepte la réponse de @benoit.