PostgreSQL PL/pgSQLのcommitとrollbackと例外処理

PostgreSQL PL/pgSQLのcommitとrollbackと例外処理のサンプルです。
(確認環境:PostgreSQL 11.2,Windows 10)

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

commitとrollbackのサンプル

commitとrollbackのサンプルです。

CREATE OR REPLACE PROCEDURE test1() 
AS $$
BEGIN
	BEGIN
		UPDATE employee
		  SET name = '安田', romaji = 'yasuda'
		  WHERE id = 2;
	
		INSERT INTO employee (id,name,romaji)
		  VALUES (4,'竹田','takeda');
		  
	EXCEPTION
		WHEN OTHERS THEN
		RAISE INFO 'SQLSTATE = %', SQLSTATE;
		RAISE INFO 'SQLERRM = %', SQLERRM;
		ROLLBACK;
		RETURN;
	END;
	COMMIT;
	RAISE INFO '%','end';
END;
$$
LANGUAGE plpgsql;

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

12～18行目は、エラーが発生したときに実行されます。
16行目は、rollbackです。上記2つのSQLの更新を行わず元に戻します。
例として、update文が成功して、insert文でエラーが発生した場合は、
update文の更新分は、更新前の状態に戻ります。
17行目のRETURNで処理を終了します。

例外発生時のSQLSTATEとSQLERRM

例外発生時、14行目のSQLSTATEはエラーコードを、15行目のSQLERRMは、エラーメッセージを表示します。

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

以下は、Postgresqlのエラーコードのリンクです。
https://www.postgresql.jp/document/8.3/html/errcodes-appendix.html

独自の例外を作成する

RAISE EXCEPTION USING ERRCODE = 'エラーコード', MESSAGE = 'エラーメッセージ' ;

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

CREATE OR REPLACE PROCEDURE test1() 
AS $$
BEGIN
	BEGIN
		UPDATE employee
		  SET name = '安田', romaji = 'yasuda'
		  WHERE id = 2;
	
		RAISE EXCEPTION USING ERRCODE = '20100',MESSAGE = '例外エラーが発生しました';
	
		INSERT INTO employee (id,name,romaji)
		  VALUES (4,'竹田','takeda');
		  
	EXCEPTION
		WHEN SQLSTATE '20100' THEN
		RAISE INFO 'SQLSTATE = %', SQLSTATE;
		RAISE INFO 'SQLERRM = %', SQLERRM;
		ROLLBACK;
		RETURN;
	END;
	COMMIT;
	RAISE INFO '%','end';
END;
$$
LANGUAGE plpgsql;

9行目は、強制的に例外を発生させています。
1つめの引数はエラーコードです。
2つめの引数はメッセージです。
19行目のRETURNで処理を終了します。

以下は、実行結果です。

以下は、Postgresqlのエラーコードのリンクです。
https://www.postgresql.jp/document/8.4/html/plpgsql-errors-and-messages.html

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

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

CREATE OR REPLACE PROCEDURE test1() 
AS $$
DECLARE
    in_ID int[];
	wk_id int;
	str1 VARCHAR(20);
BEGIN
	in_ID[0] := 1;
	in_ID[1] := 900;
	in_ID[2] := 3;

	FOREACH wk_id IN ARRAY in_ID
	LOOP	
		BEGIN
			select name into STRICT str1 from employee
			where ID = wk_id;
			RAISE INFO '%',str1;

		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				RAISE INFO '%','データ無';
		END;
	END LOOP;
END;
$$
LANGUAGE plpgsql;

15行目にSTRICTオプションをつけるとデータがないときは、20行目のNO_DATA_FOUNDでキャッチできます。
データがないときで、STRICTオプションが無い場合はNullが返り20行目でキャッチされません。

以下は、実行結果です。