【PL/SQL】トランザクション設計パターン｜COMMIT境界・ロック・再処理まで

CREATE OR REPLACE PROCEDURE register_order(
  p_order_id IN NUMBER
) AS
BEGIN
  validate_order(p_order_id);
  insert_order_header(p_order_id);
  insert_order_detail(p_order_id);
  reserve_stock(p_order_id);
  write_order_history(p_order_id);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

CREATE OR REPLACE PROCEDURE update_customer_rank(
  p_customer_id IN NUMBER
) AS
BEGIN
  UPDATE customers
     SET rank_code = 'A'
   WHERE customer_id = p_customer_id;

  -- NG: 共通部品が勝手に確定している
  COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE update_customer_rank_core(
  p_customer_id IN NUMBER
) AS
BEGIN
  UPDATE customers
     SET rank_code = 'A'
   WHERE customer_id = p_customer_id;
END;
/

CREATE OR REPLACE PROCEDURE update_customer_rank_job(
  p_customer_id IN NUMBER
) AS
BEGIN
  update_customer_rank_core(p_customer_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

ALTER TABLE orders ADD CONSTRAINT uk_orders_request
  UNIQUE (request_id);

ALTER TABLE order_details ADD CONSTRAINT fk_order_details_header
  FOREIGN KEY (order_id) REFERENCES orders(order_id);

ALTER TABLE orders ADD CONSTRAINT ck_orders_status
  CHECK (status IN ('NEW', 'RESERVED', 'CANCELED', 'SHIPPED'));

DECLARE
  v_stock_qty stock_items.stock_qty%TYPE;
BEGIN
  SELECT stock_qty
    INTO v_stock_qty
    FROM stock_items
   WHERE item_id = :item_id
   FOR UPDATE;

  IF v_stock_qty < :order_qty THEN
    RAISE_APPLICATION_ERROR(-20001, '在庫不足です');
  END IF;

  UPDATE stock_items
     SET stock_qty = stock_qty - :order_qty
   WHERE item_id = :item_id;
END;

-- 画面: 他ユーザーが編集中ならすぐ戻す
SELECT *
  FROM orders
 WHERE order_id = :order_id
 FOR UPDATE NOWAIT;

-- ジョブ: ロックされていない待ち行だけ処理する
SELECT request_id
  FROM send_queue
 WHERE status = 'WAITING'
 ORDER BY request_id
 FOR UPDATE SKIP LOCKED;

-- 全処理で「顧客 -> 受注 -> 明細」の順に固定する
SELECT *
  FROM customers
 WHERE customer_id = :customer_id
 FOR UPDATE;

SELECT *
  FROM orders
 WHERE order_id = :order_id
 FOR UPDATE;

SELECT *
  FROM order_details
 WHERE order_id = :order_id
 FOR UPDATE;

BEGIN
  update_order_header(:order_id);

  SAVEPOINT before_optional_coupon;

  BEGIN
    apply_coupon(:order_id, :coupon_code);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO before_optional_coupon;
      write_warning_log(:order_id, SQLERRM);
  END;

  update_order_total(:order_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'ORDERS',
    err_log_table_name => 'ERR$_ORDERS'
  );
END;
/

INSERT INTO orders(order_id, customer_id, order_date, amount)
SELECT order_id, customer_id, order_date, amount
  FROM staging_orders
LOG ERRORS INTO err$_orders ('IMPORT_20260528')
REJECT LIMIT UNLIMITED;

DECLARE
  TYPE t_order_ids IS TABLE OF orders.order_id%TYPE;
  l_order_ids t_order_ids := t_order_ids(101, 102, 103);
BEGIN
  FORALL i IN 1 .. l_order_ids.COUNT SAVE EXCEPTIONS
    UPDATE orders
       SET status = 'CANCELED'
     WHERE order_id = l_order_ids(i);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      INSERT INTO batch_error_log(
        error_index,
        error_code,
        error_message
      ) VALUES (
        SQL%BULK_EXCEPTIONS(i).ERROR_INDEX,
        SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
        SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
      );
    END LOOP;
    ROLLBACK;
    RAISE;
END;

CREATE OR REPLACE PROCEDURE write_audit_log(
  p_message IN VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO audit_logs(logged_at, message)
  VALUES (SYSTIMESTAMP, p_message);

  COMMIT;
END;
/

BEGIN
  UPDATE orders
     SET status = 'CONFIRMED'
   WHERE order_id = :order_id;

  INSERT INTO event_outbox(
    event_id,
    event_type,
    aggregate_id,
    payload,
    status,
    created_at
  ) VALUES (
    event_seq.NEXTVAL,
    'ORDER_CONFIRMED',
    :order_id,
    :payload_json,
    'WAITING',
    SYSTIMESTAMP
  );

  COMMIT;
END;

DECLARE
  v_current_customer_id NUMBER := NULL;
BEGIN
  FOR r IN (
    SELECT customer_id, order_id
      FROM staging_orders
     ORDER BY customer_id, order_id
  ) LOOP
    IF v_current_customer_id IS NOT NULL
       AND v_current_customer_id <> r.customer_id THEN
      COMMIT;
    END IF;

    process_customer_order(r.customer_id, r.order_id);
    v_current_customer_id := r.customer_id;
  END LOOP;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

MERGE INTO payment_requests t
USING (
  SELECT :request_id AS request_id,
         :order_id AS order_id,
         :amount AS amount
    FROM dual
) s
ON (t.request_id = s.request_id)
WHEN NOT MATCHED THEN
  INSERT (request_id, order_id, amount, status, created_at)
  VALUES (s.request_id, s.order_id, s.amount, 'WAITING', SYSTIMESTAMP)
WHEN MATCHED THEN
  UPDATE SET last_seen_at = SYSTIMESTAMP;

SET TRANSACTION READ ONLY;

SELECT SUM(amount)
  FROM orders
 WHERE order_date >= DATE '2026-05-01'
   AND order_date <  DATE '2026-06-01';

COMMIT;

CREATE OR REPLACE PROCEDURE close_month(
  p_target_month IN DATE
) AS
BEGIN
  validate_month(p_target_month);
  create_billing(p_target_month);
  update_closing_status(p_target_month);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    write_audit_log(
      'close_month failed: ' || SQLERRM
    );
    RAISE;
END;
/