九、存储过程中异常的处理九、存储过程中异常的处理

【九、存储过程中异常的处理】原则如下：
1、存储过程中的异常要在本存储过程中进行处理。
2、存储过程中调用存储过程，被调用的如果发生了异常，一般父存储过程需要终止。
3、子存储过程中的异常处理不得污染父存储过程中的异常处理。
在这样的原则下，每个存储过程都要进行异常的声明与处理，处理完毕之后要抛出异常对父存储过程进行中断，否则因为子存储过程中的异常已经得到了处理，父存储过程会继续执行下去。又因为不能使父存储过程的异常处理被污染，所以最好的处理异常的方式就是声明变量，该变量专门为异常而存在，发生异常则变量值改变，处理完异常后，在父存储过程中判断该变量的值，如果子存储过程中发生了异常，则主动抛出异常，中断父存储过程的运行，这样就能分清楚异常到底是发生在具体的哪个存储过程中了。所以以后的异常处理就采用强哥的那种方式了。
如上。
测试存储过程：

CREATE OR REPLACE PACKAGE TEST_ROBACK ISPROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2); PROCEDURE PROCH1(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2); END TEST_ROBACK;

CREATE OR REPLACE PACKAGE BODY TEST_ROBACK ISPROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2) IS V_FLAGVARCHAR2(1) := '0'; V_RESULT VARCHAR2(1000) := ''; E_EXCEPTION EXCEPTION; BEGIN O_FLAG:= '0'; O_RESULT := '处理成功'; PROCH1(O_FLAG => V_FLAG, O_RESULT => V_RESULT); IF (V_FLAG != '0') THEN RAISE E_EXCEPTION; END IF; COMMIT; EXCEPTION WHEN E_EXCEPTION THEN O_FLAG:= '1'; O_RESULT := '处理失败'; DBMS_OUTPUT.PUT_LINE('子过程发生异常'); ROLLBACK; WHEN OTHERS THEN O_FLAG:= '1'; O_RESULT := '处理失败'; DBMS_OUTPUT.PUT_LINE('主过程发生异常'); ROLLBACK; END PROMAIN; PROCEDURE PROCH1(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2) IS V_SQL VARCHAR2(1000) := ''; --用来接收本存储过程中调用的其他的存储过程的返回结果 V_FLAGVARCHAR2(1) := '0'; V_RESULT VARCHAR2(1000) := ''; E_EXCEPTION EXCEPTION; --用来返回本存储过程的执行结果 BEGIN O_FLAG:= '0'; O_RESULT := '执行成功'; V_SQL:= 'INSERT INTO AZWZ(CLAIMINFO) VALUES (''1'')'; EXECUTE IMMEDIATE V_SQL; RAISE E_EXCEPTION; EXCEPTION WHEN E_EXCEPTION THEN O_FLAG:= '1'; O_RESULT := '处理失败'; WHEN OTHERS THEN O_FLAG:= '1'; O_RESULT := '处理失败'; END PROCH1; BEGIN DBMS_OUTPUT.PUT_LINE('TEST'); END TEST_ROBACK;

关于raise_application_error的用法：

CREATE OR REPLACE PACKAGE BODY TEST_ROBACK ISPROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2) IS E_EXCEPTION EXCEPTION; PRAGMA EXCEPTION_INIT(E_EXCEPTION, -20999); BEGIN O_FLAG:= '0'; O_RESULT := '处理成功'; PROCH1; COMMIT; EXCEPTION WHEN E_EXCEPTION THEN O_FLAG:= '1'; O_RESULT := '处理失败'; DBMS_OUTPUT.PUT_LINE(SQLERRM); ROLLBACK; WHEN OTHERS THEN O_FLAG:= '1'; O_RESULT := '处理失败'; DBMS_OUTPUT.PUT_LINE('主过程发生异常'); ROLLBACK; END PROMAIN; PROCEDURE PROCH1 IS V_SQL VARCHAR2(1000) := ''; BEGIN V_SQL := 'INSERT INTO AZWZ(CLAIMINFO,A) VALUES (''1'')'; EXECUTE IMMEDIATE V_SQL; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20999, SQLERRM); END PROCH1; BEGIN DBMS_OUTPUT.PUT_LINE('TEST'); END TEST_ROBACK;

综上，异常处理大概有两种方式：

在子过程中进行异常处理，同时用改变返回值的方式在主过程中进行子过程是否发生异常的判断
-在主过程中定义异常号，在子过程中进行异常处理，同时采用raise_application_error的方式抛出自定义异常号及异常信息，然后在主过程中直接捕获。这种方式的sqlcode只能在-20000到-20999

