SQLのストアドプロシージャ（STORED PROCEDURE）を完全ガイド！初心者でもわかるデータベースの自動処理
新人
「データベースで、同じ処理を何度も実行しないといけない時に、毎回SQLを書くのは面倒ですね。」
先輩
「そんな時に便利なのがストアドプロシージャ（STORED PROCEDURE）だよ！」
新人
「ストアドプロシージャって何ですか？」
先輩
「ストアドプロシージャは、SQLの処理をあらかじめデータベースに保存しておき、必要なときに簡単に呼び出せる機能なんだ。実際に見てみよう！」
1. ストアドプロシージャとは？
ストアドプロシージャ（STORED PROCEDURE）とは、複数のSQL文をまとめてデータベースに保存し、後から簡単に呼び出せる仕組みです。
ストアドプロシージャのメリット
- 再利用可能 - 一度作成すれば、何度でも呼び出せる
- 処理速度が向上 - 毎回SQLを送信する必要がなく、サーバー側で直接実行できる
- セキュリティの向上 - 直接SQLを実行しないため、SQLインジェクション対策にもなる
ストアドプロシージャの主な用途
- 特定のデータを取得・更新する処理
- 条件分岐を使った処理
- バッチ処理や定期的なデータ処理
では、基本的なストアドプロシージャの作成方法を見ていきましょう！
2. ストアドプロシージャの作成（CREATE PROCEDURE）の基本
ストアドプロシージャを作成するには、CREATE PROCEDURE 文を使用します。
1. シンプルなストアドプロシージャの作成
例えば、employeesテーブルの全データを取得するストアドプロシージャを作成してみましょう。
DELIMITER //
CREATE PROCEDURE get_all_employees()
BEGIN
SELECT * FROM employees;
END //
DELIMITER ;
ポイント:
DELIMITER //- ストアドプロシージャの区切りを設定CREATE PROCEDURE get_all_employees()-get_all_employeesという名前のプロシージャを作成BEGIN ... END- 実行するSQL文をまとめるDELIMITER ;- 通常のSQLの区切りに戻す
2. ストアドプロシージャの実行
作成したストアドプロシージャは、次のSQLで実行できます。
CALL get_all_employees();
このSQLを実行すると、employeesテーブルの全データが取得されます。
3. 実行結果
| employee_id | name | age | department | salary |
|---|---|---|---|---|
| 1 | 田中 太郎 | 30 | 営業 | 5000000 |
| 2 | 佐藤 花子 | 25 | マーケティング | 4200000 |
| 3 | 鈴木 一郎 | 35 | 開発 | 6000000 |
このように、ストアドプロシージャを使うことで、複雑なSQLを簡単に実行できます。
次のセクションでは、パラメータを使用したストアドプロシージャの作成について解説します。
4. パラメータを使用したストアドプロシージャ（引数の活用）
ストアドプロシージャには、パラメータ（引数）を指定することができます。これにより、動的なデータ取得や処理が可能になります。
1. 指定した社員の情報を取得するストアドプロシージャ
以下のストアドプロシージャは、employee_idを引数として受け取り、指定した社員の情報を取得します。
DELIMITER //
CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
SELECT * FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;
2. ストアドプロシージャの実行
作成したストアドプロシージャを実行するには、CALL文を使います。
CALL get_employee_by_id(1);
3. 実行結果
| employee_id | name | age | department | salary |
|---|---|---|---|---|
| 1 | 田中 太郎 | 30 | 営業 | 5000000 |
このように、ストアドプロシージャにパラメータを渡すことで、特定のデータのみを取得することができます。
5. 条件分岐（IF, CASE）を使った処理
ストアドプロシージャでは、IF文やCASE文を使って、条件分岐を行うことができます。
1. IF文を使ったストアドプロシージャ
以下のストアドプロシージャは、指定した社員の給与が500万円以上かどうかを判定します。
DELIMITER //
CREATE PROCEDURE check_salary(IN emp_id INT)
BEGIN
DECLARE emp_salary INT;
SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
IF emp_salary >= 5000000 THEN
SELECT '給与は500万円以上です' AS result;
ELSE
SELECT '給与は500万円未満です' AS result;
END IF;
END //
DELIMITER ;
2. CASE文を使ったストアドプロシージャ
CASE文を使用して、社員の年齢に応じたカテゴリを返すストアドプロシージャを作成します。
DELIMITER //
CREATE PROCEDURE get_age_category(IN emp_id INT)
BEGIN
DECLARE emp_age INT;
DECLARE category VARCHAR(50);
SELECT age INTO emp_age FROM employees WHERE employee_id = emp_id;
SET category = CASE
WHEN emp_age < 25 THEN '若手'
WHEN emp_age BETWEEN 25 AND 40 THEN '中堅'
ELSE 'ベテラン'
END;
SELECT category AS 年齢カテゴリ;
END //
DELIMITER ;
このように、IF文やCASE文を使用すると、条件に応じた動的な処理が可能になります。
6. ループ（WHILE, LOOP）を使った処理
ストアドプロシージャでは、繰り返し処理を行うためにWHILEやLOOPを使用できます。
1. WHILEを使ったループ処理
以下のストアドプロシージャは、1から指定した数値までの合計を計算します。
DELIMITER //
CREATE PROCEDURE sum_numbers(IN max_num INT, OUT total_sum INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET total_sum = 0;
WHILE i <= max_num DO
SET total_sum = total_sum + i;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
2. LOOPを使ったループ処理
LOOPを使ったカウント処理の例です。
DELIMITER //
CREATE PROCEDURE loop_example(IN max_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
loop_label: LOOP
SELECT CONCAT('現在のカウント: ', i) AS message;
SET i = i + 1;
IF i > max_count THEN
LEAVE loop_label;
END IF;
END LOOP;
END //
DELIMITER ;
このように、WHILEやLOOPを使うことで、繰り返し処理を実装できます。
次のセクションでは、ストアドプロシージャの削除・変更やトリガーとの違いについて解説します。
7. ストアドプロシージャの削除・変更（DROP PROCEDURE, ALTER PROCEDURE）
ストアドプロシージャを削除または変更する方法を解説します。
1. ストアドプロシージャの削除（DROP PROCEDURE）
不要になったストアドプロシージャはDROP PROCEDUREで削除できます。
DROP PROCEDURE IF EXISTS get_all_employees;
このSQLを実行すると、get_all_employeesプロシージャが削除されます。
2. ストアドプロシージャの変更（再作成）
ストアドプロシージャはALTER PROCEDUREで直接変更できません。そのため、一度削除してから再作成する必要があります。
DROP PROCEDURE IF EXISTS get_employee_by_id;
DELIMITER //
CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
SELECT name, age, department FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;
このように、DROP PROCEDUREを実行してから、新しい内容でプロシージャを作成します。
8. ストアドプロシージャとトリガーの違い（どちらを使うべきか）
ストアドプロシージャとトリガーはどちらもデータベースの自動処理に利用されますが、用途が異なります。
1. ストアドプロシージャとトリガーの比較
| 機能 | ストアドプロシージャ（STORED PROCEDURE） | トリガー（TRIGGER） |
|---|---|---|
| 実行タイミング | 手動で呼び出す（CALL文） |
データ変更時（INSERT, UPDATE, DELETE） |
| 用途 | 複雑な処理や計算 | データ変更時の監査ログや自動補完 |
| パフォーマンス | 必要なときに実行できるため負荷を抑えやすい | 変更のたびに実行されるため、多用すると処理負荷がかかる |
2. どちらを使うべきか？
- 特定の処理を必要なタイミングで実行 → ストアドプロシージャ
- データの変更時に自動的に処理を実行 → トリガー
たとえば、売上データの集計処理はストアドプロシージャ、削除前にバックアップを取る処理はトリガーが適しています。
9. 実践！ストアドプロシージャを活用したデータ処理の最適化
ストアドプロシージャを活用してデータ処理を最適化する方法を紹介します。
1. ユーザー情報をバッチ更新するストアドプロシージャ
全ユーザーのステータスを一括で更新するストアドプロシージャを作成します。
DELIMITER //
CREATE PROCEDURE update_user_status(IN new_status VARCHAR(20))
BEGIN
UPDATE users SET status = new_status WHERE last_login < NOW() - INTERVAL 6 MONTH;
END //
DELIMITER ;
このストアドプロシージャを実行すると、last_loginが6か月以上前のユーザーのステータスが変更されます。
CALL update_user_status('inactive');
2. 売上集計を行うストアドプロシージャ
売上データを集計し、日ごとの売上を計算するストアドプロシージャを作成します。
DELIMITER //
CREATE PROCEDURE daily_sales_summary()
BEGIN
INSERT INTO sales_summary (date, total_sales)
SELECT order_date, SUM(amount) FROM orders
GROUP BY order_date;
END //
DELIMITER ;
このストアドプロシージャを実行すると、売上データを日ごとに集計し、sales_summaryテーブルに記録します。
CALL daily_sales_summary();
このように、ストアドプロシージャを活用すると、データの更新や集計を自動化し、業務を効率化できます。
