【PL/SQL】COMMITとROLLBACKの正しい使い方｜トランザクション境界設計・層別規約・例外統合・暗黙コミット罠・テンプレ3種

-- ===========================================================
-- API層：トランザクション境界。COMMIT/ROLLBACKは「ここだけ」
-- ===========================================================
CREATE OR REPLACE PACKAGE BODY pkg_api_order AS
  PROCEDURE post_order(p_payload IN CLOB, p_result OUT CLOB) AS
  BEGIN
    -- ビジネス処理は下位レイヤに完全委譲
    pkg_order_service.create_order(p_payload, p_result);

    -- 全部成功したらここで確定（ここがコミット責任の所在）
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;            -- 失敗時は完全ロールバック
      pkg_util_log.error(SQLERRM);
      RAISE;               -- 例外を上位（クライアント）へ伝搬
  END;
END pkg_api_order;
/

-- ===========================================================
-- Service層／Repository層：COMMITは絶対に書かない
-- ===========================================================
CREATE OR REPLACE PACKAGE BODY pkg_order_service AS
  PROCEDURE create_order(p_payload IN CLOB, p_result OUT CLOB) AS
  BEGIN
    pkg_order_repo.insert_header(...);
    pkg_order_repo.insert_items(...);
    pkg_inventory_repo.decrement(...);
    -- ❌ ここにCOMMITやROLLBACKを書いてはいけない
    -- 失敗時は例外を伝搬させ、API層のEXCEPTION句で処理させる
  END;
END pkg_order_service;
/

CREATE OR REPLACE PACKAGE BODY pkg_order_repo AS
  PROCEDURE insert_header(...) AS
  BEGIN
    INSERT INTO orders(...) VALUES(...);
    -- ❌ ここでもCOMMIT禁止
  END;
END pkg_order_repo;
/

