SQL Server 入門 SQL Server 入門

ホーム > SQL Server のユーザー定義関数 > SQL Server のユーザー定義のスカラー値関数を作成する

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;

SQL Server のユーザー定義のスカラー関数を作成する 1

上記のスクリプトを実行すると、ユーザー定義のスカラー関数 ufnStudentNoGet ができました。

SQL Server のユーザー定義のスカラー関数を作成する 2


それでは、作ったユーザー定義のスカラー値関数を使ってみましょう。

例えば全学生の StudentNo は次のように取得できます。

SELECT  *,
        dbo.ufnStudentNoGet(StudentID) AS StudentNo
FROM    Student;

[ 実行結果 ]
SQL Server のユーザー定義のスカラー関数を作成する 3

WHERE 句で何も指定せずにユーザー定義関数を使っていますが、レコード数が多いと実行に時間がかかる可能性があります。

もし動的に StudentNo 生成する必要がないのであれば、Student テーブルに StudentNo カラムを追加し、ファンクションを使って値を保存してしまったほうが、データ取得のパフォーマンスが良くなると思います。

その時は Student テーブルに StudentNo というカラムを作った後で、UPDATEステートメントで ufnStudentNoGet を使って値を更新します。

UPDATE  Student
SET     StudentNo = dbo.ufnStudentNoGet(StudentID);

---

SELECT  *
FROM    Student;

[ 実行結果 ]
SQL Server のユーザー定義のスカラー関数を作成する 4 SQL Server のユーザー定義のスカラー関数を作成する 5


次は、SQL Server のユーザー定義のテーブル値関数を作成してみましょう。

次へ SQL Server のユーザー定義のテーブル値関数を作成する

SQL Server 関連の人気書籍
ホーム > SQL Server のユーザー定義関数 > SQL Server のユーザー定義のスカラー値関数を作成する