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

原则如下：
1、存储过程中的异常要在本存储过程中进行处理。
2、存储过程中调用存储过程，被调用的如果发生了异常，一般父存储过程需要终止。
3、子存储过程中的异常处理不得污染父存储过程中的异常处理。

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

CREATE OR REPLACE PACKAGE TEST_ROBACK IS

  PROCEDURE 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 IS

  PROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2) IS
    V_FLAG   VARCHAR2(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_FLAG   VARCHAR2(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 IS

  PROCEDURE 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
(
  id             VARCHAR2(100) not null,
  proce_name     VARCHAR2(30),
  log_level      VARCHAR2(2),
  log_info       VARCHAR2(1000),
  makedate       DATE,
  maketime       VARCHAR2(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 is

  INTO_LEVEL CONSTANT A_LOG.LOG_LEVEL%TYPE := '1';
  ERR_LEVEL  CONSTANT A_LOG.LOG_LEVEL%TYPE := '2';

  PROCEDURE LOG(I_PROCEDURENAME A_LOG.PROCE_NAME%TYPE,
                I_LOGLEVEL      A_LOG.LOG_LEVEL%TYPE,
                I_LOGINFO       A_LOG.LOG_INFO%TYPE);

end Z_LOG;

create or replace package body Z_LOG is

  PROCEDURE LOG(I_PROCEDURENAME A_LOG.PROCE_NAME%TYPE,
                I_LOGLEVEL      A_LOG.LOG_LEVEL%TYPE,
                I_LOGINFO       A_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 IS

  PROCEDURE PROMAIN(O_FLAG OUT VARCHAR2, O_RESULT OUT VARCHAR2);

  PROCEDURE PROCH1;

END TEST_ROBACK;

CREATE OR REPLACE PACKAGE BODY TEST_ROBACK IS

  PROCEDURE 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_SQL      VARCHAR2(1000) := '';
    V_PRO_NAME A_LOG.PROCE_NAME%TYPE := 'PROCH1';
    V_PARAM    VARCHAR2(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，而不会影响到调用本过程的其他过程的事务。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,319评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,801评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,567评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,156评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,019评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,090评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,500评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,192评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,474评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,566评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,338评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,212评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,572评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,890评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,169评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,478评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,661评论 2赞 335