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.