Hello all,
What is the best way, in a PL/SQL program, to check for the existence of a record and create it if it doesn't exist.
There are those 2 ways that I know of:
1) Using the SELECT / EXCEPTION
SELECT <col> INTO <var> FROM table WHERE <onerecordcondition>;
:
:
EXCEPTION
WHEN_NO_DATA_FOUND
I hate that way which breaks the program flow and you have to place BEGIN/EXCEPTION/END statements everywhere where you need to check that condition for different tables. I prefer to check the value of %NOTFOUND right after the statement but that doesn't work with SELECT INTO.
2) Using UPDATE / %NOTFOUND
UPDATE <table> SET <col> = <samecol> WHERE <onerecordcondition>;
IF SQL%NOTFOUND THEN
:
:
End IF;
This keeps the program flow and doesn't require BEGIN/END blocks but it performs an unecessary update to the table.
Is there any other better way to perform such an easy task in PL/SQL ?
Thank you.
p.s. Oracle 9i database.