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.