Oracle: how to INSERT if a row doesn't exist

What is the easiest way to INSERT a row if it doesn't exist, in PL/SQL (oracle)?

I want something like:

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

But it's not working.

Note: this table has 2 fields, say, name and age. But only name is PK.

Solution 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'
                  )

Solution 2:

Assuming you are on 10g, you can also use the MERGE statement. This allows you to insert the row if it doesn't exist and ignore the row if it does exist. People tend to think of MERGE when they want to do an "upsert" (INSERT if the row doesn't exist and UPDATE if the row does exist) but the UPDATE part is optional now so it can also be used here.

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

Solution 3:

If name is a PK, then just insert and catch the error. The reason to do this rather than any check is that it will work even with multiple clients inserting at the same time. If you check and then insert, you have to hold a lock during that time, or expect the error anyway.

The code for this would be something like

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