Re: SQL help

On 26.09.2007, Thomas Kellerer <FJIFALSDGVAF_at_spammotel.com> wrote:
> Frank van Bortel wrote on 25.09.2007 20:31:
>> The above is geared towards the expectation you will have
>> more inserts than updates, if you anticipate to do more
>> updates than inserts, reverse the insert and update:
>> - - for all records to be processed
>> - - loop
>> - -- update the record
>> - ---- if the update fails (catch the ORA-01403 exception),
>> - ---- then insert;
>> - -- end loop;
>>
>> The rationale is that trying to insert (or update),
>> catching the error, processing the exception, and
>> subsequently update (or insert) takes more time
>> than doing a successful insert (or update).
>>
>
> If the row does not exists, the UPDATE will not "fail", it will simply return 0
> (zero) rows updated. Not sure how to retrieve the number of updated records in
> PL/SQL, in JDBC this would be
>
> int rowsUpdate = statement.executeUpdate("UPDATE ...");
> if (rowsUpdate == 0)
> {
> statement.executeUpdate("INSERT ....");
> }
>

In PL/SQL:

update ...;
if sql%rowcount = 0 then
  insert ...;
end if;

-- 
Wiktor Moskwa