使用专门的表保存错误
将异常保存到专门的表格中：

-- Create table create table A_LOG ( idVARCHAR2(100) not null, proce_nameVARCHAR2(30), log_levelVARCHAR2(2), log_infoVARCHAR2(1000), makedateDATE, maketimeVARCHAR2(10), standbystring1 VARCHAR2(100), standbystring2 VARCHAR2(100) )

-- Create sequence create sequence MY_SEQ minvalue 1 maxvalue 9999999999999999999999999999 start with 81 increment by 1 cache 20;

日志类存储过程：

create or replace package Z_LOG isINTO_LEVEL CONSTANT A_LOG.LOG_LEVEL%TYPE := '1'; ERR_LEVELCONSTANT A_LOG.LOG_LEVEL%TYPE := '2'; PROCEDURE LOG(I_PROCEDURENAME A_LOG.PROCE_NAME%TYPE, I_LOGLEVELA_LOG.LOG_LEVEL%TYPE, I_LOGINFOA_LOG.LOG_INFO%TYPE); end Z_LOG;

create or replace package body Z_LOG isPROCEDURE LOG(I_PROCEDURENAME A_LOG.PROCE_NAME%TYPE, I_LOGLEVELA_LOG.LOG_LEVEL%TYPE, I_LOGINFOA_LOG.LOG_INFO%TYPE) IS PRAGMA AUTONOMOUS_TRANSACTION; V_SQL VARCHAR(1000); LOG_EXCEPTION EXCEPTION; BEGIN V_SQL := 'insert into A_LOG (ID, PROCE_NAME, LOG_LEVEL, LOG_INFO, MAKEDATE, MAKETIME) VALUES (MY_SEQ.NEXTVAL,' || ':1,' || ':2,' || ':3,' || 'TRUNC(SYSDATE),TO_CHAR(SYSDATE,''HH24:MM:SS''))'; EXECUTE IMMEDIATE V_SQL USING I_PROCEDURENAME, I_LOGLEVEL, I_LOGINFO; COMMIT; EXCEPTION WHEN OTHERS THEN RAISE LOG_EXCEPTION; END LOG; end Z_LOG;

测试类：

CREATE OR REPLACE PACKAGE TEST_ROBACK ISPROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2); PROCEDURE PROCH1; END TEST_ROBACK;

CREATE OR REPLACE PACKAGE BODY TEST_ROBACK ISPROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2) IS E_EXCEPTION EXCEPTION; PRAGMA EXCEPTION_INIT(E_EXCEPTION, -20009); V_PRO_NAME A_LOG.PROCE_NAME%TYPE := 'PROMAIN'; BEGIN Z_LOG.LOG(V_PRO_NAME, '1', '开始调用主过程'); O_FLAG:= '0'; O_RESULT := '处理成功'; PROCH1; EXCEPTION WHEN E_EXCEPTION THEN O_FLAG:= '1'; O_RESULT := '处理失败'; Z_LOG.LOG(V_PRO_NAME, '2', SQLERRM); ROLLBACK; WHEN OTHERS THEN O_FLAG:= '1'; O_RESULT := '处理失败'; Z_LOG.LOG(V_PRO_NAME, '2', SQLERRM); ROLLBACK; END PROMAIN; PROCEDURE PROCH1 IS V_SQLVARCHAR2(1000) := ''; V_PRO_NAME A_LOG.PROCE_NAME%TYPE := 'PROCH1'; V_PARAMVARCHAR2(2) := '1'; BEGIN Z_LOG.LOG(V_PRO_NAME, '1', '开始调用子过程'); insert into AZWZ (CLAIMINFO) VALUES ('2'); V_SQL := 'INSERT INTO AZWZ(CLAIMINFO,A) VALUES (''' || V_PARAM || ''')'; EXECUTE IMMEDIATE V_SQL; EXCEPTION WHEN OTHERS THEN Z_LOG.LOG(V_PRO_NAME, '2', V_SQL); ROLLBACK; RAISE_APPLICATION_ERROR(-20009, V_SQL); END PROCH1; BEGIN DBMS_OUTPUT.PUT_LINE('TEST'); END TEST_ROBACK;

事务控制
在上面的示例中要注意事务控制，在日志类中采用自治事务。这样的话，每次commit就只提交本过程中的dml，而不会影响到调用本过程的其他过程的事务。