MySQL のユーザー定義関数
ここでは MySQL のユーザー定義関数についてご説明します。
MySQL の ユーザー定義関数を作成する
MySQL でユーザー定義関数を作成するには、CREATE FUNCTION 文を使います。
CREATE FUNCTION 文の基本的な構文は次の通りです。
DELIMITER //
CREATE FUNCTION ユーザー定義関数名 (
入力引数名 データ型
)
RETURNS 戻り値のデータ型
[NOT DETERMINISTIC | DETERMINISTIC]
BEGIN
SQL ルーチンステートメント (RETURN 文を含む)
END//
DELIMITER ;まず、ユーザー定義関数の定義の中でデフォルトの DELIMITER の ; を使うので、DELIMITER を定義の中で使わない他の文字（ここでは //）に変更します。
入力引数はオプショナルで、なくても大丈夫です。
CREATE FUNCTION 文では RETURNS 戻り値のデータ型 と SQL ルーチンステートメントの中の RETURN 戻り値; は必須です。
[NOT DETERMINISTIC | DETERMINISTIC] もオプショナルで、省略した場合は NOT DETERMINISTIC が使われます。
基本的な考え方としては、入力引数が同じであれば同じ結果になる場合、その関数は DETERMINISTIC で、そうでなければ NOT DETERMINISTIC です。
この [NOT DETERMINISTIC | DETERMINISTIC] は作成者が宣言するもので MySQL はチェックを行いません。
デフォルトでは、CREATE FUNCTION 文は DETERMINISTIC か、NO SQLか、READS SQL DATA を指定していないと次のようなエラーになります。
Error Code: 1418. This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
次のクエリーを実行して作成者が危険なイベントをバイナリーログに書き込まないと信用する設定にすることで、NOT DETERMINISTIC な関数を定義してもエラーが出なくなります。
SET GLOBAL log_bin_trust_function_creators = 1;それでは、簡単なユーザー定義関数を作成してみます。
税込の値段を入力引数として渡して、税抜きの値段を返す get_before_tax_price という名前のユーザー定義関数を作成するには、次のようにできます。
DELIMITER //
CREATE FUNCTION get_before_tax_price (
after_tax_price DECIMAL(13,2)
)
RETURNS DECIMAL(13,2)
DETERMINISTIC
BEGIN
DECLARE before_tax_price DECIMAL(13,2);
DECLARE sale_tax_percent DECIMAL(5,2) DEFAULT 10; -- 10%
SET before_tax_price = IFNULL(after_tax_price, 0) * 100 / (sale_tax_percent + 100) ;
RETURN before_tax_price;
END//
DELIMITER ;sale_tax_percent は 10% にしています。
12 行目で税抜きの金額を計算し、13 行目で before_tax_price を RETURN しています。
このクエリーを実行すると、get_before_tax_price という名前のユーザー定義関数が作成されます。
MySQL Workbench の Schemas タブをリフレッシュすると、Funcitons に get_before_tax_price ができているのがわかります。
MySQL の ユーザー定義関数を実行する
MySQL のユーザー定義関数は、組み込み関数と同様に SELECT 文 で使うことができます。
SELECT ユーザー定義関数名([引数1, 引数2, ...])
[FROM ...];例えば、先ほど作成した before_tax_price 関数を実行するには、次のようにできます。
SELECT get_before_tax_price(1000);このクエリーを実行すると、消費税が 10% の時の税込価格 1000 円の税抜き価格である 909.09 円が取得できていますね。
MySQL の ユーザー定義関数を削除する
MySQL でユーザー定義関数を削除するには、DROP FUNCTION 文を使います。
DDROP FUNCTION 文の構文は次の通りです。
DROP FUNCTION ユーザー定義関数名;例えば、先ほど作成した get_before_tax_price という名前のユーザー定義関数を削除したいのであれば次のようにできます。
DROP FUNCTION get_before_tax_price;このクエリーを実行すると get_before_tax_price ユーザー定義関数が削除されます。
DROP FUNCTION 文は、実行した時に指定したユーザー定義関数が存在しないとエラーになります。
指定したユーザー定義関数が存在する時のみ削除したい場合は、IF EXISTS を次のように追加します。
DROP FUNCTION IF EXISTS ユーザー定義関数名;以上、MySQL のユーザー定義関数についてご説明しました。