Reputation: 399
I am trying to construct an INSERT INTO query that needs some exception handling.
Here's the code so far:
declare
nvid number := 4561;
fromMot number;
toMot number;
begin
for idx in 1..10 loop
for fwd in 0..1 loop
if fwd=0 then fromMot:=idx; toMot:=8;
else fromMot:=8; toMot:=idx;
End if;
insert into MOT_BUFFER_TIME(MBT_ID,MBT_NV_ID,MBT_START_MOT_ID,MBT_END_MOT_ID,MBT_BUFFER_TIME)
values ((select max(mbt.MBT_ID)+1 from MOT_BUFFER_TIME mbt),nvid,fromMot,toMot,600);
exception
when dup_val_on_index then
UPDATE MOT_BUFFER_TIME set MBT_BUFFER_TIME=600 where MBT_NV_ID=nvid and MBT_START_MOT_ID=fromMot and MBT_END_MOT_ID=toMot;
end loop;
end loop;
end;
/
The table inserts some buffer times (all 600) for a number of MBT_START_MOT_ID and MBT_END_MOT_ID combinations. These are n-8 and 8-n, which I have chosen to do with the nested FOR loops.
The issue is that the table already has some entries (usually 8-5 and 5-8) which have a different value that will need changing to 600, and while MBT_ID is the primary key, there is a constraint that the combination of MBT_NV_ID, MBT_START_MOT_ID and MBT_END_MOT_ID needs to be unique.
So I thought that I would write an exception, such that if there is a duplicate error, I would update instead.
The problem is that that PL/SQL developer is complaining about the placement of exception
, telling me that it is expecting something else, and also complaining about unexpected loop
on the penultimate line.
Clearly I've done something wrong, but I can't see what. Any hints? Thanks.
EDIT: Added "+1" to the first field in the values section, because without it it was pulling off a primary key value that was already used.
Upvotes: 0
Views: 164
Reputation: 870
Apart from actually avoiding the problem (e.g. as OldProgrammer suggested), the EXCEPTION
statement should be a part of a code block, which you do not have, so you have to add BEGIN
and END
. Something like this:
declare
nvid number := 4561;
fromMot number;
toMot number;
begin
for idx in 1..10 loop
for fwd in 0..1 loop
if fwd=0 then fromMot:=idx; toMot:=8;
else fromMot:=8; toMot:=idx;
End if;
BEGIN
insert into MOT_BUFFER_TIME(MBT_ID,MBT_NV_ID,MBT_START_MOT_ID,MBT_END_MOT_ID,MBT_BUFFER_TIME)
values ((select max(mbt.MBT_ID)+1 from MOT_BUFFER_TIME mbt),nvid,fromMot,toMot,600);
exception
when dup_val_on_index then
UPDATE MOT_BUFFER_TIME set MBT_BUFFER_TIME=600 where MBT_NV_ID=nvid and MBT_START_MOT_ID=fromMot and MBT_END_MOT_ID=toMot;
END;
end loop;
end loop;
end;
/
Edited to match the question edit (as pointed out in the comment).
Upvotes: 3