Using "IF NOT EXIST" in a procedure

Question

I'm trying to create a procedure that will insert me an new entry IF the ID is not already used. I just can't figure out what I'm not doing right there:

CREATE OR REPLACE PROCEDURE add_user
IS
BEGIN
    IF NOT EXISTS SELECT * FROM tab WHERE tab.id = '111'
    THEN
INSERT INTO tab(id, name, job, city, phone)
        VALUES(111, 'Frank', 'Programmer', 'Chicago', '111111111');
END IF;
END;

Answer 1

Do not try to enforce referential integrity in code as it is very, very likely that it will be very, very wrong. Not obviously wrong - no, this is the kind of "wrong" that only shows up when you have 200 users banging on the database and then something goes very very far south in a very big hurry and all of a sudden nobody can use the system or do their jobs and your phone is ringing and your boss is breathing steam and cinders down the back of your neck and you're sweating and there's perspiration running down your contacts and you can't see and you can't think and and and... You know, that kind of wrong. :-)

Instead, use the referential integrity features of the database that are designed to prevent this kind of wrong. A good place to start is with the rules about Normalization. You remember, you learned about them back in school, and everybody said how stooopid they were, and WHY do we have to learn this junk because everybody knows that nobody does this stuff because it, like, doesn't matter, does it, hah, hah, hah (aren't we smart)? Yeah, that stuff - the stuff that after a few days on a few projects like the paragraph above you suddenly Get Religion About, because it's what's going to save you (the smarter you, the older-but-wiser you) and your dumb ass from having days like the paragraph above.

So, first thing - ensure your table has a primary key. Given the field names above I'm going to suggest it should be the ID column. To create this constraint you issue the following command at the SQL*Plus command line:

ALTER TABLE TAB ADD CONSTRAINT TAB_PK PRIMARY KEY(ID);

Now, rewrite your procedure as

CREATE OR REPLACE PROCEDURE add_user IS
BEGIN
  INSERT INTO TAB
    (id, name, job, city, phone)
  VALUES
    (111, 'Frank', 'Programmer', 'Chicago', '111111111');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    EXIT;  -- fine - the user already exists
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error in ADD_USER : ' || SQLCODE || ' : ' || SQLERRM);
    RAISE;
END;

So, rather than try to pre-check to see if the coast is clear we just barge right in and insert the damn user! For we are programmers! We are strong!! We care nothing for the potential risks!!! We drink caffeinated soft drinks WITH HIGH-FRUCTOSE CORN SYRUP!!!! Errors cannot frighten us!!!

ARE WE NOT MEN?!?!?

Well, actually, if we're responsible (rather than "reprehensible" :-) programmers, we actually care a whole helluva lot about potential errors, if only because we know they're gonna land on OUR desks, most likely when we'd rather be surfing the web, or learning a new programming language, or chatting to/drooling over the girl in the next aisle who works in marketing and who is seriously out of our league - my point being, errors concern us. Avoiding them, handling them properly - these things are what set professional developers apart from dweeb wannabees and management strikers. Which gets us to the line of code right after the INSERT statement:

EXCEPTION

That's right - we know things can go wrong with that INSERT statement - but because we ARE men, and we ARE responsible programmers, we're gonna Do The Right Thing, which in this case means "Handle The Exception We Can Forsee". And what do we do?

WHEN DUP_VAL_ON_INDEX THEN

GREAT! We KNOW we can get a DUP_VAL_ON_INDEX exception, because that's what's gonna get thrown when we try to insert a user that already exists. And then we'll Do The Right Thing:

  EXIT;  -- fine - the user already exists.

which in this case is to ignore the error completely. No, really! We're trying to insert a new user. The user we're trying to insert is already there. What's not to love? Now, it may well be that in that mystical, mythical place called The Real World it's just possible that it might be considered gauche to simply ignore this fact, and there might be a requirement to do something like log the fact that someone tried to add an already-extant user - but here in PretendLand we're just going to say, fine - the user already exists so we're happy.

BUT WAIT - there's more! Not ONLY are we going to handle (and, yeah, ok, ignore) the DUP_VAL_ON_INDEX exception, BUT we will also handle EVERY OTHER POSSIBLE ERROR CONDITION KNOWN TO MAN-OR-DATABASE KIND!!!

WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||
                       '  Error in ADD_USER : ' || SQLCODE || ' : ' || SQLERRM);
  RAISE;

Which means we'll spit out a somewhat useful error message telling use WHEN, WHERE, and WHAT went wrong, and then re-raise the exception (whatever it might be) so that whomsoever called us gets it dumped gracelessly into THEIR lap so THEY can handle it. (Serves 'em right for calling us, the lazy so-and-so's...).

So, now you know.

Go thou and do good works.

And...share and enjoy.

Answer 2

The direct answer to your question is to put the subquery in parentheses:

IF NOT EXISTS (SELECT * FROM tab WHERE tab.id = 111) THEN
    INSERT INTO tab(id, name, job, city, phone)
        VALUES(111, 'Frank', 'Programmer', 'Chicago', '111111111');
END IF;

However, that is not a good solution to your problem (I removed the single quotes because the constant is quoted in one place but not in another). One issue is race conditions. The not exists part might run, then another process might insert the row, and then the insert will fail.

The right solution is:

create unique index tab_id on tab(id);

Then, either use merge for the insert. Or wrap the insert in exception handling code. Or, use the logging facility:

    INSERT INTO tab(id, name, job, city, phone)
        VALUES(111, 'Frank', 'Programmer', 'Chicago', '111111111');
    LOG ERRORS INTO errlog ('Oops, something went wrong') REJECT LIMIT UNLIMITED;

Then you will not be allowed to insert duplicate rows into the table. They won't go in, and you won't get an error (unless you want one).

Answer 3

You forgot to add ( and ) in IF statement:

  CREATE OR REPLACE PROCEDURE add_user
    IS
    BEGIN
        IF NOT EXISTS (SELECT * FROM tab WHERE tab.id = '111') THEN
    INSERT INTO tab(id, name, job, city, phone)
            VALUES(111, 'Frank', 'Programmer', 'Chicago', '111111111');
    END IF;
    END;