首页 文章

除ORA-00942表不存在外，阻止存储过程运行所有错误

提问于
浏览 次
0

我在Oracle 11g中有一个存储过程，它将删除某些表中特定客户端的记录 . 以下脚本是其设置的示例 . 即使引发异常，它也将继续运行每个块 . 我们希望它在发生任何其他异常时停止运行，除了“-942表不存在” . 如果该表不存在，则该过程的其余部分应继续运行，所有其他过程应使其停止 . 我怎样才能做到这一点？

create or replace PROCEDURE SCHEMA.PURGE_RECORDS

(vCLIENT_ID IN VARCHAR2, pINPUTSCOPE IN VARCHAR2,
  pSUCCESS_IND OUT VARCHAR2, pOUTCOME_DESC OUT VARCHAR2)

AS

  VTABLE_NAME VARCHAR2(200);  
  vSQL VARCHAR2(10000);

BEGIN

  IF pINPUTSCOPE = 'ALL' THEN

    BEGIN
      VTABLE_NAME := 'TABLE NAME';
      vSQL := ' 
          DELETE FROM TABLENAME T WHERE EXISTS (SELECT 1 FROM TABLE2 TSK WHERE CLIENT = '''|| vCLIENT_ID ||''' AND ASK_ID = T.ASK_ID)   ';
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION WHEN OTHERS THEN NULL; DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
    END;

    BEGIN
      VTABLE_NAME := 'TABLE NAME';
      vSQL := ' 
          DELETE FROM TABLENAME3 T WHERE EXISTS (SELECT 1 FROM TABLE3 TSK WHERE CLIENT = '''|| vCLIENT_ID ||''' AND ASK_ID = T.ASK_ID)  ';
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION WHEN OTHERS THEN NULL; DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
    END;

    BEGIN
      VTABLE_NAME := 'TABLE NAME';
      vSQL := ' 
          DELETE FROM TABLENAME4 T WHERE EXISTS (SELECT 1 FROM TABLE4 TSK WHERE CLIENT = '''|| vCLIENT_ID ||''' AND ASK_ID = T.ASK_ID)  ';
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION WHEN OTHERS THEN NULL; DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
    END;

    BEGIN
      VTABLE_NAME := 'TABLE NAME';
      vSQL := ' 
          DELETE FROM TABLENAME5 T WHERE EXISTS (SELECT 1 FROM TABLE5 TSK WHERE CLIENT = '''|| vCLIENT_ID ||''' AND ASK_ID = T.ASK_ID)  ';
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION WHEN OTHERS THEN NULL; DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
    END;
  END IF;

END;
oracle plsql

1 回答

  • 1

    您可以显式捕获ORA-00942错误，报告您想要的，然后忽略它;然后使用RAISE处理所有其他异常 - 这会将异常传播到下一个块，在这种情况下导致过程终止 - 或者根本不捕获它们 .

    ORA-00942不是the predefined exceptions之一，因此您需要定义一个异常名称并使用the PRAGMA EXCEPTION_INIT clause将异常关联到the internally defined exception number：

    ...
AS
  VTABLE_NAME VARCHAR2(200);  
  vSQL VARCHAR2(10000);

  NO_SUCH_TABLE EXCEPTION;
  PRAGMA EXCEPTION_INIT (NO_SUCH_TABLE, -942);
BEGIN

  IF pINPUTSCOPE = 'ALL' THEN

    BEGIN
      VTABLE_NAME := 'TABLE NAME';
      vSQL := ' 
          DELETE FROM TABLENAME T WHERE EXISTS (SELECT 1 FROM TABLE2 TSK WHERE CLIENT = '''|| vCLIENT_ID ||''' AND ASK_ID = T.ASK_ID)   ';
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION
        WHEN NO_SUCH_TABLE THEN
          DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
          -- this exception has been squashed
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
          RAISE;
    END;
    ...

    并在每个子块中重复异常catch . 您的 OTHERS 处理程序仍将捕获任何其他异常 . 捕捉和挤压 OTHERS 通常是一个坏主意，最好将其移除并让异常自然传播;即使你__49185_重新丢失原始问题的行号 . 所以你真的需要这样做：

    ...
      EXECUTE IMMEDIATE vSQL;   
      DBMS_OUTPUT.PUT_LINE (  VTABLE_NAME || '  Scope set: ' ||  pINPUTSCOPE || ' ' || '(' || TO_CHAR(SQL%ROWCOUNT) || ' ROWS DELETED)' || chr(10));
      EXCEPTION
        WHEN NO_SUCH_TABLE THEN
          DBMS_OUTPUT.PUT_LINE('ERROR :  ' || VTABLE_NAME || ' ' ||  SQLERRM);
          -- this exception has been squashed, all others will propagate
    END;

    ...
    回复于

相关问题