Saturday, May 19, 2012

How to Merge a Row

The tough challenge that seems to have been faced by this developer was that the ID, name and value passed into the procedure needed to be either applied as an update if the name existed, or else inserted as a new row. You might think you could just use MERGE, or maybe attempt the update, capturing the ID value with a RETURNING clause, then if that found no rows insert a new row using seq_somethings.NEXTVAL for the ID. But wait, that wouldn't be complicated enough, would it?

Here's the table:

create table something
( id               integer  not null constraint pk_something primary key
, name             varchar2(100)
, publicsomething  number   default 0  not null );
Here's what they came up with:
PROCEDURE SaveSomething(pId              IN OUT something.id%TYPE,
                        pName            IN something.name%TYPE,
                        pPublicSomething IN something.publicsomething%TYPE) IS
     counter NUMBER;
BEGIN
     SELECT COUNT(rowid)
     INTO   counter
     FROM   something c
     WHERE  LOWER(c.name) = LOWER(pName);

     IF counter > 0 THEN
          SELECT id
          INTO   pId
          FROM   something c
          WHERE  LOWER(c.name) = LOWER(pName);
     END IF;

     IF (pId IS NOT NULL AND pId > 0) THEN
          UPDATE something
          SET    id              = pId,
                 name            = pName,
                 publicsomething = pPublicsomething
          WHERE  id = pId;

     ELSE
          SELECT seq_somethings.NEXTVAL
          INTO   pId
          FROM   dual;

          INSERT INTO something
               (id, name, publicsomething)
          VALUES
               (pid, pname, ppublicsomething);
     END IF;

EXCEPTION
     WHEN OTHERS THEN
          -- log the details then throw the exception so the calling code can perform its own logging if required.
          log_error('PK_ADMIN.SaveSomething',
                    USER,
                    SQLCODE || ': ' || SQLERRM);
          RAISE;
END SaveSomething;

Thanks Boneist for this. By the way she mentioned she counted 6 WTFs, "some more subtle than others". I'm not sure whether we're counting the stupid redundant brackets around the IF condition (drives me crazy), the novel 5-character indent or the design WTF in which the "name" column is expected to be unique but has no constraint or indeed index. I'm definitely counting SQLCODE || ': ' || SQLERRM though.

6 comments:

Dalibor Carapic said...

Looks like some of my code :(

Tim... said...

I forgive them everything because at least they used a RAISE at the end of the OTHERS exception handler. :)

Cheers

Tim...

Thomas Kyte said...

@tim - hah, I just came here to say the same thing!

PTW said...

The name column does not have to be unique if it's null or if 2 sessions update rows to have the same value before either session commits. Oracle is poor at enforcing this weaker type of constraint, so we have to write our own code!

Is the 2nd parameter in log_error() a WTF? I can't see the need to pass the username. "SET id = pId...WHERE id = pId" definitely is. So, I suspect, is the IN OUT definition for the pId parameter. The update is performed on the row identified by pId if pName is not in the table, otherwise the row identified by pName is updated. The insert will only be done if pId is null or <= 0.

"Select sequence from dual"...quality! I see it so often!

Adrian said...

PTW, please tell me you are being ironic.

Writing an update/insert is a concurrency WTF in itself. You have to at least insert/update and bounce off the constraint.

Why do people insist on surrogate sequence based identifiers on tables with single column natural identifiers? It's insanity.

Joel Garry said...

I've seen enough precedence fails that are much crazier-making than (an arguably redundant set of brackets in an if statement) that I don't think that is a WTF.

words: Psilka orsUple