ストアドプロシージャの長所と短所

2025 11/27

2025年11月27日 2025年11月25日

背景

Oracleのストアドプロシージャを多用するプロジェクトを引き継いで開発をすることになりました。
MVCフレームワークを使ったweb開発経験が長いエンジニアの観点で、ストアドプロシージャの長所と短所を共有します。

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

ストアドプロシージャ（Stored Procedure, SP）とは、データベース内に保存される一連のSQL処理やロジックを指します。

データベース内に保存されているため、アプリケーションから呼び出すだけで実行できる。
Oracle独自の手続き型言語であるPL/SQLで記述。
パラメータの受け渡しや処理結果の返却も可能。

基本的な構造

CREATE OR REPLACE PROCEDURE add_customer(
    p_name  IN VARCHAR2,
    p_email IN VARCHAR2,
    p_id    OUT NUMBER
) AS
BEGIN
    -- シーケンスからIDを取得
    SELECT seq_customers.NEXTVAL INTO p_id FROM dual;

    -- データを挿入
    INSERT INTO customers(customer_id, name, email)
    VALUES (p_id, p_name, p_email);

    -- コミット（必要に応じて）
    COMMIT;
END;

呼び出し例

DECLARE
    l_id NUMBER;
BEGIN
    add_customer('佐藤花子', 'sato@example.com', l_id);
END;

ストアドプロシージャでできること

1. 複雑なデータ操作

複数テーブルにまたがる挿入・更新・削除やトランザクション制御が可能です。

CREATE OR REPLACE PROCEDURE update_order_status(p_order_id IN NUMBER, p_status IN VARCHAR2)
AS
BEGIN
    -- 注文テーブルのステータス更新
    UPDATE orders
    SET status = p_status
    WHERE order_id = p_order_id;

    -- 注文明細テーブルのステータスも同時に更新
    UPDATE order_items
    SET item_status = p_status
    WHERE order_id = p_order_id;

    COMMIT; -- 明示的なトランザクション制御
END;

トランザクション管理をデータベース内で完結することができます。

2. 条件分岐やループ処理

PL/SQLではIF文やループ構文を用いて、複雑なロジックを実装できます。

CREATE OR REPLACE PROCEDURE apply_discount(p_customer_id IN NUMBER)
AS
    v_total NUMBER;
BEGIN
    SELECT SUM(amount) INTO v_total FROM orders WHERE customer_id = p_customer_id;

    IF v_total > 10000 THEN
        UPDATE orders SET discount = 0.1 WHERE customer_id = p_customer_id;
    ELSE
        UPDATE orders SET discount = 0.05 WHERE customer_id = p_customer_id;
    END IF;

    -- ループで明細に割引を適用
    FOR r IN (SELECT item_id, price FROM order_items WHERE customer_id = p_customer_id) LOOP
        UPDATE order_items
        SET price = r.price * 0.9
        WHERE item_id = r.item_id;
    END LOOP;

    COMMIT;
END;

3. 再利用可能な処理の定義

共通処理をSPに集約することで、複数のアプリケーションや他のSPから呼び出すことができます。

CREATE OR REPLACE PROCEDURE log_event(p_event_type IN VARCHAR2, p_message IN VARCHAR2)
AS
BEGIN
    INSERT INTO event_logs (event_type, message, created_at)
    VALUES (p_event_type, p_message, SYSDATE);
    COMMIT;
END;

BEGIN
    log_event('INFO', '注文処理開始');
    log_event('INFO', '注文処理終了');
END;

4. 高度なDB機能の利用

OracleをはじめとするDB固有の機能も活用できます。

-- カーソル操作の例（複数行取得）
CREATE OR REPLACE PROCEDURE print_customers
AS
    CURSOR c_customers IS SELECT customer_id, name FROM customers;
    v_customer c_customers%ROWTYPE;
BEGIN
    OPEN c_customers;
    LOOP
        FETCH c_customers INTO v_customer;
        EXIT WHEN c_customers%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_customer.customer_id || ' : ' || v_customer.name);
    END LOOP;
    CLOSE c_customers;
END;

