Oracle PL/SQLのcommitとrollbackと例外処理

Oracle PL/SQLのcommitとrollbackと例外処理のサンプルです。

目次

サンプル commitとrollbackのサンプル
  独自の例外を作成する(Raise文)
  独自の例外を作成する(RAISE_APPLICATION_ERROR)
  処理の途中に例外処理を入れる(データがないとき)

commitとrollbackのサンプル

commitとrollbackのサンプルです。

CREATE OR REPLACE PROCEDURE TEST1
IS
BEGIN
	UPDATE syain
	  SET name = '安田', romaji = 'yasuda'
	  WHERE id = 2;

	INSERT INTO syain (id,name,romaji)
	  VALUES (4,'竹田','takeda');

	COMMIT;

EXCEPTION
WHEN OTHERS THEN
	dbms_output.put_line('SQLCODE = ' || SQLCODE);
    dbms_output.put_line('SQLERRM = ' || SQLERRM);
	ROLLBACK;
END;

4~6行目は、update文です。
8,9行目は、insert文です。
11行目は、commitです。上記2つのSQLでエラーが発生しない場合データを確定させます。

13行目以降は、エラーが発生したときに実行されます。
17行目は、rollbackです。上記2つのSQLの更新を行わず元に戻します。
例として、4行目のupdate文が成功し、8行目からのinsert文でエラーが発生した場合は、
4行目のupdate文の更新分は、更新前の状態に戻ります。

例外発生時のSQLCODEとSQLERRM

15行目のSQLCODEは、0以外の値が表示されます。0は正常時に表示されます。
16行目のSQLERRMは、エラーメッセージが表示されます。

以下は、メッセージ例です。

上記プロシージャを2回実行するとキーの一意制約でエラーになります。
(ORA-00001: 一意制約(HR.SYS_C007013)に反しています)

独自の例外を作成する(Raise文)

Raise 例外名;

Raise文で独自の例外を作成するサンプルです。

CREATE OR REPLACE PROCEDURE TEST1
IS
	REIGIAI_TEST EXCEPTION;
BEGIN
	UPDATE syain
	  SET name = '安田', romaji = 'yasuda'
	  WHERE id = 2;

	RAISE REIGIAI_TEST;

	INSERT INTO syain (id,name,romaji)
	  VALUES (4,'竹田','takeda');

	COMMIT;

EXCEPTION
WHEN REIGIAI_TEST THEN
	dbms_output.put_line('SQLCODE = ' || SQLCODE);
    dbms_output.put_line('SQLERRM = ' || SQLERRM);
	ROLLBACK;

WHEN OTHERS THEN
	dbms_output.put_line('SQLCODE = ' || SQLCODE);
    dbms_output.put_line('SQLERRM = ' || SQLERRM);
	ROLLBACK;
END;

3行目は、独自で作成した例外(REIGIAI_TEST)を宣言しています。
9行目は、RAISE 例外名で強制的に例外を発生させています。
17行目は、9行目の例外をキャッチし、20行目のrollbackまで実行します。

以下は、実行結果です。

SQLCODEは1で、SQLERRMはUser-Defined Exceptionと表示されます。

独自の例外を作成する(RAISE_APPLICATION_ERROR)

RAISE_APPLICATION_ERROR(エラーコード,エラーメッセージ);

RAISE_APPLICATION_ERRORで独自の例外を作成するサンプルです。

CREATE OR REPLACE PROCEDURE TEST1
IS
	REIGIAI_TEST EXCEPTION;
BEGIN
	UPDATE syain
	  SET name = '安田', romaji = 'yasuda'
	  WHERE id = 2;

	RAISE_APPLICATION_ERROR(-20100,'例外エラーが発生しました');

	INSERT INTO syain (id,name,romaji)
	  VALUES (4,'竹田','takeda');

	COMMIT;

EXCEPTION

WHEN OTHERS THEN
	dbms_output.put_line('SQLCODE = ' || SQLCODE);
    dbms_output.put_line('SQLERRM = ' || SQLERRM);
	ROLLBACK;
END;

9行目は、強制的に例外を発生させています。
1つめの引数はSQLCODEです。-20,000から-20,999の範囲のエラー番号が可能です。
2つめの引数はメッセージです。最大2,048バイトまでの文字列が可能です。

以下は、実行結果です。

独自に指定したSQLCODEとSQLERRMが表示されています。

以下は、Oracleのエラーと例外の処理のリンクです
https://docs.oracle.com/cd/F10533_01/TTPLS/exceptions.htm#CIHCHBIC

処理の途中に例外処理を入れる(データがないとき)

処理の途中に例外処理を入れるサンプルです。

create or replace procedure TEST1
IS
    TYPE AR1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
    ID AR1;
	WK_STR1 VARCHAR2(200);
	WK_NAME VARCHAR2(20);
BEGIN
	ID(0) := '1';
	ID(1) := '900';
	ID(2) := '3';
	WK_STR1  := '';
    FOR i IN ID.FIRST..ID.LAST
	LOOP
        BEGIN
			SELECT name INTO WK_NAME FROM SYAIN
			WHERE id = ID(i);

	         WK_STR1 := WK_STR1 || 'データ有=' || WK_NAME || ',';

	     	 EXCEPTION
			    WHEN NO_DATA_FOUND THEN
	            WK_STR1 := WK_STR1 || 'データ無' || ',';
         END;
	END LOOP;
    DBMS_OUTPUT.PUT_LINE(WK_STR1); --データ有=鈴木,データ無,データ有=佐藤,
END;

12行目からループ文です。8~10行目の配列の数分ループします。
20,21,22行目は、15,16行目のSelect文でデータを取得できなかったときに実行されます。
ポイントは、14,23行目のBEGINとENDです。
7,26行目には、外側のBEGINとENDがあります。

関連の記事

Oracle PL/SQLのプロシージャのサンプル
Oracle PL/SQLのファンクションのサンプル
Oracle PL/SQLのパッケージのサンプル

△上に戻る