PL/SQLで規模が大きくなるほど、プロシージャやファンクションが散在してメンテナンスが難しくなります。パッケージを使うとインターフェースと実装を分離でき、名前空間で衝突を避け、初期化や状態の保持、オーバーロードなどを通じて再利用性を高められます。ここではパッケージ仕様と本体の基本、公開・非公開メンバーの設計、初期化セクション、状態管理、オーバーロード、依存関係と権限、ベストプラクティスまで実務視点で解説します。
パッケージの基本構造
パッケージは仕様（spec）と本体（body）で構成されます。仕様には外部に公開するプロシージャやファンクション、型、変数などの宣言を記述し、本体には実装と非公開メンバーを記述します。まずは最小例を示します。
-- 仕様（インターフェース）
CREATE OR REPLACE PACKAGE pkg_customer AS
-- 公開型
TYPE t_customer IS RECORD(
id NUMBER,
name VARCHAR2(100)
);
-- 公開ファンクション
FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2;
-- 公開プロシージャ
PROCEDURE upsert_customer(p_id NUMBER, p_name VARCHAR2);
END pkg_customer;
/
-- 本体（実装）
CREATE OR REPLACE PACKAGE BODY pkg_customer AS
-- 非公開変数（パッケージ状態）
g_changed_count PLS_INTEGER := 0;
-- 非公開ヘルパー
PROCEDURE log_change(p_id NUMBER, p_name VARCHAR2) IS
BEGIN
g_changed_count := g_changed_count + 1;
DBMS_OUTPUT.PUT_LINE('changed: ' || p_id || ' -> ' || p_name);
END;
FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(100);
BEGIN
SELECT name INTO v_name FROM customers WHERE id = p_id;
RETURN v_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
PROCEDURE upsert_customer(p_id NUMBER, p_name VARCHAR2) IS
v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM customers WHERE id = p_id;
IF v_exists = 0 THEN
INSERT INTO customers(id, name) VALUES(p_id, p_name);
ELSE
UPDATE customers SET name = p_name WHERE id = p_id;
END IF;
log_change(p_id, p_name); -- 非公開プロシージャ呼び出し
END;
-- パッケージ初期化セクション
BEGIN
DBMS_OUTPUT.PUT_LINE('pkg_customer initialized');
END pkg_customer;
/
この例では仕様で公開シグネチャを固定し、本体で非公開の変数とヘルパーを隠蔽しています。初期化セクションは最初の参照時に一度だけ実行されるため、キャッシュの準備や環境チェックに便利です。
公開と非公開の設計指針
仕様は外部契約であり、互換性を壊す変更は避けるべきです。呼び出し側から利用されるAPIは仕様に記述し、内部的な補助ロジックや一時変数は本体のみに置くことで変更容易性を確保できます。公開するものはプロシージャとファンクションのシグネチャ、必要なら定数や型、例外名を選びます。逆に実装詳細は本体だけに置くとテストや差し替えが容易になります。
状態管理と初期化の活用
パッケージにはセッションスコープの変数を保持できます。参照回数や一時的なキャッシュ、フラグなどをg_で始める命名で整理すると読みやすくなります。初期化セクションではテーブル存在チェックやコンテキスト取得を行えますが、長時間かかる処理は避け、エラー時のハンドリングを明示しておくと安全です。
CREATE OR REPLACE PACKAGE pkg_ctx AS
PROCEDURE set_lang(p_lang VARCHAR2);
FUNCTION current_lang RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_ctx AS
g_lang VARCHAR2(10);
PROCEDURE set_lang(p_lang VARCHAR2) IS
BEGIN
g_lang := p_lang;
END;
FUNCTION current_lang RETURN VARCHAR2 IS
BEGIN
RETURN NVL(g_lang, 'ja');
END;
BEGIN
g_lang := SYS_CONTEXT('USERENV','LANG'); -- 初期化で既定値
END;
/
オーバーロードで使い勝手を高める
同名で引数の型や個数が異なるサブルーチンを複数宣言して、呼び出し易さを向上できます。
CREATE OR REPLACE PACKAGE pkg_order AS
PROCEDURE add_item(p_order_id NUMBER, p_item_id NUMBER, p_qty NUMBER);
PROCEDURE add_item(p_order_id NUMBER, p_sku VARCHAR2, p_qty NUMBER);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_order AS
PROCEDURE add_item(p_order_id NUMBER, p_item_id NUMBER, p_qty NUMBER) IS
BEGIN
INSERT INTO order_items(order_id,item_id,qty) VALUES(p_order_id,p_item_id,p_qty);
END;
PROCEDURE add_item(p_order_id NUMBER, p_sku VARCHAR2, p_qty NUMBER) IS
v_item_id NUMBER;
BEGIN
SELECT id INTO v_item_id FROM items WHERE sku = p_sku;
add_item(p_order_id, v_item_id, p_qty);
END;
END;
/
呼び出し側は状況に応じた引数で同じ手続きを使えるため、APIがシンプルになります。
結果セットの返却とパフォーマンス
アプリケーション層にデータを渡す場合、REF CURSORを使うと柔軟です。大量データ処理にはBULK COLLECTとFORALLを併用し、コンテキストスイッチを減らして高速化します。
CREATE OR REPLACE PACKAGE pkg_query AS
TYPE t_cur IS REF CURSOR;
PROCEDURE list_active(p_cur OUT t_cur);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_query AS
PROCEDURE list_active(p_cur OUT t_cur) IS
BEGIN
OPEN p_cur FOR
SELECT id, name FROM customers WHERE active = 1 ORDER BY id;
END;
END;
/
例外と独自例外の公開
仕様に例外名を公開すると、呼び出し側で明確にハンドリングできます。実装側ではRAISE_APPLICATION_ERRORを用いて詳細を付与します。
CREATE OR REPLACE PACKAGE pkg_auth AS
e_invalid_user EXCEPTION;
PRAGMA EXCEPTION_INIT(e_invalid_user, -20001);
PROCEDURE login(p_user VARCHAR2, p_pass VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_auth AS
PROCEDURE login(p_user VARCHAR2, p_pass VARCHAR2) IS
v_ok NUMBER;
BEGIN
SELECT COUNT(*) INTO v_ok FROM users WHERE uname=p_user AND upass=p_pass;
IF v_ok = 0 THEN
RAISE e_invalid_user;
END IF;
END;
END;
/
依存関係と権限のポイント
アプリケーションスキーマとは別スキーマにパッケージを置く場合、実行権限と必要なオブジェクト権限を付与します。GRANTで明示し、必要に応じてシノニムを作ると呼び出しが簡潔になります。
-- 実行権限を付与
GRANT EXECUTE ON app.pkg_customer TO webapp;
-- 呼び出し側スキーマでシノニム作成
CREATE SYNONYM pkg_customer FOR app.pkg_customer;
テストとコンパイル、変更管理
仕様を変更すると本体の再コンパイルが必要になります。ALTER PACKAGEで本体のみ再コンパイルでき、依存オブジェクトの無効化に注意します。単体テストはDBMS_SQL、UTPLSQLなどのフレームワークを用いると自動化しやすくなります。
ALTER PACKAGE pkg_customer COMPILE;
ALTER PACKAGE pkg_customer COMPILE BODY;
SHOW ERRORS PACKAGE pkg_customer;
SHOW ERRORS PACKAGE BODY pkg_customer;
ベストプラクティス
公開APIの最小化と仕様の安定化を最優先に設計すると将来の差し替えが容易になります。命名規約を統一してg_やp_などの接頭辞で役割を明確にすると可読性が向上します。副作用のある処理はプロシージャ、純粋関数はファンクションに分けることでテスト容易性が高まります。パッケージ初期化で重い処理を行わず、例外は公開名で扱い、詳細はログに残す方針が安全です。パフォーマンス面では必要に応じてBULK処理やREF CURSORを取り入れ、不要なコンテキストスイッチを避けることが要点になります。
まとめ
PL/SQLのパッケージは、機能をひとかたまりのモジュールとして公開し、実装詳細を隠蔽できる強力な仕組みです。仕様と本体の分離で変更に強く、状態と初期化の活用で実務ロジックを整理でき、オーバーロードと結果セット返却で使い勝手を高められます。権限や依存関係を適切に管理し、テストとコンパイルを運用に組み込むことで、再利用性と保守性に優れたデータベース層を構築できます。
