How to continue after an exception is thrown in Pl/sql
This question once let me depressed, very useful skill!
sql> Declare
2 type TESTITB is table of number index by Binary_integer;
a testitb;
b number;
3 4 5 begin
A: =11
6 7 A (1): =12
A (4): =4;
8 9
for I a.first. A.last loop
B:=i;
Dbms_output.put_line (' a= ' | | A (i));
End Loop;
EXCEPTION
When No_data_found THEN
dbms_output.put_line (' b= ' | | b);
End;
/
a=12
b=2
In this case, when a (2) throws a No_data_found exception, the entire block exits. Modify the exception position and place it in the loop.
Declare
Type TESTITB is table of number index by Binary_integer;
a TESTITB;
B number;
Begin
A: =11
A (1): =12
A (4): =4;
for I in A.first. A.last loop
begin
B:=i;
DBMS_OUTP Ut.put_line (' a= ' | | A (i));
EXCEPTION
When No_data_found THEN
dbms_output.put_line (' b= ' | | b);
End;
End Loop;
End;
/
a=12
b=2
b=3
a=4
b=5
b=6
b=7
b=8
b=9
a=11
Pl/sql procedure successfully completed.
This way it can be done without affecting the entire pl/sql block.