-- ✅ 標準テンプレート：最上位での例外捕捉＋ROLLBACK＋ログ＋再raise
CREATE OR REPLACE PROCEDURE pkg_api.run_batch AS
BEGIN
  pkg_extract.run;
  pkg_transform.run;
  pkg_load.run;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;                              -- 全変更を取り消す
    pkg_util_log.error(                    -- 監査ログは独立コミット
      p_module => 'run_batch',
      p_msg    => SQLERRM,
      p_stack  => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;                                 -- 上位（呼び出し元）へ伝搬
END;
/

-- ✅ SAVEPOINT版：複数ジョブの一部失敗を許容
CREATE OR REPLACE PROCEDURE pkg_api.run_multi_jobs AS
BEGIN
  FOR rec IN (SELECT job_id FROM pending_jobs ORDER BY job_id) LOOP
    SAVEPOINT before_each_job;
    BEGIN
      pkg_job_runner.execute(rec.job_id);
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO before_each_job;       -- このジョブだけ取り消す
        pkg_util_log.error(
          p_module => 'job_' || rec.job_id,
          p_msg    => SQLERRM);
        -- 他のジョブは続行（RAISEしない設計）
    END;
  END LOOP;
  COMMIT;                                  -- 成功したジョブだけ確定
END;
/

-- ❌ アンチパターン：例外を握りつぶす
CREATE OR REPLACE PROCEDURE pkg_bad.run AS
BEGIN
  pkg_x.do_something;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN NULL;       -- ❌ 致命的：失敗が記録もされず気付けない
END;
/

-- ① DDLで暗黙コミットが発火する例
INSERT INTO orders(order_id, customer_id) VALUES(1, 100);
-- まだCOMMITしていない

CREATE TABLE temp_log(id NUMBER);   -- ← DDLで暗黙COMMIT発火！
DROP TABLE temp_log;                 -- ← もう一度DDLで暗黙COMMIT

ROLLBACK;                            -- もう取り消せない（INSERTは確定済み）
SELECT * FROM orders WHERE order_id = 1;  -- ヒットしてしまう

-- ② 上記事故を回避する方法：DDL前に明示的にコミットor取消し
INSERT INTO orders(order_id, customer_id) VALUES(2, 200);
ROLLBACK;                            -- 先に明示的に判断する
CREATE TABLE temp_log(id NUMBER);   -- これで影響なし

-- ③ TRUNCATE もDDLなので同じ
INSERT INTO logs(log_id, msg) VALUES(1, 'temp');
TRUNCATE TABLE work_table;          -- ← INSERTも一緒にコミット！

-- ✅ Java/JDBCから呼ばれる前提：COMMITしない、例外で失敗を伝える
CREATE OR REPLACE PROCEDURE create_order_for_api(
  p_customer_id IN  NUMBER,
  p_items_json  IN  CLOB,
  p_order_id    OUT NUMBER
) AS
BEGIN
  -- 入力検証
  IF p_customer_id IS NULL THEN
    RAISE_APPLICATION_ERROR(-20001, 'customer_id is required');
  END IF;

  -- 業務処理（DML実行）
  pkg_order_service.create_order(p_customer_id, p_items_json, p_order_id);

  -- ❌ ここにCOMMITを書かない（クライアントが管理する）
  -- ❌ ROLLBACKも書かない（クライアントが判断する）

EXCEPTION
  WHEN pkg_order_service.e_invalid_customer THEN
    RAISE_APPLICATION_ERROR(-20002, 'Invalid customer: ' || p_customer_id);
  -- WHEN OTHERS は書かない（自動的に上位へ伝搬される）
END;
/

-- Java側の典型実装（参考）
-- try {
--   conn.setAutoCommit(false);
--   try (CallableStatement cs = conn.prepareCall("{CALL create_order_for_api(?, ?, ?)}")) {
--     cs.setLong(1, customerId);
--     cs.setClob(2, itemsJson);
--     cs.registerOutParameter(3, Types.NUMERIC);
--     cs.execute();
--     orderId = cs.getLong(3);
--   }
--   conn.commit();          // ← ここでコミット
-- } catch (SQLException e) {
--   conn.rollback();        // ← ここでロールバック
--   throw e;
-- }

DECLARE
  CURSOR cur IS SELECT * FROM input_data;
  TYPE t_rec IS TABLE OF input_data%ROWTYPE;
  v_batch t_rec;
  C_BATCH_SIZE CONSTANT PLS_INTEGER := 1000;  -- 1000件ごとにCOMMIT
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_batch LIMIT C_BATCH_SIZE;
    EXIT WHEN v_batch.COUNT = 0;

    FORALL i IN 1 .. v_batch.COUNT
      INSERT INTO target_table VALUES v_batch(i);

    COMMIT;   -- バッチごとにコミット（ORA-01555を回避）
  END LOOP;
  CLOSE cur;
END;
/

-- ===========================================================
-- テンプレ1：APIエンドポイント（最上位＋例外＋ログ＋RAISE）
-- ===========================================================
CREATE OR REPLACE PROCEDURE api_xxx(
  p_input  IN  CLOB,
  p_output OUT CLOB
) AS
BEGIN
  -- ビジネスロジックは下位レイヤに完全委譲
  pkg_xxx_service.do_something(p_input, p_output);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    pkg_util_log.error('api_xxx', SQLERRM,
      DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RAISE;
END;
/

-- ===========================================================
-- テンプレ2：バッチ処理（バッチサイズコミット）
-- ===========================================================
CREATE OR REPLACE PROCEDURE batch_xxx AS
  CURSOR cur IS SELECT * FROM source_t WHERE processed = 0;
  TYPE t_rec IS TABLE OF source_t%ROWTYPE;
  v_buf  t_rec;
  C_BATCH CONSTANT PLS_INTEGER := 1000;
  v_total PLS_INTEGER := 0;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO v_buf LIMIT C_BATCH;
    EXIT WHEN v_buf.COUNT = 0;

    FORALL i IN 1 .. v_buf.COUNT
      UPDATE source_t SET processed = 1
       WHERE id = v_buf(i).id;

    v_total := v_total + v_buf.COUNT;
    COMMIT;   -- バッチごとに確定
  END LOOP;
  CLOSE cur;

  pkg_util_log.info('batch_xxx', '処理件数: ' || v_total);
EXCEPTION
  WHEN OTHERS THEN
    -- ROLLBACKは最終バッチ分のみ取消（既COMMIT分は残る）
    ROLLBACK;
    pkg_util_log.error('batch_xxx', SQLERRM);
    RAISE;
END;
/

-- ===========================================================
-- テンプレ3：複数ジョブの部分コミット（SAVEPOINT）
-- ===========================================================
CREATE OR REPLACE PROCEDURE multi_jobs AS
  v_ok PLS_INTEGER := 0;
  v_ng PLS_INTEGER := 0;
BEGIN
  FOR rec IN (SELECT job_id FROM pending_jobs) LOOP
    SAVEPOINT sp_each;
    BEGIN
      pkg_runner.execute(rec.job_id);
      v_ok := v_ok + 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO sp_each;
        v_ng := v_ng + 1;
        pkg_util_log.error('job_' || rec.job_id, SQLERRM);
    END;
  END LOOP;

  COMMIT;   -- 成功したジョブをまとめて確定
  pkg_util_log.info('multi_jobs', 'OK=' || v_ok || ' NG=' || v_ng);
END;
/