自律型トランザクションのサンプル（AUTONOMOUS_TRANSACTION)

PL/SQLでログを取りたい場合とかに実プログラムのトランザクションとは別で
ログ用のトランザクションを作成したいときとか結構あると思います。

そんなときはAUTONOMOUS_TRANSACTIONを使うと自律型トランザクションを実装出来ます。

サンプルを書きました。

やってることはログインユーザが持ってるオブジェクト名とタイプをOBJECT_LISTテーブルに1件ずつINSERT
して1件ずつログを吐く。
自律型を確認するためOBJCT_LISTへのINSERTはROLLBACKしてみるという簡単なプロシージャ。

ログテーブル

CREATE TABLE PLSQL_LOG
(
    LOG_LEVEL                      VARCHAR2(10) NOT NULL,
    LOG_MESSAGE                    VARCHAR2(100) NOT NULL,
    REGISTER_DATE                  TIMESTAMP(6) DEFAULT systimestamp NOT NULL
)

ロガープロシージャ

CREATE OR REPLACE PROCEDURE LOGGER
(
	 V_LOG_LEVEL IN VARCHAR2
	,V_LOG_MESSAGE IN VARCHAR2
)
IS
	--自律型トランザクションであることを明記
	PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	--ログを登録
	INSERT INTO
		PLSQL_LOG
			(LOG_LEVEL,LOG_MESSAGE,REGISTER_DATE)
		VALUES
			(V_LOG_LEVEL,V_LOG_MESSAGE,SYSTIMESTAMP);
	COMMIT;
END LOGGER;
/

オブジェクト一覧格納テーブル

CREATE TABLE OBJECT_LIST
(
    OBJECT_NAME                    VARCHAR2(100) NOT NULL,
    OBJECT_TYPE                    VARCHAR2(100) NOT NULL
)

OBJECT_LISTインサートプロシージャ

CREATE OR REPLACE PROCEDURE CREATE_OBJECT_LIST
IS
	CURSOR OBJ_LIST IS SELECT * FROM USER_OBJECTS;
BEGIN
	--自分の持っているオブジェクトをSELECTしてループ
	FOR REC IN OBJ_LIST LOOP
		--OBJECT_LISTにオブジェクト名とタイプを登録
		INSERT INTO
			OBJECT_LIST
				(OBJECT_NAME,OBJECT_TYPE)
			VALUES
				(REC.OBJECT_NAME,REC.OBJECT_TYPE);
		--ログを出力
		LOGGER('DEBUG','OBJECT_NAME='||REC.OBJECT_NAME||' OBJECT_TYPE='||REC.OBJECT_TYPE);
	END LOOP;
	--自律の確認のためROLLBACK
	ROLLBACK;
END CREATE_OBJECT_LIST;
/

上記のCREATE_OBJECT_LISTプロシージャを実行するとPLSQL_LOGテーブルにはCOMMITされてOBJECT_LISTテーブルはROLLBACKされます。

応用次第で色々使い道があるので色々試して見ると楽しいです。