-- 動的SQLの例（テーブル名を動的に変更）
CREATE OR REPLACE PROCEDURE dynamic_update(p_table IN VARCHAR2)
AS
BEGIN
    EXECUTE IMMEDIATE 'UPDATE ' || p_table || ' SET status = ''ACTIVE''';
    COMMIT;
END;

ORMapperでは実現しづらい処理も可能です。

ストアドプロシージャを多用するシステムの役割分担(例)

層	主な責務	具体例
DB（ストアドプロシージャ）	ビジネスロジックの多く、データの整合性、複雑な計算や集計	顧客ランク計算、受注処理、在庫引当、トランザクション制御
アプリケーション（Java, C#, PHPなど）	DB呼び出し、結果の表示、簡単なデータ整形	DBから取得した値を画面に渡す、ログ出力、エラーハンドリング
フロントエンド	表示、ユーザー操作	HTML/JS/CSSによるUI

ビジネスロジックはDBに集中し、アプリケーション側はほぼ呼び出すだけとなっています。

ストアドプロシージャの長所

1. パフォーマンス

ストアドプロシージャは「事前にコンパイルされる」ため、高速に実行できます。

また、アプリケーション側から大量のSQLを何度も送信する必要がなく「プロシージャを1回呼び出すだけ」で済むため、通信回数を減らせます。

2. セキュリティ

ストアドプロシージャは事前にDB側で定義された処理を呼び出す形式のため、SQLインジェクションなどのリスクを低減することができる
ストアドプロシージャ単位で実行権限を設定可能であり、アプリからtableへのアクセスを細かに管理できる

例：アプリは EXECUTE get_user のみ許可され、直接 SELECT * FROM users はできないようにする。
　　アプリはusersテーブルに対しても限定的にしかアクセスできない

ビジネスロジックをDBに閉じることで、アプリ側が間違って直接重要なデータを操作するリスクを減らせる

3. データベース側の処理を確認することで、システムの動作を概ね把握できる

DBへの理解は深いが、webアプリケーションの開発知識が乏しいメンバーがいる場合はメリットになるかもしれません。

ストアドプロシージャの短所

1. ソースコードの管理やデプロイに手間がかかる

DBに対する直接修正となるため、マイグレーション管理を行う場合、修正のたびにソースファイルが増加します。

マイグレーション管理ではなく同一のファイルで管理する場合、今どの修正がテスト環境・本番環境にあたっているかを確認したり、修正を実際に適応することを運用でカバーしなければなりません。

2. ストアドプロシージャにビジネスロジックが集中することによる課題

MVCフレームワークでは、model層やservice層に分かれた処理をストアドプロシージャが全て担うことになるため、自然と肥大化しやすくなります。

また、プロジェクト単位で明確なルールが定められていない場合、共通化が進まず、重複処理が発生するといった場面も多々見受けられます。

3. データがどうあるべきかを開発者が正確に把握する必要がある

MVCフレームワークではルールを宣言的に記述できますが、ストアドプロシージャでは明示的に手順としてルールを書くことが多くなります。
そのため、新しい画面やAPIを追加する際に、データの整合性を保つ上でミスが発生しやすくなります。

Rails（宣言的に書く）

Railsでは、「何を守りたいか」を宣言するだけで、フレームワークが自動的に処理してくれます。

class User < ApplicationRecord
  validates :email, presence: true, uniqueness: true
  before_save :normalize_email
end

validates :email, presence: true → 「email は必ず存在すべき」というルールを宣言
Railsが自動でレコードを保存する前にチェックを行うため、新しい画面やAPIを追加しても、宣言したルールは自動で適用される

ストアドプロシージャ（明示的に書く）

ストアドプロシージャでは、「どうやってルールを守るか」を手順として明示的に記述する必要があります。

CREATE OR REPLACE PROCEDURE insert_user(
  p_email IN VARCHAR2
) AS
BEGIN
  -- 存在チェック
  IF EXISTS (SELECT 1 FROM users WHERE email = p_email) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Email already exists');
  END IF;

  -- 挿入処理
  INSERT INTO users (email) VALUES (p_email);
END;