【PL/SQL】パッケージ設計でコード管理と再利用性を極める｜レイヤード分割・Repository・依存制御・命名規約・テスト容易性

-- ✅ SPEC: 契約だけ（クライアントが読むドキュメント）
CREATE OR REPLACE PACKAGE pkg_order_service AS
  /*
   * 注文関連のビジネスロジック層
   * 利用例:
   *   v_order_id := pkg_order_service.create_order(p_customer_id, p_items);
   */

  -- 公開例外
  e_invalid_customer EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_customer, -20101);

  -- 注文作成（複数明細を一括登録、IDを返す）
  FUNCTION create_order(
    p_customer_id IN NUMBER,
    p_items       IN order_item_tab    -- 公開型（別パッケージで定義）
  ) RETURN NUMBER;

  -- 注文取消
  PROCEDURE cancel_order(p_order_id IN NUMBER);

  -- 注文金額の取得
  FUNCTION calc_total(p_order_id IN NUMBER) RETURN NUMBER;
END pkg_order_service;
/

-- ✅ BODY: 実装と内部詳細を完全隠蔽
CREATE OR REPLACE PACKAGE BODY pkg_order_service AS

  -- 内部定数（SPECに書く必要はない）
  c_status_active CONSTANT VARCHAR2(10) := 'ACTIVE';

  -- 内部ヘルパー（外部から呼ばれない）
  FUNCTION resolve_tax_rate(p_country VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN CASE p_country WHEN 'JP' THEN 0.10 ELSE 0.00 END;
  END;

  FUNCTION create_order(
    p_customer_id IN NUMBER,
    p_items       IN order_item_tab
  ) RETURN NUMBER AS
    v_order_id NUMBER;
  BEGIN
    -- Repositoryに移譲（SQLは書かない）
    v_order_id := pkg_order_repo.insert_order(p_customer_id);
    pkg_order_repo.insert_items(v_order_id, p_items);
    RETURN v_order_id;
  END;

  PROCEDURE cancel_order(p_order_id IN NUMBER) AS
  BEGIN
    pkg_order_repo.update_status(p_order_id, 'CANCELLED');
  END;

  FUNCTION calc_total(p_order_id IN NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN pkg_order_repo.sum_amount(p_order_id);
  END;
END pkg_order_service;
/

-- ┌─────────────┐
-- │ API層       │ pkg_api_order
-- ├─────────────┤
-- │ Service層   │ pkg_order_service
-- ├─────────────┤
-- │ Repository層│ pkg_order_repo
-- ├─────────────┤
-- │ Util層      │ pkg_util_date / pkg_util_log
-- └─────────────┘

-- API層: 外部入口（バリデーション・例外翻訳・監査ログ）
CREATE OR REPLACE PACKAGE pkg_api_order AS
  FUNCTION post_order(p_json CLOB) RETURN CLOB;
END pkg_api_order;
/

CREATE OR REPLACE PACKAGE BODY pkg_api_order AS
  FUNCTION post_order(p_json CLOB) RETURN CLOB AS
    v_cust  NUMBER;
    v_items order_item_tab;
    v_id    NUMBER;
  BEGIN
    -- バリデーションとパース（Util層を活用）
    pkg_util_log.audit('post_order', p_json);
    pkg_util_json.parse_order(p_json, v_cust, v_items);

    -- ビジネス処理はService層に委譲
    v_id := pkg_order_service.create_order(v_cust, v_items);

    -- 結果フォーマット
    RETURN pkg_util_json.build_response(v_id);
  EXCEPTION
    WHEN pkg_order_service.e_invalid_customer THEN
      RETURN pkg_util_json.error_response(400, 'Invalid customer');
  END;
END pkg_api_order;
/

-- Repository層: SQLはここだけに集中
CREATE OR REPLACE PACKAGE pkg_order_repo AS
  FUNCTION insert_order(p_customer_id NUMBER) RETURN NUMBER;
  PROCEDURE insert_items(p_order_id NUMBER, p_items order_item_tab);
  PROCEDURE update_status(p_order_id NUMBER, p_status VARCHAR2);
  FUNCTION sum_amount(p_order_id NUMBER) RETURN NUMBER;
END pkg_order_repo;
/

CREATE OR REPLACE PACKAGE BODY pkg_order_repo AS
  FUNCTION insert_order(p_customer_id NUMBER) RETURN NUMBER AS
    v_id NUMBER;
  BEGIN
    INSERT INTO orders(order_id, customer_id, status, created_at)
    VALUES(orders_seq.NEXTVAL, p_customer_id, 'NEW', SYSDATE)
    RETURNING order_id INTO v_id;
    RETURN v_id;
  END;
  -- 他の実装は省略
END pkg_order_repo;
/

CREATE OR REPLACE PACKAGE pkg_config AS
  FUNCTION get(p_key VARCHAR2) RETURN VARCHAR2;
  PROCEDURE refresh;
END pkg_config;
/

CREATE OR REPLACE PACKAGE BODY pkg_config AS
  -- セッション内で1つだけ存在する状態
  TYPE t_map IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(64);
  g_cache  t_map;

  PROCEDURE refresh AS
  BEGIN
    g_cache.DELETE;   -- 全クリア
    FOR rec IN (SELECT k, v FROM app_config) LOOP
      g_cache(rec.k) := rec.v;
    END LOOP;
  END;

  FUNCTION get(p_key VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    RETURN g_cache(p_key);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  END;
BEGIN
  -- 初期化セクション: パッケージが初参照されたときに1度だけ実行
  refresh;
END pkg_config;
/

-- 利用例（高速・SQL不要）
DECLARE
  v_url VARCHAR2(200);
BEGIN
  v_url := pkg_config.get('api.endpoint');
END;
/

-- 全依存関係を一覧（自スキーマのパッケージのみ）
SELECT name, referenced_name, referenced_type
  FROM user_dependencies
 WHERE type = 'PACKAGE BODY'
   AND referenced_type = 'PACKAGE'
 ORDER BY name, referenced_name;

-- 循環依存を検出（Aが参照するBがAを参照している）
SELECT d1.name AS pkg_a, d1.referenced_name AS pkg_b
  FROM user_dependencies d1
  JOIN user_dependencies d2
    ON d1.name = d2.referenced_name
   AND d1.referenced_name = d2.name
 WHERE d1.type = 'PACKAGE BODY'
   AND d2.type = 'PACKAGE BODY'
   AND d1.referenced_type = 'PACKAGE'
   AND d2.referenced_type = 'PACKAGE';

-- レイヤ違反を検出（命名規約 pkg_*_repo が pkg_*_service を呼んでいないか）
SELECT name, referenced_name
  FROM user_dependencies
 WHERE name LIKE 'PKG_%_REPO'
   AND referenced_name LIKE 'PKG_%_SERVICE'
   AND type = 'PACKAGE BODY';

-- 設定キャッシュ
CREATE OR REPLACE PACKAGE pkg_user_ctx AS
  PROCEDURE set_current_user(p_user_id NUMBER);
  FUNCTION  get_current_user RETURN NUMBER;
  PROCEDURE clear;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_user_ctx AS
  g_user_id NUMBER;
  PROCEDURE set_current_user(p_user_id NUMBER) IS
  BEGIN g_user_id := p_user_id; END;
  FUNCTION get_current_user RETURN NUMBER IS BEGIN RETURN g_user_id; END;
  PROCEDURE clear IS BEGIN g_user_id := NULL; END;
END;
/

-- アプリケーション側（Java/.NET等）でセッション返却時に必ずクリア
-- BEGIN pkg_user_ctx.clear; END;
-- もしくは、全パッケージステートを一括リセット
-- BEGIN DBMS_SESSION.RESET_PACKAGE; END;

-- Repositoryインターフェースをパッケージ仕様で抽象化
CREATE OR REPLACE PACKAGE pkg_order_repo_iface AS
  FUNCTION insert_order(p_cust NUMBER) RETURN NUMBER;
END;
/

-- 本番実装
CREATE OR REPLACE PACKAGE BODY pkg_order_repo_iface AS
  FUNCTION insert_order(p_cust NUMBER) RETURN NUMBER AS
  BEGIN
    RETURN pkg_order_repo.insert_order(p_cust);
  END;
END;
/

-- Service層は「ifaceを呼ぶ」と決まっている
CREATE OR REPLACE PACKAGE BODY pkg_order_service AS
  FUNCTION create_order(p_cust NUMBER) RETURN NUMBER AS
  BEGIN
    -- 本番でもテストでも同じコード
    RETURN pkg_order_repo_iface.insert_order(p_cust);
  END;
END;
/

-- テスト時はpkg_order_repo_ifaceのBODYだけ差し替えてモック化
-- CREATE OR REPLACE PACKAGE BODY pkg_order_repo_iface AS
--   FUNCTION insert_order(p_cust NUMBER) RETURN NUMBER AS
--   BEGIN
--     RETURN 99999;  -- モック値を返すだけ
--   END;
-- END;
-- /

-- テスト関数（utPLSQLフレームワーク等）
CREATE OR REPLACE PROCEDURE test_create_order AS
BEGIN
  ASSERT(pkg_order_service.create_order(100) = 99999, 'モックが返るはず');
END;
/