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

サンプル	commitとrollbackのサンプル
	強制的に例外を発生させる(Raise文)
	独自の例外を発生させる(RAISE_APPLICATION_ERROR)
	ループ処理に例外処理を入れる(NO_DATA_FOUND)

commitとrollbackのサンプル

commitとrollbackのサンプルです。

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

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

	COMMIT;

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

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

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

例外発生時のSQLCODEとSQLERRM

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

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

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

強制的に例外を発生させる(Raise文)

Raise 例外名;

RAISE 例外名で強制的に例外を発生させることができます。

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

	RAISE EXCEPTION_TEST;

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

	COMMIT;

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

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

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

以下は、実行結果です。

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

独自の例外を発生させる(RAISE_APPLICATION_ERROR)

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

RAISE_APPLICATION_ERRORで独自の例外を作成し発生させれます。
ORA-20,000から-20,999までの指定ができます。

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

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

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

	COMMIT;

EXCEPTION

WHEN OTHERS THEN
	ROLLBACK;
	dbms_output.put_line('SQLCODE = ' || SQLCODE);
    dbms_output.put_line('SQLERRM = ' || SQLERRM);
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

ループ処理に例外処理を入れる(NO_DATA_FOUND)

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

create or replace procedure TEST1
IS
    TYPE AR1 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
    ID AR1;
	WK_NAME VARCHAR2(20);
BEGIN
	ID(0) := '1';
	ID(1) := '900';
	ID(2) := '3';

    FOR i IN ID.FIRST..ID.LAST
	LOOP
        BEGIN
			SELECT name INTO WK_NAME FROM employee
			WHERE id = ID(i);
			
			DBMS_OUTPUT.PUT_LINE(WK_NAME);

	     	 EXCEPTION
			    WHEN NO_DATA_FOUND THEN
				DBMS_OUTPUT.PUT_LINE('No data');
         END;
	END LOOP;
END;

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

以下は、実行結果です。