ストアドプロシージャ エラー処理サンプル

エラーハンドリングがされていないストアドプロシージャをたまに見かけますが最低限「when others then」によるエラー処理は行うべきとおもいます。ストアドプロシージャ内でエラーハンドリングを行うと上位プログラムエラーのキャッチができないと勘違いされている方もいるかもしれませんが、EXCEPTIONブロックでraise文を記載する事で実現できます。
以下はユーザ定義例外、ZERO_DIVIDE例外、OTHERS 例外の利用サンプルです。

ストアドプロシージャエラー処理（エラーハンドリング）サンプル

CREATE OR REPLACE PROCEDURE SOONI.testprc01
(
  delete_limit IN number
 ,insert_limit IN number
)
IS
    insert_count    number;
    delete_count    number;
    devcount        number;
    err01           exception;
    err02           exception;
BEGIN
    delete from ex01;
    delete_count := SQL%ROWCOUNT; -- 直前の実行SQLの処理対象件数
    if delete_count >= delete_limit then
        raise err01;
    end if;
    insert into EX01(SID, SNAME, BIRTHDAY)
    select SID, SNAME, BIRTHDAY from EX02;
    insert_count := SQL%ROWCOUNT;
    if insert_count >= insert_limit then
        raise err02;
    end if;
    DBMS_OUTPUT.PUT_LINE ('DELETE件数:'||insert_count||'   INSERT件数:'||insert_count);
    select insert_count/delete_count into devcount from dual;
    commit;
EXCEPTION
    when err01 then
        DBMS_OUTPUT.PUT_LINE ('ユーザ定義例外発生！ DELETE件数:'||delete_count);
        rollback;
        DBMS_OUTPUT.PUT_LINE ('rollbackしました');
        -- raise;  <-- ここにraiseを入れると上位呼び出しプログラムへエラーを投げます
    when err02 then
        DBMS_OUTPUT.PUT_LINE ('ユーザ定義例外発生！ INSERT件数:'||insert_count);
        rollback;
        DBMS_OUTPUT.PUT_LINE ('rollbackしました');
    when ZERO_DIVIDE then
        DBMS_OUTPUT.PUT_LINE ('0割り発生');
    when others then
        DBMS_OUTPUT.PUT_LINE ('その他のエラーが発生');
        dbms_output.put_line('sqlcode：'||sqlcode||'  sqlerrm：'||sqlerrm);
        rollback;
END testprc01;
/

実行例

以下実行例に解説いれています。

-- 標準出力する際には必要
SQL> set serveroutput on

-- ex01 テーブル件数を0件にして0割条件を作成
-- （when ZERO_DIVIDE thenにて処理されている）

SQL> exec testprc01(10,20);
DELETE件数:0   INSERT件数:0
sqlcode：-1476
sqlerrm：ORA-01476: 除数がゼロです

PL/SQLプロシージャが正常に完了しました。 ←エラーハンドリングできているので「正常」

-- エラーハンドリングしなかった場合以下のようになります。
-- 上位呼び出しプログラムでエラーをキャッチする事になります。
SQL> exec testprc01(10,20);
DELETE件数:6   INSERT件数:6
BEGIN testprc01(10,20); END;

*
行1でエラーが発生しました。:
ORA-01476: 除数がゼロです
ORA-06512: "SOONI.TESTPRC01", 行25
ORA-06512: 行1


SQL>
-- INSERT対象レコードに重複を持たせex01テーブルの一意制約違反を
-- 発生させる。（when others thenにて処理されている）
SQL> exec testprc01(10,20);
その他のエラーが発生
sqlcode：-1  sqlerrm：ORA-00001: 一意制約(SOONI.PK_EX01)に反しています

PL/SQLプロシージャが正常に完了しました。

SQL>
-- ユーザ定義エラーを発生させる（when err01 thenにて処理されている）
SQL> exec testprc01(1,2);
ユーザ定義例外発生！ DELETE件数:5
rollbackしました

PL/SQLプロシージャが正常に完了しました。

-- ユーザ定義エラーを発生させる（when err02 thenにて処理されている）
SQL> exec testprc01(10,2);
ユーザ定義例外発生！ INSERT件数:6
rollbackしました

PL/SQLプロシージャが正常に完了しました。

-- エラーなく終了した場合
SQL> exec testprc01(10,20);
DELETE件数:6   INSERT件数:6

PL/SQLプロシージャが正常に完了しました。

SQL>