CODEWITHSUNDEEP
oracle-databasestored-procedures
aghaux
aghaux

Reputation: 749

Update or insert based on if employee exist in table

Do want to create Stored procc which updates or inserts into table based on the condition if current line does not exist in table?

This is what I have come up with so far:

PROCEDURE SP_UPDATE_EMPLOYEE
(
   SSN                              VARCHAR2,
   NAME                             VARCHAR2
)
AS
BEGIN
    IF EXISTS(SELECT * FROM tblEMPLOYEE a where a.ssn = SSN)
     --what ? just carry on to else
    ELSE
    INSERT INTO pb_mifid (ssn, NAME)
     VALUES (SSN, NAME);
END;

Is this the way to achieve this?

Upvotes: 0

Views: 8342

Answers (2)

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

This is quite a common pattern. Depending on what version of Oracle you are running, you could use the merge statement (I am not sure what version it appeared in). 

create table test_merge (id integer, c2 varchar2(255));

create unique index test_merge_idx1 on test_merge(id);

merge into test_merge t
  using (select 1 id, 'foobar' c2 from dual) s
  on (t.id = s.id)
  when matched then update set c2 = s.c2
  when not matched then insert (id, c2) 
  values (s.id, s.c2);

Merge is intended to merge data from a source table, but you can fake it for individual rows by selecting the data from dual.

If you cannot use merge, then optimize for the most common case. Will the proc usually not find a record and need to insert it, or will it usually need to update an existing record?

If inserting will be most common, code such as the following is probably best:

begin
  insert into t (columns) 
  values ()
exception
  when dup_val_on_index then
    update t set cols = values
end;

If update is the most common, then turn the procedure around:

begin
  update t set cols = values;

  if sql%rowcount = 0 then  
    -- nothing was updated, so the record doesn't exist, insert it. 
    insert into t (columns)
            values ();
  end if;
end;

You should not issue a select to check for the row and make the decision based on the result - that means you will always need to run two SQL statements, when you can get away with one most of the time (or always if you use merge). The less SQL statements you use, the better your code will perform.

Upvotes: 2

Florin Ghita
Florin Ghita

Reputation: 17643

BEGIN
  INSERT INTO pb_mifid (ssn, NAME)
  select  SSN, NAME from dual
  where not exists(SELECT * FROM tblEMPLOYEE a where a.ssn = SSN);
END;

UPDATE: Attention, you should name your parameter p_ssn(distinguish to the column SSN ), and the query become:

  INSERT INTO pb_mifid (ssn, NAME)
  select  P_SSN, NAME from dual
  where not exists(SELECT * FROM tblEMPLOYEE a where a.ssn = P_SSN);

because this allways exists:

SELECT * FROM tblEMPLOYEE a where a.ssn = SSN

Upvotes: 2

Related Questions