2022.01.13

Lv1

【SQL基礎】ストアドファンクションの登録

kaneko

本記事ではストアドファンクションを登録する方法について説明します。

ストアドファンクションはストアドルーチンの一種です。

ストアドルーチンについてはこちらの記事をご覧ください。

ストアドファンクションとセットで扱われることの多い「ストアドプロシージャ」についてはこちらの記事をご覧ください。

ストアドファンクションの実行についてはこちらの記事をご覧ください。

ストアドファンクションの削除、更新についてはこちらの記事をご覧ください。

ストアドファンクション登録の基本構文

ストアドファンクションを登録するときの基本構文は以下のとおりです。

CREATE FUNCTION ファンクション名 (引数名 データ型)
RETURNS 戻り値のデータ型 DETERMINISTC/NOT DETERMINISTIC
BEGIN
処理内容
RETURN 戻り値
END;

まず、ファンクション名と引数に関することを定義します。

引数のないファンクションも定義可能です。

次に、戻り値に関することをRETURNSに続けて定義します。

戻り値のデータ型と、戻り値が決定的かどうかを定義します。

決定的である場合はDETERMINISTICと記述します。決定的とは、同一の引数に対して返す値が決まっているということです。（Aという引数に対して必ずA´という値を返す）

DETERMINISTICを省略した場合は、NOT DETERMINISTIC（非決定的）であるとみなされます。

次に、処理内容を定義します。

処理内容が複数行にわたる場合はBEGINとENDで囲みます。

また、その場合にはデリミタの変更が必要です。

デリミタの変更についてはこちらの記事で解説しています。

RETURNで戻り値を定義するのは必須です。

ストアドファンクション登録例

実際にストアドファンクションを登録してみましょう。

例1.引数のないファンクション

“Hello”という文字列を返すファンクションを”TEST1″という名前で作成します。

引数のないファンクションを定義するときはファンクション名のあとに（）だけを記述します。

delimiter //
CREATE FUNCTION TEST1 ()
RETURNS VARCHAR(5) DETERMINISTIC
BEGIN
RETURN 'Hello';
END//
delimiter ;

最初と最後にデリミタを変更しています。

ただ、今回は処理内容がRETURNの1行のみであるため、デリミタを変更せず以下のように記述することもできます。登録されるファンクションの内容はどちらの書き方でも変わりません。

CREATE FUNCTION TEST1 ()
RETURNS VARCHAR(5) DETERMINISTIC
RETURN 'Hello';

例2.引数のあるファンクション

“Hello, “のあとに引数で指定した文字列を結合して表示するファンクションを”TEST2″という名前で作成します。

delimiter //
CREATE FUNCTION TEST2 (name VARCHAR(10))
RETURNS VARCHAR(17) DETERMINISTIC
BEGIN
RETURN CONCAT('Hello, ', name);
END//
delimiter ;

文字列結合のためにCONCATという関数を使用しています。

また、例2も例1と同様に処理内容が1行であるため、以下のように記述可能です。

CREATE FUNCTION TEST2 (name VARCHAR(10))
RETURNS VARCHAR(17) DETERMINISTIC
RETURN CONCAT('Hello, ', name);

ストアドファンクションの確認

作成したファンクションが登録されていることを確認します。

information_schemaのROUTINESテーブルを参照することで確認できます。

以下のSQLを実行してみましょう。

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

先ほど作成した”TEST1″と”TEST2″が登録されていることが確認できました。

ストアドファンクションの内容確認

作成したファンクションの内容を確認するためには以下のSQLを実行します。

1	`SHOW` `CREATE` `FUNCTION` `ファンクション名;`

例として”TEST1″の内容を確認してみましょう。

ファンクションの内容が確認できました。（実行結果を見やすく表示するためにセミコロンではなく”\G”を使用しています。）

ストアドファンクションの実行についてはこちらの記事をご覧ください。

学習のポイント

・ファンクションの戻り値が決定的である場合はDETERMINISTICを記述する必要がある。

・ストアドファンクションを確認するためにはinformation_schemaのROUTINESテーブルを参照するか、SHOW CREATE FUNCTIONを使用する。

練習問題

問、次に示すSQLの文法について正しく説明したものを選んでください。

CREATE FUNCTION TEST3()
RETURNS FLOAT
RETURN RAND();

1.デリミタを変更していないため、不適切な文法である。
2.DETERMINISTICを記述していないため、不適切な文法である。
3.NOT DETERMINISTICを記述していないため、不適切な文法である。
4.文法的に不適切なところはない。

答え

4.文法的に不適切なところはない。

[解説]

ファンクションの処理内容が１行で完結しているため、デリミタの変更は不要です。

DETERMINISTICは、ファンクションの戻り値が決定的である場合に記述します。

今回のファンクションの戻り値はRAND関数（ランダムな数字を返す関数）が使用されているため戻り値が決定的であるとはいえません。

また、DETERMINISTIC/NOT DETERMINISTICの指定を省略した場合はNOT DETERMINISTICであるものとして扱われます。記述がないこと自体は不適切であるとは言えません。

以下は実際にTEST3を登録、実行した例です。

連載目次リンク

SQL基礎連載目次

Developer