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

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

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

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


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

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

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

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

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

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

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

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

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

参考　ACID特性の基本

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

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

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

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

ストアドプロシージャ：サンプル【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);

このストアドプロシージャは、"p_department_id" と "p_percentage" という2つの入力パラメータを受け取り、指定された部門IDの従業員の給与を指定された割合だけ増減させます。

引数で「1」と「10」を渡しているので、このストアドプロシージャを実行すると、部門IDが1の従業員の給与が10%増加し、更新された従業員の一覧が表示されることになります。

SQLを１から学習したい方は

SQLやデータベースの仕組みを１から学習したい方（学び直したい方）向けに、現役エンジニア達のスキルを結集して 完全無料 のSQL教材を作成しました。

SQLは決して難しい技術ではないので、エンジニアであれば「当たり前のように」扱えて当然かも・・・？

とはいえ、案外SQLをちゃんと使ったことがない人も多いはずです。この機会に是非一度ご覧になってみてください。

【完全無料】SQL：初心者向け学習ロードマップ

SQL（Structured Query Language）：初心者向けにわかりやすく３０記事でSQLをマスターするための記事を整理。IT初心者でもこの記事を読めば必ずSQLを利用することができるようになります！

it-biz.online

2023.09.22