Re: "insert or update" in one step?

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 2 Jun 2001 03:39:16 -0500
Message-ID: <xB1S6.544$Ia3.372221@nnrp2.sbc.net>

Martin, thanks for your examples.

you are right about which example to use in which situation. if you performing nearly all inserts, then perform the insert first. if you are mostly updating, then perform the update first.

what if you have a fairly good mix ? performing an insert that raises the ORA-00001 exception actually generates redo. if any constraints have been deferred, an exception may not be raised until a commit is performed.

if you are concerned with avoiding "hung" sessions due to a locked row, then use a cursor FOR UPDATE NOWAIT, which will immediately raise a ORA-00054 exception if a row is locked by another session. a plain old UPDATE statement will wait, and wait, and wait... for most of our applications, it is better to catch the -54, and let the app handle retry logic. the app already has to deal with a lot of other oracle exceptions. sometimes, we catch the -54 exception, and translate this to a function return code (or an OUT argument)

for some tables, the app doesn't care what values are in the columns, we app wants to replace them. a lot of times, they are updating the row with the exact values that are already in the row, and that generates rollback and redo. so i like to have an opportunity to compare the values in the columns with the new values, and determine whether an update is actually required or not.

here is an quick example of the approach i prefer to use in some cases. it looks like more code, but in testing, it actually provides better performance (primarily due to a reduction in the amount of rollback and redo generated.)

PROCEDURE upsert_mytable

(as_key1   VARCHAR2
,as_col2   VARCHAR2
,as_commit VARCHAR2

)
IS
  CURSOR lcsr_lockrow IS
  SELECT t.ROWID, t.*
    FROM MYTABLE t
   WHERE t.KEY = as_key1
  FOR UPDATE NOWAIT;
BEGIN
  OPEN lcsr_lockrow;
  FETCH lcsr_locrow INTO lrec;
  IF lcsr_lockrow%NOTFOUND THEN
    INSERT INTO MYTABLE
    (KEY1, COL2)
    VALUES
    (as_key1, as_col2);
  ELSE
    IF lrec.COL2 = as_col2 THEN
      NULL;
    ELSE

      UPDATE MYTABLE t
         SET t.COL2 = as_col2
       WHERE t.ROWID = lrec.ROWID;

    END IF;
  END IF;
  CLOSE lcsr_lockrow;

optionally perform a commit IF as_commit = 'Y' THEN COMMIT; END IF; EXCEPTION
optionally handle ORA-00054 WHEN busy_nowait_excep THEN RAISE; WHEN OTHERS THEN RAISE; END upsert_mytable;

"Martin" <m.suttrup_at_freenet.de> wrote in message news:d2a2bf74.0105310226.5c16c80b_at_posting.google.com... > "Spencer" <spencerp_at_swbell.net> wrote in message news:<aBhR6.139$fw1.191464_at_nnrp2.sbc.net>...
> > the only way to an "update/insert" in a single call to the
> > database is to use a pl/sql block (either anonymous or
> > stored will work)

> >

> > you've got the idea though... select "for update" the row
> > by key value. if found, update it, if not found, then insert.

> >

> > HTH

> >

> > "Todd" <gdulli_at_zdnetmail.edu> wrote in message
> > news:3b159f56$0$1877_at_news.denver1.Level3.net...
> > > Is there a way to do an "insert or update" statement in one step?
i.e.
I
> > > want to insert a certain row if it's not there, or else just update
it,
without
> > > doing a select first and then branching to either an insert or update.
> > >
> > > Thanks! - Todd (gdulli_at_zdnetmail.edu - change edu to com)
> > >
> > >
> > >

>
> Hi
>
> if you're using a PL/SQL-Block than another way is
> BEGIN
>    INSERT
> EXCEPTION
>    WHEN DUP_VAL_ON_INDEX THEN
>       UPDATE
> END;
>
> or
>
> BEGIN
>    UPDATE
>    IF SQL%ROWCOUNT=0 THEN
>       INSERT
>    END IF;
> END;
>
> If you are doing more Insert's use the first Block, else the second one.
>
> HTH
>
> Martin
>

Received on Sat Jun 02 2001 - 03:39:16 CDT