【PL/SQL】パッケージ仕様と本体の分離設計｜依存関係・再コンパイル・デプロイ順序まで

CREATE OR REPLACE PACKAGE math_util AS
  FUNCTION add(
    p_a IN NUMBER,
    p_b IN NUMBER
  ) RETURN NUMBER;

  FUNCTION subtract(
    p_a IN NUMBER,
    p_b IN NUMBER
  ) RETURN NUMBER;
END math_util;
/

CREATE OR REPLACE PACKAGE BODY math_util AS
  FUNCTION add(
    p_a IN NUMBER,
    p_b IN NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN p_a + p_b;
  END add;

  FUNCTION subtract(
    p_a IN NUMBER,
    p_b IN NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    RETURN p_a - p_b;
  END subtract;
END math_util;
/

CREATE OR REPLACE PACKAGE customer_loader AS
  PROCEDURE load_customers(
    p_batch_id IN NUMBER
  );
END customer_loader;
/

CREATE OR REPLACE PACKAGE BODY customer_loader AS
  FUNCTION is_valid_email(
    p_email IN VARCHAR2
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN p_email IS NOT NULL
       AND REGEXP_LIKE(p_email, '^[^@]+@[^@]+\.[^@]+$');
  END is_valid_email;

  PROCEDURE load_customers(
    p_batch_id IN NUMBER
  ) IS
  BEGIN
    FOR r IN (
      SELECT customer_id, customer_name, email
      FROM customer_stg
      WHERE batch_id = p_batch_id
    ) LOOP
      IF is_valid_email(r.email) THEN
        INSERT INTO customers(customer_id, customer_name, email)
        VALUES (r.customer_id, r.customer_name, r.email);
      ELSE
        INSERT INTO customer_load_error(batch_id, customer_id, message)
        VALUES (p_batch_id, r.customer_id, 'invalid email');
      END IF;
    END LOOP;
  END load_customers;
END customer_loader;
/

SELECT name,
       type,
       referenced_owner,
       referenced_name,
       referenced_type
FROM user_dependencies
WHERE referenced_name = 'CUSTOMER_LOADER'
ORDER BY type, name;

SELECT owner,
       name,
       type,
       referenced_owner,
       referenced_name,
       referenced_type
FROM all_dependencies
WHERE referenced_name = 'CUSTOMER_LOADER'
ORDER BY owner, type, name;

SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN ('CUSTOMER_LOADER')
   OR status = 'INVALID'
ORDER BY object_type, object_name;

SELECT name, type, line, position, text
FROM user_errors
WHERE name = 'CUSTOMER_LOADER'
ORDER BY sequence;

-- 仕様変更あり
@customer_loader.pks
@customer_loader.pkb
@dependent_package.pkb

-- 本体だけ変更
@customer_loader.pkb

-- 最後に状態確認
SELECT object_name, object_type, status
FROM user_objects
WHERE status = 'INVALID';

CREATE OR REPLACE PACKAGE session_cache AS
  PROCEDURE set_value(p_value IN VARCHAR2);
  FUNCTION get_value RETURN VARCHAR2;
END session_cache;
/

CREATE OR REPLACE PACKAGE BODY session_cache AS
  g_value VARCHAR2(100);

  PROCEDURE set_value(p_value IN VARCHAR2) IS
  BEGIN
    g_value := p_value;
  END;

  FUNCTION get_value RETURN VARCHAR2 IS
  BEGIN
    RETURN g_value;
  END;
END session_cache;
/

CREATE OR REPLACE PACKAGE order_api
AUTHID DEFINER
AS
  PROCEDURE create_order(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  );
END order_api;
/

GRANT EXECUTE ON order_api TO app_user;

CREATE OR REPLACE PACKAGE order_internal
AUTHID DEFINER
ACCESSIBLE BY (order_api)
AS
  PROCEDURE validate_order(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  );
END order_internal;
/

CREATE OR REPLACE PACKAGE order_api
AUTHID DEFINER
AS
  PROCEDURE create_order(
    p_customer_id IN NUMBER,
    p_amount      IN NUMBER
  );
END order_api;
/

database/
  packages/
    customer_loader.pks
    customer_loader.pkb
    order_api.pks
    order_api.pkb
  deploy/
    001_create_specs.sql
    002_create_bodies.sql
    003_grants.sql
    004_check_invalid_objects.sql

CREATE OR REPLACE PACKAGE BODY customer_loader AS
  PROCEDURE load_customers(p_batch_id IN NUMBER) IS
  BEGIN
    DBMS_APPLICATION_INFO.SET_MODULE(
      module_name => 'CUSTOMER_LOADER',
      action_name => 'LOAD_CUSTOMERS'
    );

    -- load logic
    NULL;

    DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
      RAISE;
  END load_customers;
END customer_loader;
/