oracle数据异常处理

------------------------------------
--处理异常后不继续执行
declare
type testitb is table of number index by binary_integer;
a testitb;
b number;
begin
a(10):=11;
a(1):=12;
a(4):=4;
for i in 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;

------------------------------------
--处理异常后继续执行
declare
type testitb is table of number index by binary_integer;
a testitb;
b number;
begin
a(10):=11;
a(1):=12;
a(4):=4;
for i in a.first .. a.last loop
begin
b:=i;
dbms_output.put_line('a='||a(i));
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('b='||b);
end;
end loop;
end;

-------------------------------------
--捕获特定的异常使用  PRAGMA EXCEPTION_INIT

declare
vsql varchar2(1000);
invalid_sql_statement exception;
PRAGMA EXCEPTION_INIT(invalid_sql_statement,-900);
begin
vsql := 'selec * from az27';
execute immediate vsql;
exception
when invalid_sql_statement then
dbms_output.put_line(vsql);
end;

-----------------------------------------
--使用raise_application_error得到自定义异常
declare
vsql varchar2(1000);
invalid_sql_statement exception;
--PRAGMA EXCEPTION_INIT(invalid_sql_statement,-900);
begin
vsql := 'selec * from az27';
execute immediate vsql;
exception
when others then
dbms_output.put_line(sqlcode);
RAISE_APPLICATION_ERROR(-20123,sqlerrm||vsql,false);
end;

-----------------------
--自定义异常
BEGIN
DECLARE
Insufficient_credite EXCEPTION;
BEGIN
RASISE Insufficient_credite;
EXCEPTION
WHEN Insufficient_credite THEN
--可以在此处理异常
　　　extend_credite(cust_id);
END　-嵌套块结束
EXCEPTION
WHEN Insufficient_credite THEN
--超出范围，不能在这里处理异常
END;