PL/SQL Upsert

Of course, if you're on version 9 of Oracle, you should be using the MERGE command to perform an efficient upsert. Even before version 9, the most efficient mechanism for doing an upsert is run just pure SQL. However, time and time again, we often see PL/SQL being used to upsert records with the following pseudo-code

• See if the record exists
• If yes, then issue an update statement
• If no, then issue an insert statement

Here's an example I picked out of a client system just today (with some name changing to protect the guilty party)

   CURSOR    cur_recipient IS
     SELECT  ...
     FROM    my_table
     WHERE   ...
     FOR UPDATE OF mast_id;
 
     OPEN  cur_recipient;
     FETCH cur_recipient
     INTO  v_recip;
 
     IF cur_recipient%FOUND THEN
       UPDATE  my_table
       SET     mast_id = :b1
       WHERE   CURRENT OF cur_recipient;
       CLOSE   cur_recipient;
     ELSE
       CLOSE  cur_recipient;
       INSERT INTO my_table
       VALUES ....
     END IF;

Even if for some obtuse reason this cannot be re-done in pure SQL, you still do NOT need three steps to perform this process in PL/SQL. You can use cursor attributes more intelligently to reduce this to two steps

• Attempt the update statement
• If the update 'fails', then issue an insert statement

The example above would be re-written as follows

       UPDATE  my_table
       SET     mast_id = :b1
       WHERE   ...
       IF SQL%NOTFOUND then
         INSERT INTO    my_table
         VALUES ....
       END IF;

Easier to code, easier to read, and does less work... You can then improve it further by coding based on whether an update or insert is more probable - if you will typically be updating, then you code the example as above. If you are typically inserting, then you code the insert first, catch the constraint exception and then issue an update.