捕集oracle,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;