Re: PL\SQL optimization for update or insert behavior

Hello!

One more method:

begin
update table QQQ set b=20 where a=10;
if sql%rowcount=0 then

    insert into QQQ(b) values(20) where a=10; end if;
end;

Best regards!
Yaroslav.
Jon <jumper_at_engineer.com> ñîîáùèë â íîâîñòÿõ ñëåäóþùåå:1b9d847.0107250540.789992d1_at_posting.google.com...
> I am trying to create a PL statement that will either update an
> existing record or insert a new record if no existing records are
> found. This will eventually be used in a JDBC PreparedStatement for
> batch processing. Assume that there is a pretty random distribution of
> inserts and updates. I've come up with a few possible ways of doing
> this but I'm not sure which will run faster. The smallest of gains are
> important since it will be run possibly thousands of times (or more).
> The requirements for the project forbid me to use stored procedures so
> that is not an option.
>
> Which of the follow statements would be the most efficient (or is
> there a better way)? Any help would be greatly appreciated.
>
> Jon
>
> --------------- Statement 1: --------
>
>
> DECLARE
> recordFound EXCEPTION;
> spoo ROWID;
> BEGIN
> SELECT ROWID into spoo from JCTEST where account_number = '103';
> raise recordFound;
> EXCEPTION
> when recordFound then
> UPDATE JCTEST SET amount = 23.23 where account_number = '103';
> when no_data_found then
> INSERT into JCTEST VALUES ('103', 101.70);
> END;
>
> -------------- Statement 2 ---------
> DECLARE
> recordFound EXCEPTION;
> spoo ROWID;
> acc varchar2(5):='103';
> amt NUMBER :=101.70;
> BEGIN
> SELECT ROWID into spoo from JCTEST where account_number = acc;
> raise recordFound;
> EXCEPTION
> when recordFound then
> UPDATE JCTEST SET amount = amt where account_number = acc;
> when no_data_found then
> INSERT into JCTEST VALUES (acc, amt);
> END;
>
> ----------- Statement 3 ----------
> DECLARE
> recordFound EXCEPTION;
> spoo NUMBER;
> BEGIN
> SELECT COUNT(*) into spoo from JCTEST where account_number = '103';
> if spoo > 0 then
> UPDATE JCTEST SET amount = 23.23 where account_number = '103';
> else
> INSERT into JCTEST VALUES ('103', 101.70);
> END IF;
> END;
Received on Wed Jul 25 2001 - 09:09:42 CDT