データベース操作・制御

ストアドプロシージャすとあどぷろしーじゃ

SQLプログラミングパフォーマンスデータベースオブジェクトトランザクションPL/SQL

ストアドプロシージャについて教えて

簡単に言うとこんな感じ！

ストアドプロシージャは「DB内部に保存しておくSQL手順書」だよ！毎回アプリからSQLを送るんじゃなく、よく使う複雑な処理を名前をつけてDB側に登録しておいて「その名前で呼び出すだけ」にできる。料理レシピを毎回手書きするんじゃなく、料理名だけ言えばシェフが作ってくれる感じ！

ストアドプロシージャとは

ストアドプロシージャ（Stored Procedure）とは、複数のSQL文や制御構文（IF/LOOP等）をひとまとめにしてデータベース内部に保存しておく「手続き型プログラム」です。“stored”（保存された）+ “procedure”（手続き）の意味そのまま、DB側に格納された処理ロジックです。

アプリケーション側からは「プロシージャ名と引数を指定して呼び出す」だけで、複雑な処理をDB側で完結させることができます。主なメリットは3つあります。1つ目はパフォーマンス：実行計画が事前にコンパイル・キャッシュされるため、毎回SQLを解析するより速い場合があります。2つ目はネットワーク通信の削減：複数のSQL文を1回の呼び出しにまとめられます。3つ目はセキュリティ：テーブルに直接アクセスさせず、プロシージャ経由のみに限定できます。

一方、デメリットとしてはDB製品ごとに構文が異なり（Oracle：PL/SQL、SQL Server：T-SQL、PostgreSQL：PL/pgSQLなど）、DBを変更するときにプロシージャの書き直しが必要になる点や、バージョン管理・テストがしにくい点が挙げられます。

ストアドプロシージャの構造

PostgreSQLでのストアドプロシージャ例

CREATE OR REPLACE PROCEDURE update_stock(
    p_product_id INTEGER,
    p_quantity    INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 在庫チェック
    IF (SELECT stock FROM products WHERE id = p_product_id) < p_quantity THEN
        RAISE EXCEPTION '在庫不足: product_id=%', p_product_id;
    END IF;

    -- 在庫を減らす
    UPDATE products
    SET    stock = stock - p_quantity
    WHERE  id = p_product_id;

    -- 出荷ログを記録
    INSERT INTO shipment_log (product_id, qty, shipped_at)
    VALUES (p_product_id, p_quantity, NOW());

    COMMIT;
END;
$$;

-- 呼び出し
CALL update_stock(123, 5);

ストアドプロシージャ vs アプリ層での処理

観点	ストアドプロシージャ	アプリ層
パフォーマンス	実行計画キャッシュで高速	毎回SQL送信
ネットワーク	往復回数を削減できる	SQL毎に通信
可搬性	DB製品依存（Oracle→PostgreSQL等で書き直し必要）	言語で自由に記述
バージョン管理	Gitで管理しにくい	コードとしてGit管理
テスト	単体テストが難しい	フレームワークでテスト容易
向いている用途	バッチ処理・複雑な集計・セキュリティ境界の設定	柔軟なビジネスロジック

歴史と背景

1992年：SQL-92 でストアドプロシージャの概念が議論に加わる（標準化は後年）
1994年：Oracle PL/SQL、Microsoft T-SQL が独自のストアドプロシージャ言語として普及
1999年：SQL/PSM（Persistent Stored Modules）として SQL:1999 標準に組み込まれる
2000年代：Javaストアドプロシージャ（Java in DB）など、手続き型言語以外での実装も登場
2017年：PostgreSQL 11 でストアドプロシージャ（PROCEDURE）が正式サポート。それ以前はファンクション（FUNCTION）のみだった
現在：マイクロサービス化・ORM普及に伴い「ロジックはアプリ層に」という流れも強まるが、バッチ・集計・セキュリティ用途では引き続き活用されている

規格	内容
ISO/IEC 9075-4 SQL/PSM	ストアドプロシージャ・ファンクションの国際標準
Oracle PL/SQL	Oracle 独自の手続き型SQL拡張
Microsoft T-SQL	SQL Server / Azure SQL 独自の手続き型SQL拡張
PL/pgSQL	PostgreSQL 独自の手続き型言語（SQL/PSM準拠）