Re: Newbie want to insert a row ...

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Mon, 25 Sep 2000 16:43:43 -0700
Message-ID: <8qoo1j$noj$1@spiney.sierra.com>

I use something like this:

:
begin
  select primaryKey into myKey from myTable where myData = some_value.   <if you get to this point, then the record already exists for "some_value">
  :
  exception
    when no_data_found then

       begin
          insert into myTable values (mySequence.nextval, myData)
            returning primaryKey into myKey;
          commit;
      end;

end;

Couple things:

The exception statement is bound to any SELECT inside the BEGIN/END. (update won't cause an exception if the record doesn't exist)

alternative:

begin

   :
   :
   begin

       select prmaryKey into myKey from myTable where myData = some_value;
       exception
           when no_data_found then myKey := null;

  end;
  if myKey is null then
    insert into myTable....
  end if;
  :
  :
end;

"Newbie" <midifree_at_free.fr> wrote in message news:J1jy5.1469$kR.4895881_at_nnrp2.proxad.net...
> Hi,
> I work on PL/SQL and i want to insert a row if it's not yet inserted :
>
> MyTable(PrimaryKey, Field)
>
> What's the best solution to insert a row if it's not yet inserted in this
> table and is there better solution ?
> 1:
> BEGIN
> INSERT INTO MyTable(PrimaryKey, Field) Values ('KeyValue',
> 'FieldValue');
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> NULL;
> END;
>
> 2 : -- I think that this solution can don't work properly sometime :
> DECLARE
> Cpt NUMBER;
> BEGIN
> SELECT COUNT(0) INTO Cpt FROM MyTable WHERE PrimaryKey = 'KeyValue';
> IF Cpt = 0 THEN
> INSERT INTO MyTable(PrimaryKey, Field) Values ('KeyValue',
> 'FieldValue');
> END IF;
> END;
>
> 3 : ????
>
> Thanks a lot
>
>
Received on Mon Sep 25 2000 - 18:43:43 CDT