この記事の要点

ストアド・プロシージャは SQL + 手続き言語で書かれた処理を DB サーバに保存する仕組み 構文: CREATE PROCEDURE name(args) BEGIN ... END。Oracle PL/SQL / PostgreSQL PL/pgSQL / MySQL ストアド パラメータは IN / OUT / INOUT。ローカル変数・制御構文 (IF/WHILE/LOOP)・例外処理を持つ 性能利点: DB サーバ側で実行 = ネットワーク往復削減、コンパイル済 欠点: 言語固有・バージョン管理が困難・テストしにくい → 現代では Application 側でロジックを持つ設計が主流

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

ストアド・プロシージャ (Stored Procedure) は、SQL に加えて変数・制御構文・例外処理などの手続き的機能を持つ「DB に保存される関数」です。アプリケーションから CALL procedure_name(args) で呼び出すと、DB サーバ内で実行されます。

1980 年代に Sybase / SQL Server で導入され、Oracle の PL/SQL、PostgreSQL の PL/pgSQL、MySQL のストアドプロシージャとして標準的な機能になりました。

基本構文 (標準 SQL/PSM 風)

CREATE PROCEDURE procedure_name (
    IN  param1 INT,
    OUT param2 VARCHAR(100),
    INOUT param3 DECIMAL
)
BEGIN
    -- ローカル変数
    DECLARE local_var INT DEFAULT 0;

    -- 処理
    SELECT name INTO param2 FROM users WHERE id = param1;
    SET param3 = param3 * 1.1;

    -- 制御構文
    IF local_var > 0 THEN
        UPDATE stats SET count = count + 1;
    END IF;
END;

-- 呼び出し
CALL procedure_name(1, @name_out, @price_inout);
SELECT @name_out;

パラメータの種類

種類	方向	用途
IN	入力のみ	引数渡し（デフォルト）
OUT	出力のみ	結果を返す
INOUT	入出力	値を渡して書き換えて返す

Oracle PL/SQL

CREATE OR REPLACE PROCEDURE update_user_salary (
    p_user_id   IN  NUMBER,
    p_increment IN  NUMBER,
    p_new_total OUT NUMBER
) AS
    v_current_salary NUMBER;
    v_user_name      VARCHAR2(100);
BEGIN
    -- データ取得
    SELECT salary, name
    INTO v_current_salary, v_user_name
    FROM employees
    WHERE id = p_user_id;

    -- 計算
    p_new_total := v_current_salary + p_increment;

    -- 更新
    UPDATE employees SET salary = p_new_total WHERE id = p_user_id;

    -- ログ
    DBMS_OUTPUT.PUT_LINE('Updated ' || v_user_name || ' to ' || p_new_total);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'User not found: ' || p_user_id);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 呼び出し
DECLARE
    v_total NUMBER;
BEGIN
    update_user_salary(101, 5000, v_total);
    DBMS_OUTPUT.PUT_LINE('New total: ' || v_total);
END;
/

PostgreSQL PL/pgSQL

