ストアドプロシージャとは？３分でわかりやすく解説

ストアドプロシージャ（STORED PROCEDURE）とは複数のSQL文を１つのプログラムのような形でまとめ、プログラムの関数のように利用することができる仕組みです。

データベースに保存されるという点が特徴で、アプリケーションで使用される一般的なタスクを実行するために定義されます。

-- ストアドプロシージャの定義
DELIMITER //  -- デリミタを変更することで、複数行のコマンドを作成できるようになります
CREATE PROCEDURE ストアドプロシージャ名 ([IN/OUT]パラメータ データ型,...)
BEGIN
  -- SQLステートメント1
  -- SQLステートメント2
  ...
END //
DELIMITER ;  -- デリミタを元に戻す


-- ストアドプロシージャの呼び出し
call ストアドプロシージャ名(引数);

ストアドプロシージャの作成方法と構文ルールは使用するDBMSによって異なります。この記事では、MySQLを例に説明していきます。

このページで学べる内容

ストアドプロシージャとは？
ストアドプロシージャの定義方法
ストアドプロシージャの実行方法
ストアドプロシージャのメリット・デメリット

プログラマーやエンジニアを目指す方であれば知らないと恥ずかしい超・基本知識の１つです。是非最後までご覧ください。

ストアドプロシージャとは？
1. ストアドプロシージャ：メリット
2. ストアドプロシージャ：デメリット
ストアドプロシージャ：サンプル【MySQL】
SQLを１から学習したい方は

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

ストアドプロシージャ（STORED PROCEDURE）とは複数のSQL文を１つのプログラムのような形でまとめ、プログラムの関数のように利用することができる仕組みのこと。

データベースに保存されるという点が特徴で、アプリケーションで使用される一般的なタスクを実行するために定義されます。

例えば定期的に実行する必要がある複数のSQLをストアドプロシージャとして定義しておけば、定期的に複数のSQLを投げる必要はなくなり、ストアドプロシージャを呼び出せばOKとなります。

ストアドプロシージャ：メリット

ストアドプロシージャを利用するメリットは以下の通り。

メリット

再利用性：ストアドプロシージャはデータベースに保存されるため、アプリケーションやユーザー間で再利用が可能。複数の異なるプログラムから呼び出して利用する仕組みにすることで、開発工数削減などの効果が期待できます。
セキュリティの向上：ストアドプロシージャを使用することで、テーブルへの直接アクセスを制限し、特定の操作のみを許可することができます。これにより、データベースのセキュリティが向上します。
パフォーマンス：ストアドプロシージャはデータベースサーバーで実行されるため、ネットワークトラフィックを削減し、応答時間を短縮することができます。また、ストアドプロシージャはコンパイル済みのため、実行速度が速いことがあります。
一貫性：データベース操作の一貫性が向上します。データ操作や検証ルールをストアドプロシージャに統一して定義することで、アプリケーション間でデータの整合性を維持しやすくなります。

参考　ACID特性の基本

ストアドプロシージャ：デメリット

ストアドプロシージャにはいくつかの欠点も存在します。

デメリット

可搬性（移植性）：ストアドプロシージャはDBMS（Oracle/Postgres/MySQL・・・）ごとに異なる言語や構文を使用するため、データベースシステムを変更する際に移行が困難になる場合があります。
複雑性：ストアドプロシージャは、データベースの機能に依存した複雑なロジックを含むことがあります。これにより、アプリケーションのコードとデータベースのロジックが密結合になり、保守性が低下することがあります。
デバッグとトラブルシューティング：ストアドプロシージャはデータベースサーバーで実行されるため、デバッグやトラブルシューティングがアプリケーションコードに比べて困難になります。
バージョン管理：アプリケーションコードとは異なり、ストアドプロシージャのバージョン管理が難しくなることがあります。

プログラム全体の効率性や堅牢度で見ればストアドプロシージャの利用はおすすめですが、デメリットをザックリいえばその分扱いが難しくなるという感じです。

ここからはより具体的にストアドプロシージャをイメージできるように実際の構文ルールやサンプルコードを見ていきましょう。

ストアドプロシージャ：サンプル【MySQL】

ここでは、MySQLを例にストアドプロシージャの定義と実行方法を解説します。

-- ストアドプロシージャの定義
DELIMITER //  -- デリミタを変更することで、複数行のコマンドを作成できるようになります
CREATE PROCEDURE ストアドプロシージャ名 ([IN/OUT]パラメータ データ型,...)
BEGIN
  -- SQLステートメント1
  -- SQLステートメント2
  ...
END //
DELIMITER ;  -- デリミタを元に戻す


-- ストアドプロシージャの呼び出し
call ストアドプロシージャ名(引数);

実際に以下の従業員テーブル（employees）に対して特定の部門（department_id）の従業員を検索（=SELECT）するだけの簡単なストアドプロシージャを作成してみます。

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department_id INT,
  salary DECIMAL(10, 2)
);

参考：CREATE TABLE文

まずは、ストアドプロシージャの定義をします。

-- p_department_id を引数にするストアドプロシージャの定義
DELIMITER //
CREATE PROCEDURE GetEmployeesByDepartment(IN p_department_id INT)
BEGIN
  SELECT id, first_name, last_name, salary
  FROM employees
  WHERE department_id = p_department_id;
END //
DELIMITER ;

このストアドプロシージャは、"p_department_id" という名前の入力パラメータを受け取り、その部門IDに該当する従業員を検索する処理を実行します。

上記のストアドプロシージャを実行します。引数には「1」を指定します。

CALL GetEmployeesByDepartment(1);

これで部門IDが「1」の従業員の一覧が取得されます。

今回の例ではストアドプロシージャの処理としてはSELECT文を１つだけ定義していますが、実際には以下のように複数のSQL文を記述して利用するのが一般的です。

DELIMITER //
CREATE PROCEDURE UpdateSalaryByDepartment(IN p_department_id INT, IN p_percentage FLOAT)
BEGIN
  -- 給与を一定の割合で増減する
  UPDATE employees
  SET salary = salary * (1 + p_percentage / 100)
  WHERE department_id = p_department_id;

  -- 更新された従業員の一覧を表示する
  SELECT id, first_name, last_name, department_id, salary
  FROM employees
  WHERE department_id = p_department_id;
END //
DELIMITER ;


-- ストアドプロシージャの実行
CALL UpdateSalaryByDepartment(1, 10);