oracle获取循环中的异常信息,zzw原创_oracle循环中的异常捕捉_捕捉异常后并继续循环...

例子如下

set serveroutput on;

declare

V_SQL VARCHAR2(255);

errorCode number; --异常编码

errorMsg varchar2(1000); --异常信息

out_return varchar2(1000);

flag varchar2(10);

CURSOR TP IS

SELECT TABLE_NAME,CONSTRAINT_NAME FROM USER_CONSTRAINTS  WHERE CONSTRAINT_TYPE = 'P';

begin

FOR E IN TP LOOP

BEGIN

V_SQL := 'ALTER TABLE ' || E.TABLE_NAME || ' ' || 'DISABLE' ||'  '|| 'CONSTRAINT ' || E.CONSTRAINT_NAME;

DBMS_OUTPUT.PUT_LINE(V_SQL);

EXECUTE IMMEDIATE (V_SQL);

EXCEPTION

when others then

errorCode := SQLCODE;

errorMsg := SUBSTR(SQLERRM, 1, 200);

flag := 'false';

out_return := 'flag=' || flag || ',errorCode=' || errorCode || ',errorMsg=' || errorMsg;

dbms_output.put_line(out_return);

null;

END;

END LOOP;

end;

注意问题

1、捕捉异常后继续下一次循环需用

EXCEPTION

when others then  null;

这样的结构。

2、在for ...LOOP   ENDLOOP 循环中捕捉异常，必须用begin   end 包起来，否则会报错。在PLSQL中报

PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following

Error: PLS-00103: Encountered the symbol "END" when expecting one of the following

正确

FOR E IN TP LOOP

begin

......................

EXCEPTION

when others then  null;

end;

endloop;

错误用法：

FOR E IN TP LOOP......................

EXCEPTION

when others then  null;

endloop;