SQL Server のユーザー定義のスカラー値関数を作成する
SQL Server のユーザー定義のスカラー値関数を作成する
SQL Server のユーザー定義関数には、ひとつの値を返すスカラー値関数と、テーブルを返すテーブル値関数があります。
今回は、ユーザー定義のスカラー値関数を作成してみましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
ユーザー定義のスカラー値関数
ユーザー定義関数を作成するには CREATE FUNCTION を使います。 シンプルなユーザー定義のスカラー値関数を作成する構文は次の通りです。
CREATE FUNCTION [ ファンクション名 ] (
[ @入力パラメーター名 1 ] [ 入力パラメーター名 1 のデータ型 ],
[ @入力パラメーター名 2 ] [ 入力パラメーター名 2 のデータ型 ],
[ @入力パラメーター名 3 ] [ 入力パラメーター名 3 のデータ型 ],
..
)
RETURNS [ 戻り値のデータ型 ]
AS
BEGIN
[ ファンクションで処理したい一連の T-SQL ステートメント ]
RETURN [ 戻り値 ]
END
[ ファンクションで処理したい一連の T-SQL ステートメント ] にテーブルの値を挿入・変更・削除等のスコープ外のリソースを変更するようなステートメントは書けません。
ユーザー定義のスカラー値関数の最後のステートメントは RETURN でなくてはいけません。
入力パラメーターには、また本サイトの T-SQL 入門では説明していませんが 「 ユーザー定義テーブル型 」 も指定することができます。
それでは、ユーザー定義のスカラー値関数を作ってみましょう。
StudentID を入力パラメータとして受け取り、ロジックに従って StudentNo を生成して返す ufnStudentNoGet という名前のスカラーファンクションを作ってみます。
適当に考えたものですが StudentNo 生成のロジックは生徒の誕生日が NULL か 1979 以前の場合は頭に A それ以外は B をつけ、そのあとに FirstName の左一文字の大文字、 アンダースコア、 LastName の大文字、StudentID とつなげるものとします。
CREATE FUNCTION ufnStudentNoGet (
@StudentID INT
)
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @StudentNo NVARCHAR(50),
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50),
@Birthday DATE;
SELECT @FirstName = FirstName,
@LastName = LastName,
@Birthday = Birthday
FROM Student
WHERE StudentID = @StudentID;
IF ISNULL(YEAR(@Birthday), 0) <= 1979
BEGIN
SET @StudentNo = 'A'
+ ISNULL(UPPER(LEFT(@FirstName, 1)), '')
+ '_'
+ ISNULL(UPPER(@LastName), '')
+ ISNULL(CAST(@StudentID AS NVARCHAR), '');
END
ELSE
BEGIN
SET @StudentNo = 'B'
+ ISNULL(UPPER(LEFT(@FirstName, 1)), '')
+ '_'
+ ISNULL(UPPER(@LastName), '')
+ ISNULL(CAST(@StudentID AS NVARCHAR), '');
END
RETURN @StudentNo;
END;
上記のスクリプトを実行すると、ユーザー定義のスカラー関数 ufnStudentNoGet ができました。
それでは、作ったユーザー定義のスカラー値関数を使ってみましょう。
例えば全学生の StudentNo は次のように取得できます。
SELECT *, dbo.ufnStudentNoGet(StudentID) AS StudentNo FROM Student;
[ 実行結果 ]
WHERE 句で何も指定せずにユーザー定義関数を使っていますが、レコード数が多いと実行に時間がかかる可能性があります。
もし動的に StudentNo 生成する必要がないのであれば、Student テーブルに StudentNo カラムを追加し、ファンクションを使って値を保存してしまったほうが、データ取得のパフォーマンスが良くなると思います。
その時は Student テーブルに StudentNo というカラムを作った後で、UPDATEステートメントで ufnStudentNoGet を使って値を更新します。
UPDATE Student SET StudentNo = dbo.ufnStudentNoGet(StudentID); --- SELECT * FROM Student;
[ 実行結果 ]
次は、SQL Server のユーザー定義のテーブル値関数を作成してみましょう。