OracleのEXECUTE IMMEDIATEを実行しても自動COMMITされるわけではない

最近会社で聞かれて答えたことをまとめてみました。

以下のようなことを聞かれました。

| OracleでEXECUTE IMMEDIATEを使うと自動COMMITされるのか

このことについて、答えたこと、答えていないことをまとめてみました。

実験

この程度のことであれば、実験するのが早いです。会社で答えたときも実験コードを作って説明しました。

ここでの実験は、DB環境にはOracleCloud(ATP)を、クライアントにはDBeaverを使っています。

実験用コード(DML)

実験用のコードを改めて起こしてみました。会社では、DBMS_OUTPUTの中をこんなに丁寧には書いていません。

CREATE TABLE TBL_DUMMY AS SELECT 1 AS X FROM DUAL WHERE ROWNUM = 0;

DECLARE
	V_CNT	NUMBER;
BEGIN
	-- 初期化
	DELETE FROM TBL_DUMMY;
	COMMIT;
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'INSERT前(DELETE後) : ' || V_CNT );
	-- 通常INSERT
	INSERT INTO TBL_DUMMY VALUES ( 1 );
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'INSERT後 : ' || V_CNT );
	-- EXECUTE IMMEDIATE INSERT
	EXECUTE IMMEDIATE 'INSERT INTO TBL_DUMMY VALUES ( 2 )';
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'INSERT(EXECUTE IMMEDIATE)後 : ' || V_CNT );
	-- ROLLBACK
	ROLLBACK;
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'ROLLBACK後 : ' || V_CNT );
END;

DROP TABLE TBL_DUMMY CASCADE CONSTRAINT PURGE;

実行結果は以下のようになります。

INSERT前(DELETE後) : 0
INSERT後 : 1
INSERT(EXECUTE IMMEDIATE)後 : 2
ROLLBACK後 : 0

ROLLBACKによってTBL_DUMMYの内容は0件に戻りました。つまり、EXECUTE IMMEDIATEの前後で自動COMMITされているわけではないということです。

実験用コード(DDL)

おそらく質問者は、EXECUTE IMMEDIATEでDDLを使うと自動COMMITされるときの記憶と混ざっていたのだと思います。DDLを用いた動きも実験用コードを作って試してみます。こちらは会社では実験していません。

CREATE TABLE TBL_DUMMY AS SELECT 1 AS X FROM DUAL WHERE ROWNUM = 0;

DECLARE
	V_CNT	NUMBER;
BEGIN
	-- 初期化
	DELETE FROM TBL_DUMMY;
	COMMIT;
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'INSERT前(DELETE後) : ' || V_CNT );
	-- 通常INSERT
	INSERT INTO TBL_DUMMY VALUES ( 1 );
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'INSERT後 : ' || V_CNT );
	-- EXECUTE IMMEDIATE INSERT
	EXECUTE IMMEDIATE 'CREATE TABLE TBL_DUMMY2 AS SELECT * FROM DUAL';
	EXECUTE IMMEDIATE 'DROP TABLE TBL_DUMMY2';
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'EXECUTE IMMEDIATE後 : ' || V_CNT );
	-- ROLLBACK
	ROLLBACK;
	SELECT COUNT(*) INTO V_CNT FROM TBL_DUMMY;
	DBMS_OUTPUT.PUT_LINE ( 'ROLLBACK後 : ' || V_CNT );
END;


DROP TABLE TBL_DUMMY CASCADE CONSTRAINT PURGE;

実行結果は以下のようになります。

INSERT前(DELETE後) : 0
INSERT後 : 1
EXECUTE IMMEDIATE後 : 1
ROLLBACK後 : 1

ROLLBACKしても登録件数は0に戻りません。EXECUTE IMMEDIATEの前後で自動COMMITされていることが分かります。

公式ドキュメント(11g)で"Oracle Databaseは、データ定義言語(DDL)文の前後で、暗黙的COMMIT文を発行します。"と書かれているあたりの話です。公式表現は、自動COMMITではなく暗黙的COMMITでした。

まとめ

EXECUTE IMMEDIATEはトランザクション制御には無関係、EXECUTE IMMEDIATEによって実行されるSQLによっては自動COMMITが掛かるものもある、ということを実験しました。

実験環境に用いたOracleCloud環境は、こういうちょっとしたSQL確認に使う分には便利な環境です。極めて短時間でDB環境を準備できます。