在庫の引き当てをアプリから五本のSQLで流していたら、混雑時だけ二重に引き当てられた。前任者が残したプロシージャを直そうとしたら、どのアプリから呼ばれているのか誰も知らなかった。ストアドプロシージャにまつわる事故は、書き方より置き方の判断で起きることが多い種類のものです。
この記事では、データベースに処理を保存して呼び出すストアドプロシージャを、作り方と採用判断の両面から扱います。MySQLでの定義とPythonからの呼び出し、SQL Server・PostgreSQL・Oracleとの構文差、性能とロックの効き方、権限設計、そしてアプリ側に書く場合との比較までを、実行できるSQL付きで並べました。データベース全般の位置づけはデータベースとは？種類・DBMS・RDBとNoSQLの選び方を実装目線で解説に、SQLそのものの文法はSQLとは？文の種類（DDL・DML・DCL）と実行順序・RDBMS方言を実装目線で解説に譲ります。仕様の前提は2026年10月時点の公式文書（MySQL 8.4系、SQL Server 2025世代のMicrosoft Learn、PostgreSQL 18系、Oracle Database 26ai世代）です。
まとめ：ストアドプロシージャを採用する前に決める5点
第一に、ストアドプロシージャは複数のSQLと制御構文をひとまとめにしてデータベースに保存し、名前で呼び出す仕組みです。呼び出しはMySQLとPostgreSQLがCALL、SQL ServerがEXECと製品ごとに違い、書く言語も方言ごとに異なります。
第二に、速さの根拠は通信の往復が減ることにあります。「事前にコンパイル済みだから速い」という説明は現行の実装と合いません。SQL Serverは初回実行時にコンパイルし、PostgreSQLとMySQLもセッション単位で解析結果を保持する方式です。
第三に、実行時の権限の既定値がDBMSによって逆になっています。MySQLとOracleは定義者の権限、PostgreSQLとSQL Serverは呼び出し側の権限が基本です。最小権限の設計はこの差を前提に組み立てます。
第四に、プロシージャを使えば注入攻撃を防げるとは限りません。内部で文字列を連結して動的SQLを組めば、アプリで直接組み立てた場合と同じ穴が開きます。
第五に、採用は「結果が入力より小さい処理」「整合性の最終防衛」「テーブル権限を渡せない利用者がいる」場合に絞り、頻繁に変わる業務規則はアプリ側に残す判断を勧めます。置くと決めたらリポジトリとマイグレーションに載せることが条件です。
ストアドプロシージャとは何かをSQLを個別に送る方式と比べて整理する
アプリがSQLを一本ずつ送る方式では、文ごとにネットワークの往復が発生します。ストアドプロシージャは、その一連の文と条件分岐・繰り返し・例外処理をデータベース側に登録しておき、呼び出し一回で中身を全部実行させる方式です。
定義と呼び出しと引数の三要素とストアドファンクションとの違い
構成要素は三つです。CREATE PROCEDUREで本体を登録する定義、CALLやEXECで実行する呼び出し、そして値を受け渡す引数です。引数には入力用のIN、結果を返すOUT、両方を兼ねるINOUTがあり、MySQLでは省略時がINになります。
似た仕組みにストアドファンクションがあります。ファンクションは値を一つ返し、SELECTの中に式として書ける一方、MySQLでは明示的にも暗黙的にもコミットを伴う文を含められません。プロシージャは単独の文としてしか呼べない代わりに、トランザクションの開始と確定を本体の中に書けます。区切りのある更新処理はプロシージャ、値の計算はファンクションという分担が基本形です。
事前コンパイルで速いという説明が現行の実装では当たらない理由
古い解説には「作成時にコンパイルされるので速い」と書かれていることがあります。Microsoft LearnのCREATE PROCEDUREは、作成時に行うのは構文チェックだけで、最初の実行時にコンパイルしてプランキャッシュに残すと説明しています。存在しないテーブルを参照するプロシージャも作成自体は成功する構図です。
PostgreSQLのPL/pgSQLの内部実装の解説も、各セッションで初めて呼ばれた時点で本体を解析し、文は初回に実行された時点で準備すると書いています。MySQL 8.4系のリファレンスマニュアルも、ストアドプログラムのキャッシュをセッション単位で持つと明記しています。アプリから送るプリペアドステートメントにも計画を使い回す仕組みがあるため、コンパイルの有無は速さの決め手になりません。
MySQLでストアドプロシージャを作成してCALLで実行する具体的な手順
ここからは在庫の引き当てを題材に、MySQL 8.4系で動く定義と呼び出しを示します。MySQL自体の版選びはMySQLとは？特徴とバージョン選定・採用判断を実装目線で解説【2026年版】を参照してください。
DELIMITERを切り替えてIN引数とOUT引数を持つ本体を定義する
mysqlクライアントは;で文の終わりを判断するため、本体の途中で送信されないよう区切り文字を一時的に変えます。以下は不足時にエラーを投げ、失敗したら巻き戻す引き当て処理です。
CREATE TABLE stock (item_id INT PRIMARY KEY, qty INT NOT NULL);
INSERT INTO stock VALUES (101, 10);
DELIMITER //
CREATE PROCEDURE reserve_stock(
IN p_item_id INT,
IN p_qty INT,
OUT p_left INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
SELECT qty INTO p_left FROM stock
WHERE item_id = p_item_id FOR UPDATE;
IF p_left IS NULL OR p_left < p_qty THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'stock shortage';
END IF;
UPDATE stock SET qty = qty - p_qty WHERE item_id = p_item_id;
SET p_left = p_left - p_qty;
COMMIT;
END//
DELIMITER ;
CALL reserve_stock(101, 3, @left);
SELECT @left; -- 7FOR UPDATEで対象行に排他ロックを掛けてから残数を読むため、同時に二つの引き当てが走っても片方は待たされます。OUT引数は本体の開始時点でNULLなので、該当行が無いときも不足と同じ扱いでエラーになる書き方です。
PythonのMySQL Connectorでのcallproc呼び出しと残数の取得
アプリからはCALL文を文字列で送るより、ドライバの専用メソッドを使うほうが引数の受け渡しが安全です。MySQL Connector/Pythonのcallprocは、渡した引数列のコピーを返し、OUTとINOUTの位置だけを結果の値で置き換えます。
import mysql.connector
conn = mysql.connector.connect(
host="127.0.0.1", user="app", password="secret", database="shop")
cur = conn.cursor()
try:
result = cur.callproc("reserve_stock", (101, 3, 0))
print("残数:", result[2])
except mysql.connector.Error as e:
print("引き当て失敗:", e.msg)
finally:
cur.close()
conn.close()三番目の引数に置いた0は、OUT引数の受け皿としての仮の値です。プロシージャ内でSELECTした結果セットはstored_results()で別に取り出します。トランザクションはプロシージャ側で確定させているため、アプリ側ではcommit()を呼んでいません。
SQL ServerとPostgreSQLとOracleで同じ処理を書くときの構文差
同じ引き当て処理でも、方言ごとに書き方が変わります。構文の差よりも気付きにくいのが実行権限の既定値の差で、移植のときに権限エラーや権限の過剰付与として表に出ます。
T-SQLはEXECで呼びSET NOCOUNT ONとTRY CATCHを冒頭に置く
SQL ServerではCREATE OR ALTERが使えるのは2016 SP1以降です。Microsoft Learnはベストプラクティスとして、本体の最初にSET NOCOUNT ONを置くこと、オブジェクトをスキーマ名付きで参照すること、エラー処理にTRY...CATCHを使うことを挙げています。名前にsp_接頭辞を付けるとシステムプロシージャと衝突するおそれがあるため避けます。
CREATE OR ALTER PROCEDURE dbo.reserve_stock
@item_id INT, @qty INT, @left INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
SELECT @left = qty FROM dbo.stock WITH (UPDLOCK, ROWLOCK)
WHERE item_id = @item_id;
IF @left IS NULL OR @left < @qty
BEGIN
THROW 50001, N'stock shortage', 1;
END
UPDATE dbo.stock SET qty = qty - @qty WHERE item_id = @item_id;
SET @left = @left - @qty;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH
END;
GO
DECLARE @left INT;
EXEC dbo.reserve_stock @item_id = 101, @qty = 3, @left = @left OUTPUT;
SELECT @left AS left_qty;呼び出し側でもOUTPUTを付け忘れると値が戻りません。入れ子呼び出しは32レベルまでという上限も公式に記載があり、プロシージャから別のプロシージャを呼ぶ設計では深さに気を配ります。
PostgreSQLは11以降のCALLで呼び出し関数との使い分けが前提になる
PostgreSQLのプロシージャは11.0のリリースノートで追加された機能で、それ以前は関数で代用していました。CREATE PROCEDUREの公式文書によると、既定は呼び出し側の権限で動くSECURITY INVOKERです。
CREATE OR REPLACE PROCEDURE reserve_stock(
p_item_id int, p_qty int, OUT p_left int)
LANGUAGE plpgsql AS $$
BEGIN
SELECT qty INTO p_left FROM stock
WHERE item_id = p_item_id FOR UPDATE;
IF p_left IS NULL OR p_left < p_qty THEN
RAISE EXCEPTION 'stock shortage';
END IF;
UPDATE stock SET qty = qty - p_qty WHERE item_id = p_item_id;
p_left := p_left - p_qty;
END;
$$;
CALL reserve_stock(101, 3, NULL);OUT引数の位置にNULLを置いて呼ぶ点が他の方言と違います。関数とプロシージャの境目、例外処理の費用、Oracleからの書き換え規則はPL/pgSQLとは？関数とプロシージャの違いと例外処理・カーソルを実装目線で解説で掘り下げています。
実行権限の既定値がDBMSごとに違う点を比較表で確かめておく
OracleはAUTHID句を省略すると定義者権限になるとPL/SQLの言語リファレンスに書かれています。MySQLのSQL SECURITYも既定はDEFINERです。四つの方言を並べると次のとおりです。
| DBMS | 呼び出し | 権限の既定 |
|---|---|---|
| MySQL 8.4系 | CALL | 定義者（DEFINER） |
| SQL Server | EXEC | 呼び出し側（CALLER） |
| PostgreSQL 18系 | CALL | 呼び出し側（INVOKER） |
| Oracle | EXEC か CALL | 定義者（DEFINER） |
Oracleの最新の構文はCREATE PROCEDURE文のリファレンスで確認できます。MySQLからPostgreSQLへ移すと、定義者権限で通っていた処理が呼び出し側の権限不足で止まる、という事故がこの表の差から起きます。
性能とロックの効き方は往復の削減とトランザクション範囲で決まる
ストアドプロシージャを入れて速くなるかどうかは、処理の形でほぼ決まります。速くならない処理に入れると、保守の負担だけが増える結果になります。
通信の往復が多く結果が入力より小さい処理で得られる速度改善の条件
効果が出るのは、アプリとデータベースの間を何度も行き来していた処理です。百件を一件ずつ更新すれば百往復かかりますが、プロシージャにまとめれば一往復で済みます。大量の行を読んで集計値だけを返す処理も、転送量が減る分だけ速くなります。
反対に、結果を全件そのまま返す処理や、外部APIの応答を待つ処理は、置き場所を変えても往復が減りません。アプリ側の遅さの原因がORMの発行クエリ数にあるなら、まずN+1問題とは？発行クエリ数の実測と検出方法・ORM別の対策の手順で数を測り、ORMの書き方で直るかを確かめるほうが先です。
本体の中のトランザクションを短く保たないとロック待ちが伸びる
プロシージャは一回の呼び出しで多くの行に触れるため、ロックを長く保持しやすい性質があります。Microsoft Learnも、明示的なトランザクションはできるだけ短くし、長いほどロックの保持が延びてデッドロックの可能性が高まると書いています。
トランザクションの範囲はDBMSで扱いが違います。MySQLとSQL Serverの既定の自動コミットでは、本体にSTART TRANSACTIONやBEGIN TRANSACTIONを書かない限り文ごとに確定します。PostgreSQLではCALL一回が一つのトランザクションとして動き、本体でCOMMITを発行したときだけ途中で区切られる仕組みです。複数のプロシージャが同じ行を逆順で更新するとデッドロックになるため、更新順の統一はデッドロックとは？DBトランザクションで起きる原因・例と解消方法の手順で、行ロックの取り方は楽観ロックと悲観ロックの違いと使い分けで決めてください。
実行計画の使い回しが裏目に出るパラメータスニッフィングへの備え
SQL Serverは初回実行時の引数の値で計画を作り、以降の呼び出しでもその計画を使います。初回の値が偏っていると、別の値で呼ばれたときに極端に遅い計画が使われ続ける現象が問題です。WITH RECOMPILEを付ければ毎回コンパイルされますが、コンパイルの費用が呼び出しごとに乗るため、遅い文に限ってクエリヒントで再コンパイルさせる方法も選べます。
PostgreSQLも、何度も実行される文については値に依存しない汎用の計画を作って使い回すかどうかを内部で判断します。どちらのDBMSでも、日によって同じプロシージャの所要時間が大きくぶれるときは、計画の使い回しを最初に疑う順番で調べると原因に早く届きます。
セキュリティと権限設計｜EXECUTE権限だけを渡す構成と動的SQLの罠
権限の面で得られる利点は、テーブルに直接触らせずに決まった操作だけを許可できることです。ただし設定の既定値と動的SQLの扱いを誤ると、利点が丸ごと消えます。
テーブル権限を渡さずEXECUTEだけを許可する最小権限の書き方
MySQLは定義者権限が既定なので、アプリ用のロールにはプロシージャのEXECUTEだけを付与すれば足ります。テーブルへのUPDATE権限を渡さないため、アプリの接続情報が漏れても任意の行を書き換えられる経路が閉じます。
-- MySQL 8.4系: アプリ用ロールには実行権限だけを渡す
CREATE ROLE app_role;
GRANT EXECUTE ON PROCEDURE shop.reserve_stock TO app_role;
-- stock テーブルへの SELECT・UPDATE は付与しない
-- PostgreSQL 18系: 定義者権限は明示し、検索パスも固定する
ALTER PROCEDURE reserve_stock(int, int, int)
SECURITY DEFINER SET search_path = public;
REVOKE EXECUTE ON PROCEDURE reserve_stock(int, int, int) FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE reserve_stock(int, int, int) TO app_role;PostgreSQLで定義者権限に切り替える場合は、検索パスを固定しないと同名の偽オブジェクトを先に解決させる攻撃の余地が残ります。また公式文書のとおり、SECURITY DEFINERを付けたプロシージャではCOMMITなどのトランザクション制御文を実行できません。途中で区切る処理と定義者権限は両立しない点に注意してください。
本体での文字列連結による動的SQLの組み立てで注入を防げない条件
OWASPのSQL Injection Prevention Cheat Sheetは、安全に実装されたストアドプロシージャはパラメータ化クエリと同等の効果を持つとしたうえで、その条件を危険な動的SQLを含まないことと定めています。並び替え列やテーブル名を引数から受け取って文字列連結で文を組めば、守りはそこで崩れます。
動的SQLが避けられない場合は、受け取った値を許可リストと照合してから組み立ててください。同じ文書は、プロシージャを動かすためにアプリを強い権限で接続させる構成が、侵害時の被害を広げるとも指摘しています。攻撃の仕組みそのものはSQLインジェクションとは？仕組み・攻撃例・対策をコード付きで解説で扱っています。
アプリ側実装と比べた保守性とストアドプロシージャを採用する条件
書けることと置くべきことは別の問題です。往復が減る利点だけで決めると、テストもレビューも通らない業務ロジックがデータベースに積み上がります。
採用条件と見送る場面を処理の性質ごとに表で切り分けて判断する
判断の軸は、処理の性質と変更頻度です。ORMとは？O/Rマッパーの仕組み・メリットとデメリットをSQL比較と実行結果で解説で扱うアプリ側の実装と比べ、どちらに置くかを次の表で決めます。
| 処理の性質 | 置き場所 | 理由 |
|---|---|---|
| 大量に読み少量を返す集計 | プロシージャ | 転送量が減る |
| 複数文の一括更新 | プロシージャ | 往復とロック時間が減る |
| 経路を問わない整合性の担保 | プロシージャ | どこから呼ばれても効く |
| 頻繁に変わる業務規則 | アプリ側 | 改修と検証が速い |
| 外部APIとの連携 | アプリ側 | 待ち時間が接続を占有する |
| 将来DBMSを替える予定 | アプリ側 | 方言の書き換えが不要 |
見送るべきなのは、テストとレビューの体制が無い現場です。定義がリポジトリに無く、本番で直接書き換えられるプロシージャは、書いた人が離れた時点で誰も直せない資産になります。
バージョン管理とテストに載せる運用をマイグレーションで固定する
プロシージャの定義はアプリのコードと同じくリポジトリに入れ、投入をマイグレーションツールに任せます。Flywayの繰り返し適用型マイグレーションは、ファイルの内容が変わったときだけ再適用されるため、定義を一ファイル一プロシージャで管理する用途に向きます。
db/migration/
V1__create_stock.sql
V2__seed_stock.sql
R__reserve_stock.sql -- CREATE OR REPLACE で定義を丸ごと書くMySQL 8.4系の構文にはCREATE OR REPLACE PROCEDUREが無いため、繰り返し適用型のファイルではDROP PROCEDURE IF EXISTSを先頭に置きます。命名規則と設定の詳細はFlywayとは？Spring Bootのマイグレーション設定と命名規則・Spring Boot 4の変更点【2026年8月】を参照してください。テストは、テスト用データベースにマイグレーションを流したあと、プロシージャを呼んで戻り値とテーブルの状態を確かめる結合テストで組むのが現実的です。
受託開発でストアドプロシージャ資産を設計・移行するときの進め方
既存システムの改修や移行の案件では、ストアドプロシージャが見積もりのぶれの最大要因になりがちです。本数と行数は数えられても、どのアプリのどの画面から呼ばれているか、権限の既定値の差で挙動が変わらないか、までを洗い出さないと工数が読めません。
任せる判断が向くのは二つの局面です。ひとつは、OracleやSQL Serverから別のDBMSへ移す局面で、書き換え対象の棚卸しと権限の付け替えに経験が要ります。移行工程の全体像はOracleからPostgreSQLへの移行手順｜互換性差分とora2pg・DMSの使い分け、移行可否の判断基準にまとめました。もうひとつは、アプリ側とデータベース側の線引きを引き直す設計段階です。当社のデータベース設計・移行支援では、既存のプロシージャを読み解いたうえで、残す範囲・アプリへ移す範囲・権限の設計を分けて提示しています。
よくある質問
ストアドプロシージャとストアドファンクションはどう使い分けますか？
途中でトランザクションを確定させる更新処理はプロシージャ、値を計算して返す処理はファンクションに分けます。ファンクションはSELECTの中に式として書ける代わりに、MySQLではコミットを伴う文を含められません。
ストアドプロシージャを使うと必ず速くなりますか？
なりません。速くなるのは、アプリとの往復が多い処理と、大量に読んで少量を返す処理です。結果を全件返す処理は置き場所を変えても転送量が同じため、効果はほぼ出ません。事前コンパイルによる高速化も、現行の主要DBMSでは決め手になりません。
ストアドプロシージャを使えばSQLインジェクションは防げますか？
本体の中で文字列を連結して動的SQLを組まない限り、パラメータ化クエリと同等に防げます。並び替え列やテーブル名を引数で受け取って連結する書き方をすると防げないため、許可リストとの照合が必要になります。
MySQLで作成するとき構文エラーになるのはなぜですか？
多くはmysqlクライアントの区切り文字が原因です。本体の途中の;で文が送信されてしまうため、DELIMITERで一時的に別の記号へ変えてから定義し、終わったら戻します。GUIツールやドライバ経由で定義を送る場合は、この切り替えは不要です。
既存のストアドプロシージャをアプリ側へ移すべきか迷っています。
変更頻度が高い業務規則と、テストが無いまま本番で書き換えられているものから移す順番を勧めます。集計や整合性の担保のようにデータベース側で効いている処理は残し、移す前に呼び出し元の一覧と権限の既定値の差を洗い出してください。
関連記事
- PL/pgSQLとは？関数とプロシージャの違いと例外処理・カーソルを実装目線で解説（PostgreSQL固有の手続き言語）
- SQLとは？文の種類（DDL・DML・DCL）と実行順序・RDBMS方言を実装目線で解説（プロシージャの中に書くSQLの基礎）
- デッドロックとは？DBトランザクションで起きる原因・例と解消方法（更新順とロック待ちの設計）
- ORMとは？O/Rマッパーの仕組み・メリットとデメリットをSQL比較と実行結果で解説（アプリ側に処理を置く場合の選択肢）
- OracleからPostgreSQLへの移行手順｜互換性差分とora2pg・DMSの使い分け、移行可否の判断基準（プロシージャ資産の移行工程）
