CODEWITHSUNDEEP
oracle-databaseplsql
TheSpaceboy0
TheSpaceboy0

Reputation: 95

procedure - insert only if not exist

I have procedure that insert city name (and also create next id). How to not let insert city name to table if the name is already exists? Thanks! There is my code:

create or replace 
PROCEDURE PlaceName(
          town IN City.Name%TYPE)
AS
  id_C City.Id_City%TYPE;
BEGIN
  SELECT NVL(Max(c.Id_City)+1,1) INTO id_C
  FROM City c;
  INSERT INTO City
  VALUES(id_C, town);
End;

Upvotes: 1

Views: 2116

Answers (4)

knagaev
knagaev

Reputation: 2957

insert into City
  select seq.nextval, town
from dual where not exists (select 1 from City where name = town);

I STRICTLY recommend to use sequences for artificial keys. "select nvl(max())" is very-very bad. If you need an explanation, ask me :)

Upvotes: 0

eaolson
eaolson

Reputation: 15094

I agree with Ben that there should be a UNIQUE constraint on the table (assume that's a valid constraint), but this can be done much simpler with a MERGE statement:

MERGE INTO city c
USING ( SELECT 1 FROM dual )
   ON c.name = town
 WHEN NOT MATCHED THEN INSERT ( id, name )
      VALUES ( my_sequence.NEXTVAL, town );

The USING clause is not really needed here, but it's mandatory for a merge statement.

Upvotes: 4

Ben
Ben

Reputation: 52863

No, don't insert only if it doesn't exist. This requires two operations. You have to check whether it exists and then you have to insert the record.

The correct way to this is to create a unique constraint on your table. You can do this inline as stated in the documentation or if your table already exists you can ALTER it to add the constraint:

ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (city);

You then catch the exception raised when inserting a city that already exists and then do whatever you wish with the information gained.

You're also incrementing your ID incorrectly. You should be using a SEQUENCE, which saves you another SELECT.

CREATE SEQUENCE city_seq
     START WITH <current max ID>
   INCREMENT BY 1;

Your procedure then becomes:

create or replace procedure PlaceName (
          town in city.name%type ) is

begin
  insert into city
  values(city_seq.nextval, town);

-- Catch the raised exception if the city already exists.
exception when dup_val_on_index then
   <do something>;
end;

Upvotes: 2

EvilTeach
EvilTeach

Reputation: 28837

Look up the Oracle MERGE command.

Upvotes: 0

Related Questions