MySQL - ストアド関数

こんにちは、データベース愛好家の卵さんたち！今日は、MySQLのストアド関数という魅力的な世界に飛び込んでみましょう。あなたの近所の親切なコンピュータ教師として、この旅をガイドするのが楽しみです。コーヒーやお気に入りの飲み物を片手に、始めましょう！

MySQL - Stored Functions

MySQL ストアド関数とは？

魔法の料理帖を思い浮かべてください。好きな料理をすぐに作れるようなものです。MySQLのストアド関数は、データベースにおけるそんな存在です！特定のタスクをデータベースで実行するために、事前に書かれたレシピ（または関数）を呼び出すことができます。

ストアド関数を使う理由は？

再利用性：一度書いたら何度も使えます！
一貫性：どこにでも同じロジックを適用します。
セキュリティ：誰が関数にアクセスし、修正できるかを制御します。
パフォーマンス：同じコードを繰り返し書くよりも速くなることがあります。

では、袖をまくって、初めてのストアド関数を作成してみましょう！

初めてのストアド関数を作成する

以下は簡単な例です：

DELIMITER //

CREATE FUNCTION HelloWorld()
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
RETURN 'Hello, World!';
END //

DELIMITER ;

これを分解してみましょう：

DELIMITER //：これにより、MySQLはデフォルトのセミコロン（;）ではなく//をステートメントのデリミタとして使用します。関数内にセミコロンがあるため、MySQLが混乱しないようにします。
CREATE FUNCTION HelloWorld()：この行は、名前が"HelloWorld"の関数を作成することを宣言します。
RETURNS VARCHAR(50)：この関数は、最大50文字の文字列（VARCHAR）を返すことを指定します。
DETERMINISTIC：このキーワードは、同じ入力に対して関数が常に同じ出力を返すことをMySQLに伝えます。
BEGINとEND：これらのキーワードは関数の本体をくくります。
RETURN 'Hello, World!';：これが関数の実際のコードです。文字列"Hello, World!"を返します。
DELIMITER ;：デリミタをデフォルトのセミコロンに戻します。

次に、関数を呼び出してみましょう：

SELECT HelloWorld();

すると、"Hello, World!"が結果として表示されます。おめでとうございます！あなたは初めてのストアド関数を作成し、呼び出しました！

ストアド関数内のパラメータと変数

関数にパラメータと変数を追加して、少し面白くしてみましょう。

DELIMITER //

CREATE FUNCTION Greet(name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
DECLARE greeting VARCHAR(100);
SET greeting = CONCAT('Hello, ', name, '! Welcome to MySQL functions!');
RETURN greeting;
END //

DELIMITER ;

この例では：

関数にname VARCHAR(50)というパラメータを追加しました。
greetingという変数をDECLAREキーワードで宣言しました。
SETキーワードを使って変数に値を割り当てました。
文字列を結合するためにCONCAT関数を使用しました。

新しい関数を呼び出してみましょう：

SELECT Greet('Alice');

これは以下の結果を返します："Hello, Alice! Welcome to MySQL functions!"

データベースデータとストアド関数の使用

次に、データベースと実際にやりとりする関数を作成しましょう。employeesというテーブルがあり、id、first_name、last_name、salaryというカラムがあると仮定します。

DELIMITER //

CREATE FUNCTION CalculateBonus(employee_id INT)
RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus DECIMAL(10,2);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

SET bonus = employee_salary * 0.1;

RETURN bonus;
END //

DELIMITER ;

この関数は：

employee_idという入力を受け取ります。
employee_salaryとbonusという変数を宣言します。
employeesテーブルから社員の給料を取得します。
給料の10%をボーナスとして計算します。
計算されたボーナスを返します。

この関数を使うには：

SELECT first_name, last_name, CalculateBonus(id) AS bonus
FROM employees
WHERE id = 1;

これはidが1の社員の名前、姓、計算されたボーナスを返します。

ストアド関数内の制御フロー

ボーナスの計算を少し複雑にして、制御フローを追加してみましょう。

DELIMITER //

CREATE FUNCTION CalculateBonusTier(employee_id INT)
RETURNS VARCHAR(20)
READS SQL DATA
BEGIN
DECLARE employee_salary DECIMAL(10,2);
DECLARE bonus_tier VARCHAR(20);

SELECT salary INTO employee_salary
FROM employees
WHERE id = employee_id;

IF employee_salary < 30000 THEN
SET bonus_tier = 'Bronze';
ELSEIF employee_salary < 50000 THEN
SET bonus_tier = 'Silver';
ELSE
SET bonus_tier = 'Gold';
END IF;

RETURN bonus_tier;
END //

DELIMITER ;

この関数はIF、ELSEIF、ELSEを使用して、給料に基づいてボーナスのランクを決定します。

ストアドプロシージャ内でのストアド関数の呼び出し

最後に、ストアドプロシージャ内でストアド関数を使ってみましょう。

DELIMITER //

CREATE PROCEDURE EmployeeBonusReport(IN emp_id INT)
BEGIN
DECLARE emp_name VARCHAR(100);
DECLARE emp_bonus DECIMAL(10,2);
DECLARE emp_bonus_tier VARCHAR(20);

SELECT CONCAT(first_name, ' ', last_name) INTO emp_name
FROM employees
WHERE id = emp_id;

SET emp_bonus = CalculateBonus(emp_id);
SET emp_bonus_tier = CalculateBonusTier(emp_id);

SELECT emp_name AS 'Employee Name',
emp_bonus AS 'Bonus Amount',
emp_bonus_tier AS 'Bonus Tier';
END //

DELIMITER ;

このストアドプロシージャは：

社員IDを受け取ります。
社員のフルネームを取得します。
CalculateBonus関数を呼び出してボーナス額を取得します。
CalculateBonusTier関数を呼び出してボーナスランクを取得します。
社員の名前、ボーナス額、ボーナスランクを返します。

このプロシージャを呼び出すには：

CALL EmployeeBonusReport(1);

以上で、MySQLのストアド関数の基本から、ストアドプロシージャ内での使用までをカバーしました。_practice makes perfect_ですので、自分で関数を試してみてください。ハッピーコーディング！

Credits: Image by storyset