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
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
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.