【PL/SQL】大規模システムにおけるモジュール分割と依存制御の最適化

許可する依存方向:
  API / Batch  -> Service
  Service      -> DAO
  Service      -> Common
  DAO          -> Table / View / Common
  API          -> Common

避ける依存方向:
  DAO          -> Service
  Common       -> API / Service / DAO
  Service A    -> Service B -> Service A
  Package Body -> 動的SQLで他業務パッケージを直接呼ぶ

CREATE OR REPLACE PACKAGE pkg_order_api AS
  TYPE t_order_result IS RECORD (
    order_id NUMBER,
    status   VARCHAR2(30),
    message  VARCHAR2(4000)
  );

  FUNCTION create_order(
    p_customer_id IN NUMBER,
    p_order_date  IN DATE
  ) RETURN t_order_result;
END pkg_order_api;
/

CREATE OR REPLACE PACKAGE BODY pkg_order_api AS
  FUNCTION validate_input(
    p_customer_id IN NUMBER,
    p_order_date  IN DATE
  ) RETURN VARCHAR2 IS
  BEGIN
    IF p_customer_id IS NULL THEN
      RETURN 'CUSTOMER_REQUIRED';
    END IF;
    IF p_order_date IS NULL THEN
      RETURN 'ORDER_DATE_REQUIRED';
    END IF;
    RETURN NULL;
  END;

  FUNCTION create_order(
    p_customer_id IN NUMBER,
    p_order_date  IN DATE
  ) RETURN t_order_result IS
    v_error VARCHAR2(100);
    v_order_id NUMBER;
  BEGIN
    v_error := validate_input(p_customer_id, p_order_date);
    IF v_error IS NOT NULL THEN
      RETURN t_order_result(NULL, 'ERROR', v_error);
    END IF;

    v_order_id := pkg_order_service.create_order(
      p_customer_id => p_customer_id,
      p_order_date  => p_order_date
    );

    RETURN t_order_result(v_order_id, 'SUCCESS', NULL);
  END;
END pkg_order_api;
/

SELECT
  owner,
  name,
  type,
  referenced_owner,
  referenced_name,
  referenced_type
FROM all_dependencies
WHERE owner = UPPER(:app_owner)
  AND type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
  AND referenced_owner IS NOT NULL
ORDER BY owner, name, referenced_owner, referenced_type, referenced_name;

WITH module_rule AS (
  SELECT 'PKG_%_DAO' AS name_pattern, 'DAO' AS layer_name FROM dual UNION ALL
  SELECT 'PKG_%_SERVICE', 'SERVICE' FROM dual UNION ALL
  SELECT 'PKG_%_API', 'API' FROM dual UNION ALL
  SELECT 'PKG_COMMON_%', 'COMMON' FROM dual
),
dep AS (
  SELECT
    owner,
    name,
    referenced_owner,
    referenced_name,
    referenced_type
  FROM all_dependencies
  WHERE owner = UPPER(:app_owner)
    AND referenced_owner = UPPER(:app_owner)
)
SELECT
  d.name AS from_object,
  d.referenced_name AS to_object,
  CASE
    WHEN d.name LIKE 'PKG\_%\_DAO' ESCAPE '\'
     AND d.referenced_name LIKE 'PKG\_%\_SERVICE' ESCAPE '\'
    THEN 'DAOからServiceを参照している'
    WHEN d.name LIKE 'PKG\_COMMON\_%' ESCAPE '\'
     AND d.referenced_name NOT LIKE 'PKG\_COMMON\_%' ESCAPE '\'
    THEN 'Commonから業務パッケージを参照している'
    WHEN d.name LIKE 'PKG\_%\_SERVICE' ESCAPE '\'
     AND d.referenced_name LIKE 'PKG\_%\_API' ESCAPE '\'
    THEN 'ServiceからAPIを参照している'
  END AS violation_reason
FROM dep d
WHERE (
    d.name LIKE 'PKG\_%\_DAO' ESCAPE '\'
    AND d.referenced_name LIKE 'PKG\_%\_SERVICE' ESCAPE '\'
  )
  OR (
    d.name LIKE 'PKG\_COMMON\_%' ESCAPE '\'
    AND d.referenced_name NOT LIKE 'PKG\_COMMON\_%' ESCAPE '\'
  )
  OR (
    d.name LIKE 'PKG\_%\_SERVICE' ESCAPE '\'
    AND d.referenced_name LIKE 'PKG\_%\_API' ESCAPE '\'
  )
ORDER BY violation_reason, from_object, to_object;

WITH dep AS (
  SELECT
    owner,
    name,
    type,
    referenced_owner,
    referenced_name,
    referenced_type
  FROM all_dependencies
  WHERE owner = UPPER(:app_owner)
    AND referenced_owner = UPPER(:app_owner)
    AND type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
)
SELECT
  owner || '.' || name AS object_name,
  referenced_owner || '.' || referenced_name AS referenced_object,
  SYS_CONNECT_BY_PATH(owner || '.' || name, ' -> ') AS path_text
FROM dep
WHERE CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE
       PRIOR referenced_owner = owner
   AND PRIOR referenced_name  = name
   AND PRIOR referenced_type  = type
START WITH owner = UPPER(:app_owner);

CREATE TABLE order_event_queue (
  event_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  event_type  VARCHAR2(50) NOT NULL,
  order_id    NUMBER NOT NULL,
  status      VARCHAR2(20) DEFAULT 'NEW' NOT NULL,
  payload     CLOB,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
  processed_at TIMESTAMP
);

CREATE INDEX ix_order_event_queue_status
  ON order_event_queue(status, created_at);

-- 注文APIは請求パッケージを直接呼ばず、イベントを登録する
INSERT INTO order_event_queue(event_type, order_id, payload)
VALUES('ORDER_CREATED', :order_id, :payload_json);

DECLARE
  v_package_name VARCHAR2(128) := 'PKG_BILLING_API';
  v_proc_name    VARCHAR2(128) := 'PROCESS_PAYMENT';
BEGIN
  -- 動的呼び出しを使うなら、呼び出し先をホワイトリスト化する。
  IF v_package_name NOT IN ('PKG_BILLING_API') THEN
    RAISE_APPLICATION_ERROR(-20000, 'Invalid dynamic target');
  END IF;

  EXECUTE IMMEDIATE
    'BEGIN ' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_package_name) ||
    '.' || DBMS_ASSERT.SIMPLE_SQL_NAME(v_proc_name) ||
    '(:1, :2); END;'
    USING :p_order_id, :p_amount;
END;
/

-- 1. INVALIDの残存確認
SELECT owner, object_type, object_name, status
FROM all_objects
WHERE owner = UPPER(:app_owner)
  AND status = 'INVALID'
ORDER BY object_type, object_name;

-- 2. 外部スキーマ参照の確認
SELECT owner, name, referenced_owner, referenced_name, referenced_type
FROM all_dependencies
WHERE owner = UPPER(:app_owner)
  AND referenced_owner IS NOT NULL
  AND referenced_owner <> owner
ORDER BY referenced_owner, referenced_type, referenced_name;

-- 3. 仕様部変更の影響確認
SELECT owner, name, type, referenced_owner, referenced_name, referenced_type
FROM all_dependencies
WHERE referenced_owner = UPPER(:app_owner)
  AND referenced_name = UPPER(:changed_package_name)
ORDER BY owner, type, name;