タイトル: ストアド・プロシージャ
SEOタイトル: SQL ストアド・プロシージャ完全ガイド
| この記事の要点 |
|
ストアド・プロシージャとは
ストアド・プロシージャ (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 を呼ぶことも多い
性能上のメリット
- ネットワーク往復削減: 複数 SQL を 1 回の CALL で実行
- コンパイル済: 実行計画がキャッシュされる
- パーミッション集約: テーブル直接権限なしでも PROCEDURE 経由でアクセス可
- 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 アノテーションで呼べます。