MySQLのストアドプロシージャと関数に関する詳細な解説
ストアドプロシージャとは、あらかじめコンパイルされたSQL文の集合であり、一度作成すれば何度でも呼び出すことができます。
このレッスンでは、ストアドプロシージャと関数の作成および使用方法について解説します。
graph TB
A[CALL Stored Procedures] --> B[Parameter Passing IN/OUT/INOUT]
B --> C[DECLARE Declare a variable]
C --> D{Process Control}
D --> E[IF / CASE Conditions]
D --> F[WHILE / LOOP Loop]
D --> G[Cursor CURSOR Iterate]
E --> H[ExecuteSQL]
F --> H
G --> H
H --> I[OUTParameter Return]
I --> J[End]
1. 学習内容
- CREATE PROCEDURE：ストアドプロシージャを作成する
- IN/OUT/INOUT パラメータの種類
- DECLARE：変数の宣言
- IF/CASE/WHILE による制御構造
- カーソル CURSOR
2. 実際の事例
(1) 課題：複雑なロジックを繰り返し記述すること
毎月、各部署の給与総額を計算していますが、SQLクエリが非常に長く、繰り返し実行されています。
(2) ストアドプロシージャを用いた解決策
SQL
CREATE PROCEDURE sp_dept_salary_stats()
BEGIN
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
END;
-- Call
CALL sp_dept_salary_stats();
3. ストアドプロシージャの作成
▶ 例：基本的なストアドプロシージャ
SQL
-- Create a Stored Procedure
DELIMITER //
CREATE PROCEDURE sp_get_user(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;
-- Call
CALL sp_get_user(1);
▶ 例：OUTパラメータを使用する場合
SQL
DELIMITER //
CREATE PROCEDURE sp_count_users(OUT total INT)
BEGIN
SELECT COUNT(*) INTO total FROM users;
END //
DELIMITER ;
-- Call
CALL sp_count_users(@total);
SELECT @total;
▶ 例：INOUTパラメータを使用する場合
SQL
DELIMITER //
CREATE PROCEDURE sp_double(INOUT num INT)
BEGIN
SET num = num * 2;
END //
DELIMITER ;
-- Call
SET @num = 5;
CALL sp_double(@num);
SELECT @num; -- 10
4. 変数の宣言
▶ 例：DECLARE 変数
SQL
DELIMITER //
CREATE PROCEDURE sp_variable_demo()
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE avg_salary DECIMAL(10,2);
DECLARE user_name VARCHAR(50);
-- Assignment
SELECT COUNT(*) INTO total FROM employees;
SELECT AVG(salary) INTO avg_salary FROM employees;
-- Using Variables
SELECT total, avg_salary;
END //
DELIMITER ;
5. プロセス制御
▶ 例：IF文
SQL
DELIMITER //
CREATE PROCEDURE sp_check_salary(IN emp_id INT)
BEGIN
DECLARE emp_salary DECIMAL(10,2);
SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;
IF emp_salary > 10000 THEN
SELECT 'High salary' AS level;
ELSEIF emp_salary > 5000 THEN
SELECT 'Medium salary' AS level;
ELSE
SELECT 'Low salary' AS level;
END IF;
END //
DELIMITER ;
▶ 例：CASE文
SQL
DELIMITER //
CREATE PROCEDURE sp_order_status(IN order_id INT)
BEGIN
DECLARE order_status VARCHAR(20);
SELECT status INTO order_status FROM orders WHERE id = order_id;
CASE order_status
WHEN 'pending' THEN SELECT 'Order is pending' AS message;
WHEN 'paid' THEN SELECT 'Order is paid' AS message;
WHEN 'shipped' THEN SELECT 'Order is shipped' AS message;
ELSE SELECT 'Unknown status' AS message;
END CASE;
END //
DELIMITER ;
▶ 例：WHILEループ
SQL
DELIMITER //
CREATE PROCEDURE sp_sum_1_to_n(IN n INT, OUT total INT)
BEGIN
DECLARE i INT DEFAULT 1;
SET total = 0;
WHILE i <= n DO
SET total = total + i;
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- Call
CALL sp_sum_1_to_n(100, @total);
SELECT @total; -- 5050
6. カーソル
▶ 例：カーソルの反復処理
SQL
DELIMITER //
CREATE PROCEDURE sp_list_users()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE user_name VARCHAR(50);
DECLARE user_email VARCHAR(100);
-- Declare cursor
DECLARE cur_users CURSOR FOR
SELECT username, email FROM users;
-- End of cursor handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- Open cursor
OPEN cur_users;
-- Iterate
read_loop: LOOP
FETCH cur_users INTO user_name, user_email;
IF done THEN
LEAVE read_loop;
END IF;
SELECT user_name, user_email;
END LOOP;
-- Close cursor
CLOSE cur_users;
END //
DELIMITER ;
7. ストアド関数
▶ 例：関数の作成
SQL
DELIMITER //
CREATE FUNCTION fn_calc_bonus(salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE bonus DECIMAL(10,2);
IF salary > 10000 THEN
SET bonus = salary * 0.2;
ELSEIF salary > 5000 THEN
SET bonus = salary * 0.1;
ELSE
SET bonus = salary * 0.05;
END IF;
RETURN bonus;
END //
DELIMITER ;
-- Using Functions
SELECT name, salary, fn_calc_bonus(salary) AS bonus FROM employees;
8. ストアドプロシージャの管理
▶ 例：表示と削除
SQL
-- View All Stored Procedures
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
-- View the CREATE statement
SHOW CREATE PROCEDURE sp_get_user;
-- Delete a Stored Procedure
DROP PROCEDURE IF EXISTS sp_get_user;
-- Delete Function
DROP FUNCTION IF EXISTS fn_calc_bonus;
❓ よくある質問
Q ストアドプロシージャと関数の違いは何ですか？
A ストアドプロシージャはCALL文を使って呼び出され、値を返しません。一方、関数はSELECT文を使って呼び出され、値を返す必要があります。
Q DELIMITER とは何ですか？
A これはステートメントの終了記号です。ストアドプロシージャに
; が含まれているため、ステートメントが途中で終了してしまうのを防ぐために、これを // に置き換える必要があります。Q ストアドプロシージャはパフォーマンスを向上させることができますか？
A ストアドプロシージャは事前にコンパイルされるため、ネットワークトラフィックが軽減されます。ただし、最新のMySQLオプティマイザはすでにかなり高性能であるため、パフォーマンスの向上は限定的です。
Q ストアドプロシージャと関数の違いは何ですか？
A プロシージャはCALL文を使用して呼び出され、値を返さずにOUTパラメータを持つことができますが、SELECT文で使用される関数は値をRETURNしなければなりません。
Q ストアドプロシージャはSQLインジェクションの脆弱性を持つ可能性がありますか？
A PREPAREとCONCATを組み合わせてSQL文を構築すると、インジェクションが発生する可能性があります。パラメータを直接渡す方が安全です。
📖 まとめ
- ストアドプロシージャは
CREATE PROCEDUREを使用して作成され、CALLを使用して呼び出されます。 - パラメータの種類：IN（入力）、OUT（出力）、INOUT（双方向）
- DECLARE は変数を宣言し、SET は値を代入します
- フロー制御：IF／CASE／WHILE／LOOP
- カーソルは、結果セットを行ごとに順に処理するために使用されます
- 保存関数は値を返さなければならない
📝 練習問題
-
基本問題（難易度：⭐）：ユーザーIDを入力として受け取り、そのユーザーの情報を返すストアドプロシージャを作成してください。
-
上級問題（難易度 ⭐⭐）：スコアに基づいて成績を返す、IF文を含むストアドプロシージャを作成してください。
-
チャレンジ問題（難易度：⭐⭐⭐）：カーソルを使用してユーザーテーブルを順に処理し、各ユーザーのポイントを計算して更新してください。
