Raising exceptions in PL/SQL
735639Dec 20 2009 — edited Dec 21 2009Hi Friends
I have the following code:
declare
var1....
var2....
cursor c1
begin
insert stmt;
update stmt;
update stmt;
for r1 in c1 loop
end loop;
end;
I will be having about 6-7 million rows every month to process. To raise exceptions, I am thinking of either of the following options:
Option 1_
declare
var1....
var2....
var3 exception;
var4 exception;
var5 exception;
cursor c1
begin
insert stmt;
update stmt;
IF SQL%NOTFOUND then
var3;
end if;
update stmt;
IF SQL%NOTFOUND then
var4;
end if;
for r1 in c1 loop
end loop;
IF SQL%NOTFOUND then
var5;
end if;
Exception
when var3 then blah blah
when var4 then blah blah
when var5 then blah blah
end;
Option 2_
declare
var1....
var2....
cursor c1
begin
insert stmt;
update stmt;
update stmt;
for r1 in c1 loop
end loop;
Exception
when others then blah blah
end;
In terms of performance, which option is better? And is there any better option?
Thanks....