【PL/SQL】コードデプロイの自動化とEdition管理（CI/CD連携）

-- DBAまたは権限を持つ管理者が一度だけ実行する初期設定
ALTER USER app_user ENABLE EDITIONS;

-- Edition利用を明示的に許可する運用の場合
GRANT USE ON EDITION ora$base TO app_user;

-- 以後のデプロイでは、通常この初期設定を繰り返さない

-- 例: app_v1 を親にして app_v2 を作る
CREATE EDITION app_v2 AS CHILD OF app_v1;

-- この接続で以後のコンパイル先を app_v2 にする
ALTER SESSION SET EDITION = app_v2;

-- 確認
SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') AS current_edition
  FROM dual;

-- 実表は非Edition化オブジェクト
CREATE TABLE t_order(
  id         NUMBER PRIMARY KEY,
  amount    NUMBER(12,2) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

-- app_v1 の公開インターフェース
ALTER SESSION SET EDITION = app_v1;

CREATE OR REPLACE EDITIONING VIEW v_order AS
SELECT id, amount, created_at
  FROM t_order;

-- 後方互換の実表拡張
ALTER TABLE t_order ADD (
  currency CHAR(3) DEFAULT 'JPY' NOT NULL
);

-- app_v2 では新列も公開する
ALTER SESSION SET EDITION = app_v2;

CREATE OR REPLACE EDITIONING VIEW v_order AS
SELECT id, amount, created_at, currency
  FROM t_order;

-- 例: app_v2で fee 列を追加し、旧 amount から補完する
ALTER TABLE t_order ADD (
  fee NUMBER(12,2) DEFAULT 0 NOT NULL
);

-- 切替期間中、旧インターフェースからのDMLを新列へ補完する
CREATE OR REPLACE TRIGGER t_order_fee_fwd
  BEFORE INSERT OR UPDATE OF amount ON t_order
  FOR EACH ROW
  FORWARD CROSSEDITION
BEGIN
  IF :NEW.fee IS NULL OR :NEW.fee = 0 THEN
    :NEW.fee := ROUND(:NEW.amount * 0.03, 2);
  END IF;
END;
/

-- 01: 後方互換の表拡張
@01_tables_additive.sql

-- 02: Editioning Viewを新Editionで更新
@02_editioning_views.sql

-- 03: 必要な期間だけCrossedition Triggerを追加
@03_crossedition_triggers.sql

-- 10: 公開仕様を先にコンパイル
@10_package_specs.sql

-- 11: 実装を後からコンパイル
@11_package_bodies.sql

-- 90: 再コンパイルと品質ゲート
@90_quality_gate.sql

-- deploy.sql
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET DEFINE OFF
SET SERVEROUTPUT ON

ALTER SESSION SET EDITION = app_v2;

@01_tables_additive.sql
@02_editioning_views.sql
@03_crossedition_triggers.sql
@10_package_specs.sql
@11_package_bodies.sql
@90_quality_gate.sql

EXIT 0

name: deploy-plsql

on:
  workflow_dispatch:

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Deploy PL/SQL to new edition
        run: |
          sql app_user/${DB_PASSWORD}@${DB_CONNECT} @deploy.sql
        env:
          DB_PASSWORD: ${{ secrets.DB_PASSWORD }}
          DB_CONNECT: ${{ secrets.DB_CONNECT }}

SET SERVEROUTPUT ON

DECLARE
  v_invalid_count NUMBER;
  v_error_count   NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_invalid_count
    FROM user_objects
   WHERE status = 'INVALID';

  SELECT COUNT(*)
    INTO v_error_count
    FROM user_errors;

  IF v_invalid_count > 0 OR v_error_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('INVALID objects: ' || v_invalid_count);
    DBMS_OUTPUT.PUT_LINE('USER_ERRORS: ' || v_error_count);
    RAISE_APPLICATION_ERROR(-20001, 'PL/SQL deploy quality gate failed');
  END IF;
END;
/

SELECT object_name, object_type, status
  FROM user_objects
 WHERE status = 'INVALID'
 ORDER BY object_type, object_name;

SELECT name, type, line, position, text
  FROM user_errors
 ORDER BY name, sequence;

SELECT name, type, referenced_name, referenced_type
  FROM user_dependencies
 WHERE referenced_name IN ('V_ORDER', 'T_ORDER')
 ORDER BY name, referenced_name;

-- 接続プールの初期化SQLなどで実行する例
ALTER SESSION SET EDITION = app_v2;

-- アプリ起動直後の確認SQL
SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') AS current_edition
  FROM dual;

CREATE OR REPLACE TRIGGER set_app_edition_on_login
AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'APP_USER'
     AND SYS_CONTEXT('USERENV', 'MODULE') LIKE 'app-api%' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET EDITION = app_v2';
  END IF;
END;
/

ALTER SESSION SET EDITION = app_v2;

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'plsql-deploy',
    action_name => 'smoke-test-app-v2'
  );

  -- 主要ビューを軽く参照
  DECLARE
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM v_order WHERE ROWNUM <= 1;
  END;

  -- 主要パッケージの疎通確認
  app_order_api.health_check;
END;
/

-- アプリ接続の初期化SQLを旧Editionへ戻す
ALTER SESSION SET EDITION = app_v1;

-- 既存の新Editionセッションが残っていないか確認する
SELECT username, edition_name, status, module
  FROM v$session
 WHERE username = 'APP_USER';

-- 問題切り分けが終わるまで、新EditionやCETは急いでDROPしない