CREATE OR REPLACE PROCEDURE transfer_money(
    sender_id    INT,
    receiver_id  INT,
    amount       NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    sender_balance NUMERIC;
BEGIN
    SELECT balance INTO sender_balance
    FROM accounts WHERE id = sender_id FOR UPDATE;

    IF sender_balance < amount THEN
        RAISE EXCEPTION '残高不足: % < %', sender_balance, amount;
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;

    INSERT INTO transfer_log(from_id, to_id, amount, ts)
    VALUES (sender_id, receiver_id, amount, NOW());

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

CALL transfer_money(1, 2, 1000);

-- PostgreSQL は CREATE FUNCTION の方が一般的
-- (RETURN 句があり、SELECT で呼べる)

MySQL ストアドプロシージャ

DELIMITER //

CREATE PROCEDURE add_order(
    IN  p_user_id  INT,
    IN  p_amount   DECIMAL(10,2),
    OUT p_order_id INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    INSERT INTO orders(user_id, amount, created_at)
    VALUES (p_user_id, p_amount, NOW());

    SET p_order_id = LAST_INSERT_ID();

    UPDATE users SET total_orders = total_orders + 1 WHERE id = p_user_id;

    COMMIT;
END //

DELIMITER ;

-- 呼び出し
CALL add_order(101, 5000.00, @order_id);
SELECT @order_id;

制御構文

-- IF / ELSEIF / ELSE
IF condition THEN
    statements;
ELSEIF other_condition THEN
    statements;
ELSE
    statements;
END IF;

-- CASE
CASE input
    WHEN 1 THEN ...
    WHEN 2 THEN ...
    ELSE ...
END CASE;

-- WHILE ループ
WHILE i < 10 DO
    SET i = i + 1;
END WHILE;

-- REPEAT ループ
REPEAT
    SET i = i + 1;
UNTIL i >= 10
END REPEAT;

-- LOOP + LEAVE
my_loop: LOOP
    SET i = i + 1;
    IF i >= 10 THEN
        LEAVE my_loop;
    END IF;
END LOOP my_loop;

-- カーソル
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;
read_loop: LOOP
    FETCH cur INTO user_id;
    IF done THEN LEAVE read_loop; END IF;
    -- 処理
END LOOP;
CLOSE cur;

エラーハンドリング

-- MySQL: DECLARE HANDLER
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    RESIGNAL;
END;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;

-- Oracle: EXCEPTION
EXCEPTION
    WHEN NO_DATA_FOUND THEN ...
    WHEN TOO_MANY_ROWS THEN ...
    WHEN DUP_VAL_ON_INDEX THEN ...
    WHEN OTHERS THEN
        -- ログ出力 + 再 RAISE
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        RAISE;
END;

-- PostgreSQL: BEGIN ... EXCEPTION
EXCEPTION
    WHEN no_data_found THEN ...
    WHEN unique_violation THEN ...
    WHEN OTHERS THEN
        RAISE NOTICE '%', SQLERRM;
        RAISE;
END;

FUNCTION との違い

項目	PROCEDURE	FUNCTION
呼び出し	CALL proc()	SELECT func()
戻り値	無し (OUT で返す)	必須 (RETURN 句)
SQL 内利用	不可	可能（WHERE 句等で使える）
副作用 (DML)	OK	制限あり（DB によっては不可）
トランザクション制御	COMMIT/ROLLBACK 可	不可 (DB による)

TRIGGER との違い

• PROCEDURE: 明示的に CALL で起動
• TRIGGER: テーブルへの INSERT/UPDATE/DELETE で自動起動
• Trigger 内から PROCEDURE を呼ぶことも多い

性能上のメリット

1. ネットワーク往復削減: 複数 SQL を 1 回の CALL で実行
2. コンパイル済: 実行計画がキャッシュされる
3. パーミッション集約: テーブル直接権限なしでも PROCEDURE 経由でアクセス可
4. DB サーバ近接: 中間データを返さずに集計可能

欠点（現代的視点）

欠点	詳細
言語固有	PL/SQL ↔ PL/pgSQL ↔ MySQL で書換必要
バージョン管理が困難	DB 内に保存、Git との連動が難しい
テストしにくい	DB 接続が必要、CI/CD で扱いにくい
デバッグ困難	IDE / ステップ実行が限定的
スケールしにくい	DB サーバが計算リソース消費 → スケール先が高価
マイクロサービスと相性悪	サービス境界がぼやける

現代の使い分け

場面	推奨
レガシー大量データ処理 (ETL)	ストアドで OK
OLTP のビジネスロジック	Application 側 (Java / PHP / Python)
セキュリティ境界としての権限制御	ストアド有効
バッチ集計	ストアド or バッチアプリ
マイクロサービス	Application 側、ストアド避ける

デバッグとプロファイリング

-- MySQL: SHOW PROCEDURE STATUS
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW CREATE PROCEDURE proc_name;

-- Oracle: DBA_PROCEDURES, USER_OBJECTS
SELECT * FROM user_procedures;
SELECT text FROM user_source WHERE name = 'PROC_NAME' ORDER BY line;

-- PostgreSQL: \df+ proc_name
SELECT proname, prosrc FROM pg_proc WHERE proname = 'proc_name';

-- 削除
DROP PROCEDURE IF EXISTS proc_name;

アンチパターン

• 巨大な単一プロシージャ（1 万行超）: 保守不能。分割せよ
• 業務ロジックを全部 DB に: テスト不能、ベンダーロックイン
• Trigger ループ: A の更新で B のトリガが、B が C を…と連鎖し制御不能
• 動的 SQL の濫用: SQL インジェクションリスク、実行計画キャッシュ無効

FAQ

Q: ストアドプロシージャは時代遅れ？
A: 「禁止」というほどではないが、新規プロジェクトでは Application 側にロジックを置くのが主流。レガシーシステムでは依然現役。

Q: バージョン管理する方法は？
A: ストアドの定義を SQL ファイルとして Git 管理し、Flyway / Liquibase 等のマイグレーションツールで適用するのが一般的。

Q: パフォーマンスはアプリ実装より速い？
A: ネットワーク往復が多い処理（カーソルループ等）では速いが、現代の Connection Pool + バッチ API で多くは並べる。「無条件にストアドが速い」は誤解。

Q: Java から呼ぶには？
A: JDBC の CallableStatement を使います。{call proc(?, ?, ?)} 形式。Spring Data JPA でも @Procedure アノテーションで呼べます。