exception for loop
Screate table test_tbl(a number,b number);
declare
a number;
b number;
begin
a := 0;
b := 0;
for x in 1..5 loop
Begin
save point s;
a := a+x;
b := 1/(6-a);
insert into test_tbl values(a,b);
exception
when others then
rollback to savepoint s;
end;
end loop;
end;
select * from test_tbl order by x;
Results:
a b
-- --
1 .2
3 .33
10 -.25
15 -.11
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Declare
cnt number;
ex exception;
begin
cnt := 0;
for x in 1..5 loop
begin
savepoint s;
cnt := cnt+1;
insert into test_tbl values(1/(3-x));
exception
when others then
rollback to savepoint s;
end;
end loop;
dbms_output.put_line('cnt='||cnt);
end;
--------------------------------------------------------
declare
cnt number;
ex exception;
begin
cnt := 0;
for x in 1..5 loop
begin
savepoint s;
cnt := cnt+1;
insert into test_tbl values(x);
if x = 3 then
raise ex;
end if;
exception
when others then
rollback to savepoint s;
end;
end loop;
dbms_output.put_line('cnt='||cnt);
end;
select * from test_tbl order by x;