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;